Looping Through Returned SF Records In a Lookup Step

Integrator.io
, , , ,
1

Due to the fact that one of my fields is an encrypted SF field, I am unable to query based on that field directly. Therefore, I have to query and bring back “potential matches” based on Birthdate which means I need to loop through the returned records and compare the Birthdates and SSN for each returned record to that of my incoming payload.

How could I lookup through these records and compare the Birthdate and SSN in my payload to the returned records? Can this be done on a transform step within the lookup itself, or does this have to be done via some sort of JavaScript (essentially doing a one-to-many lookup on these two criteria)? If one of these records matches, I will have a branch step right after this lookup to kick it out to another flow.

Here is a mock return of the lookup step:

{
 "page_of_records": [
   {
     "record": {
       "attributes": {
         "type": "Contact",
         "url": "/services/data/v61.0/sobjects/Contact/003XX00000DummyID001"
       },
       "Id": "003XX00000DummyID001",
       "vlocity_ins__SocialSecurityNumber__c": "111-11-1111",
       "Birthdate": "1970-01-01"
     }
   },
   {
     "record": {
       "attributes": {
         "type": "Contact",
         "url": "/services/data/v61.0/sobjects/Contact/003XX00000DummyID002"
       },
       "Id": "003XX00000DummyID002",
       "vlocity_ins__SocialSecurityNumber__c": "222-22-2222",
       "Birthdate": "1980-02-02"
     }
   },
   {
     "record": {
       "attributes": {
         "type": "Contact",
         "url": "/services/data/v61.0/sobjects/Contact/003XX00000DummyID003"
       },
       "Id": "003XX00000DummyID003",
       "vlocity_ins__SocialSecurityNumber__c": "333-33-3333",
       "Birthdate": "1990-03-03"
     }
   },
   {
     "record": {
       "attributes": {
         "type": "Contact",
         "url": "/services/data/v61.0/sobjects/Contact/003XX00000DummyID004"
       },
       "Id": "003XX00000DummyID004",
       "vlocity_ins__SocialSecurityNumber__c": "444-44-4444",
       "Birthdate": "2000-04-04"
     }
   }
 ]
}
Also, here is the lookup step (since I can't query directly against the vlocity_ins__SocialSecurityNumber__c:
Select
  Id,
  vlocity_ins__SocialSecurityNumber__c,
  Birthdate
FROM
  Contact
WHERE
  Birthdate = {{record.PrimaryMemberInformation.DateOfBirth}}
2

@tylerlamparter - Thanks so much for the guidance during office hours today on the postResponseMap script.

One question I forgot to ask is should this be a one-to-many lookup step given the fact that the initial query (based on Birthdate alone) could return multiple matches? From there I can use the postResponseMap script to loop through all of the SSN's to see if there is a match?

3

@daveguderian no, you would end up with 1 source record and many child records that are mapped back into the source record. From there, the script would need to filter out child records where the encrypted field on the child record ≠ the parent level field.

4

Thanks @tylerlamparter . My postresponsemap script must not be working correctly then. It only seems to be looking at the first returned record and not looking at any other ones returned from the lookup.