Interrent CSV Customization

  • 107Views
  • Last Post 01 April 2021
0
votes
Andrew Ditty posted this 30 March 2021

Hello,

I have several requests for my client to customize the CSVs output by SkyVia. I need the query replaced on three files in two similar packages with the following SQL.

The packages are named "Salesforce CSV Export - CLV" (123992) and "Salesforce CSV Export - CLV" (118001)

Each package has three CSVs "ResProspects", "ResTenants", and "ResRoommates"

The SQL needs to be updates for all three CSVs in both packages, as described in the comments below.

Thank you,

Andrew

 

Order By: Standard | Newest | Votes
0
votes
Andrew Ditty posted this 30 March 2021

For packages 118001 and 123992

For Applicant__c --> ResProspects

 

SELECT 'Prospect_Code' as ResProspects, 'Property_Code' as ' ', 'Status' as '  ', 'FirstName' as '   ', 'LastName' as '    ', 'First_Contacted_On' as '     ', 'Source' as '      ', 'Agent' as '       ', 'MiddleName' as '        ', 'Salutation' as '         ', 'Address1' as '          ', 'Address2' as '           ', 'City' as '            ', 'State' as '             ', 'ZipCode' as '              ', 'HomePhone' as '                ', 'OfficePhone' as '                   ', 'CellPhone' as '                    ', 'Email' as '                     ', 'Date_Of_Birth' as '                      ', 'Drivers_License' as '                       ', 'Income' as '                        ', 'Preferred_Rent' as '                          ', 'Preferred_Bedrooms' as '                           ', 'Preferred_Bath' as '                             ', 'Preferred_MoveIn' as '                              ', 'Previous2_Address2' as '                               ', 'Company' as '                                                    ', 'Position' as '                                                      ', 'Date_Show' as '                                                        ', 'CurrentEmployment_AdditionalIncome' as '                                                                                                 '

 

union all

 

SELECT Applicant__r.Prospect_sCode__pc, Applicant__c.Yardi_Property_hMy__c, Applicant__c.Yardi_sStatus__c, Applicant__c.First_Name__c, Applicant__c.Last_Name__c, date(Applicant__c.First_Contacted_On__c), Opportunity__r.LeadSource, Opportunity__r.sAgent__c, Applicant__c.Middle_Name__c, Applicant__r.Salutation, Applicant__r.PersonMailingStreet, Applicant__r.PersonMailingAddress_Additional__c, Applicant__r.PersonMailingCity, Applicant__r.PersonMailingStateCode, Applicant__r.PersonMailingPostalCode, Applicant__c.Home_Phone__c, Applicant__c.Mobile_Phone__c, Applicant__c.Work_Phone__c, Applicant__c.EmailAddress__c, Applicant__c.DateofBirth__c, Applicant__c.Drivers_License__c, Applicant__c.TotalMonthlyIncome__c, Opportunity__r.Budget__c, Opportunity__r.Desired_Bathrooms__c, Opportunity__r.Desired_Bedrooms__c, Opportunity__r.Desired_Move_In_Date__c, Applicant__c.Person_Unique_Id__c, Applicant__c.Employer__c, Applicant__c.Current_Occupation__c, Applicant__c.First_Showing_Date__c, Applicant__c.Total_Additional_Monthly_Income__c

 

  FROM Applicant__c

 

    LEFT OUTER JOIN Account AS Applicant__r ON Applicant__c.Applicant__c = Applicant__r.Id

 

    LEFT OUTER JOIN Opportunity AS Opportunity__r ON Applicant__c.Opportunity__c = Opportunity__r.Id

 

 WHERE (Applicant__c.Role__c IN ('Primary Tenant') AND Applicant__c.Send_Prospect_To_Yardi__c = True AND date(Applicant__c.LastModifiedDate) = date('now', 'localtime'))

0
votes
Andrew Ditty posted this 30 March 2021

For packages 118001 and 123992

For Contact --> ResTenants

 

 

