# Salesforce incorrect date format **URL:** \ **Category:** Integrator.io\ **Tags:** filter, error-handling, import, salesforce, flow-builder\ **Created:** [October 24, 2023, 3:29pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325 "2023-10-24T15:29:45Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![daveguderian](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@daveguderian](https://connective.celigo.com/u/daveguderian)\ **Post date:** [October 24, 2023, 3:29pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/1 "2023-10-24T15:29:45Z")
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. ![5301043caa20740200000001-e9db94ce0a524fbbae162abe71e9426e.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/d/d8bab96d48778ac854d008d4a7c6edbb34de46b0.png) ![5301043caa20740200000001-3ab383d71a2040588f7f3a11da6307db.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/5/5b4a5a255eaa9d1addf806cf0667886800ad2b80.png) ![5301043caa20740200000001-c511f127b7b74e93b146336c6f7d7d56.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/4/4b3ebee671c70ef78d1748dcdc204ce0e4aa01be.png) As always, any help is greatly appreciated! ---
**Author:** ![natebriant](https://avatars.discourse-cdn.com/v4/letter/n/d78d45/32.png) [@natebriant](https://connective.celigo.com/u/natebriant)\ **Post date:** [October 24, 2023, 10:29pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/2 "2023-10-24T22:29:24Z")
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... ![5301043caa20740200000001-e634e3502d0f49409be61b33b635f67c.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/f/f433eeccd829cc27ad4d2670f00b7c1947a36db8.png) 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. ![5301043caa20740200000001-ad3287e33b844a6eb8413b6552c01fd2.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/b/bb5c8a73aa289bef1125e8afa2f90d7c67c28627.png) 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. ![5301043caa20740200000001-972123d87bfe4ec58c2fcb04ec2207b0.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/9/998f1e5e4ba09e3ee2b5497001a79f79f14552f7.png) 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. ![5301043caa20740200000001-e260052743fb4bb69244f4a084e24fd3.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/6/67b82443f02fdcb56525875f53e1d87fc98a58e3.png) 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. ![5301043caa20740200000001-fc98feca93e74e4686f3eef1dd90da19.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/f/f4b87d785765b82c19f524870cee0dcdc856a20c.png) 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. ---
**Author:** ![daveguderian](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@daveguderian](https://connective.celigo.com/u/daveguderian)\ **Post date:** [October 25, 2023, 2:42am UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/3 "2023-10-25T02:42:54Z")
Thank you so much Nate. I am just getting started on this. For the lookup, which option would be most appropriate? ![5301043caa20740200000001-32aa0e8b2e4e46c987f0120a25d0a1d0.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/0/07f3e48d6adac07c2aa4f37ad198f379fa9f6a23.png) ---
**Author:** ![steveklett](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@steveklett](https://connective.celigo.com/u/steveklett)\ **Post date:** [October 25, 2023, 2:45am UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/4 "2023-10-25T02:45:50Z")
Look up additional records (per record) ---
**Author:** ![daveguderian](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@daveguderian](https://connective.celigo.com/u/daveguderian)\ **Post date:** [October 25, 2023, 1:36pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/5 "2023-10-25T13:36:10Z")
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 = {{?}} ``` ---
**Author:** ![natebriant](https://avatars.discourse-cdn.com/v4/letter/n/d78d45/32.png) [@natebriant](https://connective.celigo.com/u/natebriant)\ **Post date:** [October 25, 2023, 2:46pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/6 "2023-10-25T14:46:24Z")
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}} ``` This statement returns the patient record whose birthday field matches the birthday value in the webhook. Then you can add an and add the other condition of matching the SS number. ---
**Author:** ![daveguderian](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@daveguderian](https://connective.celigo.com/u/daveguderian)\ **Post date:** [October 25, 2023, 4:35pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/7 "2023-10-25T16:35:07Z")
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}} ``` ---
**Author:** ![daveguderian](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@daveguderian](https://connective.celigo.com/u/daveguderian)\ **Post date:** [October 25, 2023, 5:00pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/8 "2023-10-25T17:00:02Z")
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}} ``` ---
**Author:** ![natebriant](https://avatars.discourse-cdn.com/v4/letter/n/d78d45/32.png) [@natebriant](https://connective.celigo.com/u/natebriant)\ **Post date:** [October 25, 2023, 7:27pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/9 "2023-10-25T19:27:17Z")
You might try inclosing the SS handlebar in single quotes as its shown in this [documentation](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_conditionexpression.htm) 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}} ``` ---
**Author:** ![daveguderian](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@daveguderian](https://connective.celigo.com/u/daveguderian)\ **Post date:** [October 26, 2023, 1:42pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/10 "2023-10-26T13:42:36Z")
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? ---
**Author:** ![natebriant](https://avatars.discourse-cdn.com/v4/letter/n/d78d45/32.png) [@natebriant](https://connective.celigo.com/u/natebriant)\ **Post date:** [October 26, 2023, 4:22pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/11 "2023-10-26T16:22:23Z")
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].id ``` in the response mapping to only get the returned Salesforce ID. This would specifically get the first value in the returned data set. ---
**Author:** ![daveguderian](https://avatars.discourse-cdn.com/v4/letter/d/ce7236/32.png) [@daveguderian](https://connective.celigo.com/u/daveguderian)\ **Post date:** [October 27, 2023, 6:26pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/12 "2023-10-27T18:26:39Z")
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. ![5301043caa20740200000001-b48be8f4be424456b7a234a5eba9dc5e.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/a/a71066f5db91d020826953ec14d040d03172062b.png) ![5301043caa20740200000001-b782af179a45456e850db6f101d5329e.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/2/2105493f888e71e1f971ea8ed5e3c3ee61b63d06.png) ---
**Author:** ![natebriant](https://avatars.discourse-cdn.com/v4/letter/n/d78d45/32.png) [@natebriant](https://connective.celigo.com/u/natebriant)\ **Post date:** [November 3, 2023, 3:25pm UTC](https://connective.celigo.com/t/salesforce-incorrect-date-format/325/13 "2023-11-03T15:25:19Z")
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