Advice: Salesforce query and mapping for bulk operation

[ritazagoni]

We are processing ~80k form submissions from CommCare and bulk upserting them so Salesforce.

For each form we're bulk-processing, we need to query fields from a Salesforce Location that matches an owner_id value from that form. So for each form we need: query( SELECT Id, Parent_Geographic_Area__c, Parent_Geographic_Area__r.Name, Parent_Geographic_Area__r.Parent_Geographic_Area__c FROM Location__c WHERE CommCare_User_ID__c = '${dataValue( 'form.subcase_0.case.create.owner_id' )(state)}' ); and then do some manipulation on the data received, and assign the result to the mapping.

One way to achieve this is:

1. to query Salesforce for a list of IDs:

   query(
   `SELECT Id, Parent_Geographic_Area__c, Parent_Geographic_Area__r.Name, Parent_Geographic_Area__r.Parent_Geographic_Area__c FROM Location__c WHERE CommCare_User_ID__c in (${listOfOwnerIds})'`
   );

   which will return a Array of records.

2. and create a mapping object with the owner_ids and the result:

   {'owner_id1': 'area1',
   owner_id2': 'area2'}

OR: could we use map() to map the owner_ids array to the fetched results?

Note that owner_id-s may be duplicated across the form submissions.

@taylordowns2000 what would be the best way to tackle this? If we're using array.map() I'd need some help implementing that.

[taylordowns2000]

@ritazagoni , the suggestion here is to use that external ID. our call was cut short, so it's possible that i'm not understanding the full requirement, but please try this for now and let me know if it doesn't work.

{
  Catchment__c: { CommCare_User_ID__c: state.data.form.subcase_0.case.create.owner_id }
}

https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asynch/datafiles_xml_rel_fields.htm

Note that if this were NOT using the bulk API, we'd use the standard relationship field from language-salesforce. See the Nurse__r example here.

[aleksa-krolls]

@taylordowns2000 it's not that simple. This will only work for 1 of the 3 location mappings. See here for a visual diagram of how 1 Person record needs to map to a hierarchy of Location records.

This is why when we are upserting only 1 person, we currently have a query like below to help us find the parent and grandparent Locations of the Village (which is the only record we can map to via owner_id). Let me and @ritazagoni know when you're available to discuss again.

query(
SELECT Id, Parent_Geographic_Area__c, Parent_Geographic_Area__r.Name, Parent_Geographic_Area__r.Parent_Geographic_Area__c FROM Location__c WHERE CommCare_User_ID__c = '${dataValue( 'form.subcase_0.case.create.owner_id' )(state)}'
);

[taylordowns2000]

@ritazagoni , holler if you need any more help!