SELECT 'Property_Code' as 'ResTenants', 'Tenant_Code' as ' ', 'Unit_Code' as '  ', 'Status' as '   ', 'First_Name' as '    ', 'Last_Name' as '     ', 'Middle_Name' as '      ', 'Salutation' as '       ', 'Address1' as '        ', 'Address2' as '         ', 'City' as '          ', 'State' as '           ', 'ZipCode' as '             ', 'Email' as '              ', 'Phone_Number_1' as '                ', 'Phone_Number_2' as '                 ', 'Phone_Number_3' as '                  ', 'Rent' as '                   ', 'Move_In_Date' as '                    ', 'Lease_From_Date' as '                      ', 'Lease_To_Date' as '                        ', 'Move_Out_Date' as '                         ', 'Notice_Date' as '                          ', 'General_Info_10' as '                           ', 'General_Info_11' as '                            ', 'Non_Sufficient_Fund_Count' as '                              ', 'Late_Fee_Count' as '                               ', 'Date_Of_Birth' as '                                '

 

union all

 

SELECT t.Yardi_Property_hMy__c, t.sCode__c, t.Unit_sCode__c, t.Yardi_iStatus__c, t.FirstName, t.LastName, t.MiddleName, t.Salutation, t.MailingStreet, t.MailingStreet2__c, t.MailingCity, t.MailingStateCode, t.MailingPostalCode, t.Email, t.MobilePhone, t.Phone, t.Other_Phone__c, t.Suite_Rent__c, t.Move_In__c, t.Lease_Start__c, t.Lease_End__c, t.Move_Out__c, t.Date_Notice__c, t.External_Id__c, t.Preferred_Language__c, t.Non_Sufficient_Fund_Count__c, t.Late_Payment_Count__c, t.Birth_Date__c

 

  FROM Contact AS t

 

WHERE (t.Send_Tenant_To_Yardi__c = True AND date(t.LastModifiedDate) = date('now', 'localtime') AND t.Primary_Tenant__c IS NULL)

 

0
votes
Andrew Ditty posted this 30 March 2021

For packages 118001 and 123992

For Contact --> ResRoommates

 

SELECT 'Tenant_Code' as ResRoommates, 'Property_Code' as ' ', 'Unit_Code' as '  ', 'Roommate_LastName' as '   ', 'Roommate_FirstName' as '    ', 'Roommate_Salutation ' as '     ', 'Roommate_Code' as '      ', 'Roommate_Email' as '       ', 'Roommate_MoveIn' as '        ', 'Roommate_MoveOut' as '         ', 'Roommate_Occupant' as '           ', 'Roommate_PhoneNumber1' as '             ', 'Roommate_Relationship' as '              ', 'Roommate_Notes' as '                '

 

union all

 

SELECT Contact.Primary_Tenant__c, Primary_Tenant__r.Yardi_Property_hMy__c, Primary_Tenant__r.Unit_sCode__c, Contact.LastName, Contact.FirstName, Contact.Salutation, Contact.Roomate_sCode__c, Contact.Email, Contact.Move_In__c, Contact.Move_Out__c, Contact.Yardi_bOccupant__c, Contact.MobilePhone, Contact.Yardi_sRelationship__c, Contact.External_Id__c

 

  FROM Contact

 

LEFT OUTER JOIN Contact AS Primary_Tenant__r ON Contact.Primary_Tenant__c = Primary_Tenant__r.Id

 

WHERE (Contact.Send_Roommate_To_Yardi__c = True AND Contact.Primary_Tenant__c IS NOT NULL AND date(Contact.LastModifiedDate) = date('now', 'localtime'))

0
votes
Andrew Ditty posted this 31 March 2021

Hello,

 

I am getting pressure from my client on these. They are a core part of our integration with SkyVia and our project as a whole. This ticket is very urgent as it could hold up user acceptance testing. Is there any estimate on when this work can be done?

 

Thanks,

Andrew

0
votes
Sabina Bashuk posted this 31 March 2021

Hi Andrew,

Sorry for the delayed reply.

If we understood you correctly, you're trying to execute a custom query in an Export package.

Unfortunately, this functionality is not supported at the moment, but we are planning to add it in the nearest future.

0
votes
Andrew Ditty posted this 31 March 2021

Hi Sabina,

We previously submitted a ticket where we did just this. https://support.skyvia.com/thread/csv-custom-first-row-1/

Please ask Mariia Zaharova how she was able to accomplish this last time. This is a must have for us

Thanks,

Andrew

0
votes
Sabina Bashuk posted this 01 April 2021

Hi Andrew,

Could you please send your queries to [email protected] as files?

We will check and try to help you. Mariia unfortunately is not available at the moment.

Sorry for keeping you waiting.

New Ticket
Categories
Popular Tags
Close