My flow uses a couple of checks (members DOB and members SSN) before updating information in Salesforce. The SSN check I have seems to work fine, but whenever I add in the check (both of these are in the import) for DOB I receive the error below.
Below are my import steps/filtering I am using to "check" salesforce for an existing record based off of the members DOB and SSN. I believe my issue has to do with the DOB filter.
As always, any help is greatly appreciated!
Hi Dave,
You can structure your flow with an additional lookup step and response mapping to give you more flexibility identifying the correct Salesforce record. The flow would look something like this...
The middle step would contain the SOQL statement that would identify the correct Patient record based upon the SS and birthday and return the Salesforce internal ID which is then used on the 3rd step to update the record.
In the lookup step (shown in the screenshot below) you specify your lookup logic as a SOQL statement to identify the record. The SOQL statement I used was ...
SELECT Name,id,Birthday__c FROM Contact WHERE Birthday__c = {{record.Birthday}}which you would modify to include your custom fields like SS and birthday and go off the patient record. You can use the Salesforce workbench to help build that compound SOQL statement and ensure you have the correct format and field names. You can see in the statement where I have included the value from the previous step which is contained in the "{{" this will dynamically change per record with the next SS and date you want to lookup.
This lookup will return the fields specified in the first part of your SOQL statement but the most important one is the internal ID of the record in your case the internal Salesforce ID for patient. You will then response map the results of the lookup which allows that data to be used in future steps. Once your lookup step is set up, you hit the plus (step 1) and then the fork (step 2) to open the response mapping interface.
You can then map the entire response of the lookup or individual fields. In the screenshot below you can see I just mapped the Salesforce ID. Good practice is to mark the response with some sort of prefix so you know that its a response value so you can see where I have put "r_ID" to mark it as my lookup response ID and that is now added to my output data to be used in the next step.
Now you can use that internal ID in the next step on the filter like you had before and not have to worry about date formatting.
Now the salesforce record with that internal ID will be the one you are modifying. Hope this helps and let me know if you have any questions.
Thank you so much Nate. I am just getting started on this. For the lookup, which option would be most appropriate?
Look up additional records (per record)
Thank you both for the help. I am struggling to understand the {{record.Birthday}} portion of the SOQL statement in the lookup. If I am pulling in three fields from the same object, should this be set to something specific?
For example, my current SOQL query looks like below, but I am not clear on what to put in the {{?}} portion at the end?
select IntPS__Social_Security_Number__c, IntPS__ID__c, IntPS__Date_Of_Birth__c from IntPS__Patient__c = {{?}}Think of the handlebar statements as variables whose values are fields from the export and dynamically change every time a record flows through. You need to specify in the where clause of the SOQL statement what values you are trying to match. Something like...
select IntPS__Social_Security_Number__c, IntPS__ID__c, IntPS__Date_Of_Birth__c from IntPS__Patient__c where IntPS__Date_Of_Birth__c = {{record.fieldThatContainsBirthdayValueOnWebhook}}Thanks Nate... this really made it click.
I am receiving a malformed query error now (unexpected token) when I query, It does work when I preview the data from AFE 1.0, but then when I actually run it I get the error below. Is it due to both of my fields (PrimaryMembersDateOfBirth and PrimarySSN) containing dashes in them?
select IntPS__Social_Security_Number__c, IntPS__Date_Of_Birth__c, Plan_Member_SFID__c from IntPS__Patient__c where IntPS__Social_Security_Number__c = {{data.PrimarySSN}} and IntPS__Date_Of_Birth__C= {{data.PrimaryMembersDateOfBirth}}
Looks like the code block didnt work to capture the query too nice so trying again below!
select IntPS__Social_Security_Number__c, IntPS__Date_Of_Birth__c, Plan_Member_SFID__c from IntPS__Patient__c where IntPS__Social_Security_Number__c = {{data.PrimarySSN}} and IntPS__Date_Of_Birth__C= {{data.PrimaryMembersDateOfBirth}}You might try inclosing the SS handlebar in single quotes as its shown in this documentation if the field is of type text.
Something like this...
select IntPS__Social_Security_Number__c, IntPS__Date_Of_Birth__c, Plan_Member_SFID__c from IntPS__Patient__c where IntPS__Social_Security_Number__c = '{{data.PrimarySSN}}' and IntPS__Date_Of_Birth__C= {{data.PrimaryMembersDateOfBirth}}Nate- Thank you for the continued help on this.
For your lookup response field, I know there are certain prefixes to choose from (i.e. data, errors, ignored, etc), but is what you put after that completely up to your discretion? Could I do something like data.DOB, or data.SSN for my lookup response fields?
Hi Dave,
Yes you can choose specific fields out of the entire returned data set. You would just need to put the correct path to that field. You see in the example I sent I used...
data.[0].idin the response mapping to only get the returned Salesforce ID. This would specifically get the first value in the returned data set.
Nate-
I am reworking this flow, but now I cant seem to get my result mapping to show in the right hand box (where account type is currently). Additionally, its not showing in the "Edit Results mapping", but does show when I execute my query.
Hi Dave,
I know we spoke and you were able to resolve this but wanted to close out this thread if anyone else needs to use it. Results mapping shows in the following steps rather than the lookup step itself.
Nate
