1

I'm using a SOQL query in an ETL tool to extract Account data from Salesforce and ultimately map it to our ERP system. The business has come up with two different use cases for extracting data in the same integration pipeline. Below is the query I'm currently using and below it is the additional scenario. I'd like to combine these into one query, if possible. They're similar, but the first excludes the integration user (_p_integrationUserId) and the second is based on a change made by the integration user. Also, the second includes an inner join on the Account History object.

Current Query

SELECT
    Industry, OwnerId, Category__c, Name, Phone, Website, BillingCity, 
    BillingCountry, BillingState, BillingStateCode, BillingPostalCode, 
    BillingStreet, Id, NetSuite_Internal_ID__c, LastModifiedDate, 
    Owner.NS_Internal_ID__c, UUID__c, BD_Partner__c, Brand__r.Name, 
    NS_Address_Internal_Id__c, Type, Ad_Agency_Parent__c, Address_Changed__c, 
    lastmodifiedby.Name, PM_Business_Alias__c, 
    Ecomm_Retention_Rep_NS_Internal_ID__c 
FROM Account 
WHERE (LastModifiedDate= last_n_days:1
AND lastmodifiedbyid NOT IN ('" + _p_integrationUserId + "')
AND NetSuite_Internal_ID__c<>null
AND Type='" + _p_Customer_Type +"') 
AND RecordTypeId NOT IN ('" + _p_Location_RT + "','" + _p_Production_Shell_RT + "')

Addt'l Query

SELECT
    Industry, OwnerId, Category__c, Name, Phone, Website, 
    BillingCity, BillingCountry, BillingState, BillingStateCode, 
    BillingPostalCode, BillingStreet, Id, NetSuite_Internal_ID__c, 
    LastModifiedDate, Owner.NS_Internal_ID__c, UUID__c, BD_Partner__c, 
    Brand__r.Name, NS_Address_Internal_Id__c, Type, Ad_Agency_Parent__c, 
    Address_Changed__c, lastmodifiedby.Name, PM_Business_Alias__c, 
    Ecomm_Retention_Rep_NS_Internal_ID__c, (SELECT Id, CreatedById FROM 
    Histories WHERE Field = 'Owner')
FROM Account WHERE (LastModifiedDate= last_n_days:1
AND lastmodifiedbyid IN ('" + _p_integrationUserId 
+ "')
AND NetSuite_Internal_ID__c<>null
AND Type='" + _p_Customer_Type +"')
AND RecordTypeId not in ('" + _p_Location_RT + "','" + _p_Production_Shell_RT + "')

I can always have two separate queries and then join the results, but it'd be great if I can combine these into one.

Updated Query

SELECT
    --A bunch of Account fields--, 
    (SELECT Id, 
     FROM AccountHistory
     WHERE (Field IN ('Name', 'Industry', 'Phone', 'Website', 'OwnerId', 
                      'Category__c', 'BillingStreet', 'BillingCity', 
                      'BillingState', 'BillingPostalCode', 'BillingCountry', 
                      'UUID__c', 'BD_Partner__c', 'Brand__c', 'Type', 
                      'Ad_Agency_Parent__c', 'PM_Business_Alias__c') 
                  AND CreatedDate = last_n_days:1))
FROM Account 
WHERE (NetSuite_Internal_ID__c<>null 
       AND Type='" + _p_Customer_Type +"' 
       AND RecordTypeId not in ('" + _p_Location_RT + "','" + _p_Production_Shell_RT + "'))

1 Answer 1

1

Update: You can use the following query:

SELECT
    AccountId, CreatedDate, OldValue, NewValue
FROM AccountHistory
WHERE CreatedDate = last_n_days:1
 AND CreatedById IN ('" + _p_integrationUserId + "')
 AND Field = 'Owner'
 AND Account.NetSuite_Internal_ID__c <> null
 AND Account.Type='" + _p_Customer_Type +"')
 AND Account.RecordTypeId not in ('" + _p_Location_RT + "','" + _p_Production_Shell_RT + "')

I assumed you still want to return only changes made within last 24 hrs, the result will contain multiple records for the same account if Owner was changed more than once (based on my test, it returns 2 records for the same change - one for OwnerId values, and one for Owner Names), you could reference any fields you want from Account by adding Account.Field_Name__c to the query.

Original Answer: You just drop and lastmodifiedbyid in ('" + _p_integrationUserId + "') from the Addt'l Query and use lastmodifiedby.Name field in ETL to identify whether modification to the record was done by integrationUser or not and process accordingly

6
  • Maybe my second query is incorrect then. I only want to get Accounts records where the Account Owner was modified regardless of who modified it (plus the additional criteria in the WHERE clause). The first query gets all Account changes, regardless of what field was modified. Apr 11, 2017 at 1:37
  • @ChrisPowers, You criteria says "They're similar, but the first excludes the integration user (_p_integrationUserId) and the second is based on a change made by the integration user." when you say in your comment "regardless of who modified it" - what is the requirement then?
    – o-lexi
    Apr 11, 2017 at 17:19
  • Sorry... that was a bit confusing. The first query is one that is currently in use. It gets all changes made to an Account record, except if it was last modified by the integration user (_p_integrationUserId). Future state - we also want to include Account records whose Account Owner was last updated by the same integration user. Apr 11, 2017 at 18:49
  • and is it the only updated by the integration user?
    – o-lexi
    Apr 11, 2017 at 18:51
  • Future state - yes, Accounts whose Owner was last modified by the integration user. Apr 11, 2017 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.