I am using the HTTP connector to update/insert an account record into Microsoft Dynamics CRM. I see that the PATCH operation uses the lookup.accountid returned by the configured lookup filters. When the composite operation results in an insert, I get the resulting accountid in my results mapping by using headers.odata-entityid. However, if it results in an update, I need access to the lookup.accountid used by the PATCH operation? Do I need to get it with JavaScript and add it to the source data?
Although it would may be good to know in the future, I don’t think I need an answer to my previous question. It looks like the CRM API does return the headers.odata-entityid on both a create and an update.
However, I am struggling with an error returned when I try to use the returned value in the next step as part of the composite lookup filter. I am getting the error below.
The field ""1")" was not found in the source data. https://{{{connection.settings.microsoftDynamics365Subdomain}}}.dynamics.com/api/data/v8.2/contacts?$filter=emailaddress1 eq '{{record.billing_email}}' and firstname eq '{{record.firstname}}' and lastname eq '{{record.lastname}}' and parentcustomerid_account eq '{{substring record.AccountId (subtract record.AccountId.length "37") (subtract record.AccountId "1")}}' and statecode = 0
When I try to use the substring above with this value:
"AccountId": "https://xxx.crm.dynamics.com/api/data/v8.2/accounts(9ea6c5bb-6079-f011-b4cb-6045bdec353c)"
I get the same error if I try to use a split “(“ 1 to get the value after the first (. Nothing is captured in the HTTP debug log, so it seems to be an error from Celigo evaluating the handlebars for the lookup.
I'm slightly confused about what the question is since terms are mixed a bit, but I'll answer a few things:
- The lookup in the composite API step doesn't allow you to reference response headers, only response body fields. Given that, if you need some value from the lookup response headers to use in the update operation of the composite API, you'd need to actually make an individual flow lookup step to do that.
- The response of the create or update operation is available for response mapping of the flow bubble when using
_jsonand/orheadersin the response mapping.
For extracting the account id, can you try this?
{{split (split record.AccountId "(" "1") ")" "0"}}
Tyler, Tried that too, and same result. Now it says field 0 instead of field 1, but otherwise the same error.
Here’s the preview in the URI builder for the lookup. Works fine.
And here’s the error with the retry data. The AccountId looks like it’s there in the retry data.
Even though the AccountId field looks like it’s there in the retry data, the error seems like it’s having trouble finding it. If I replace the whole handlebar expression with a hardcoded GUID, it runs fine.
What if you try:
{{split (split record.AccountId "(" 1) ")" 0}}
or
{{split (split record.AccountId '(' 1) ')' 0}}
or
{{split (split record.AccountId '(' '1') ')' '0'}}
Your first suggestion works fine in the preview window. I don’t think it’s the handlebar syntax.
The reason I ask to test these variations is that I've seen cases where it's being incorrectly evaluated, and sometimes switching between single and double quotes resolves it until we can fix the bug. Additionally, the docs show the index as an integer, so I figured it's also worth a shot. Let me know if all three fail.
Also, is this handlebar on the lookup section of the composite API?
Yes, it’s in the lookup section of the composite API.
I tried it without the “ around the numbers, and same result.
Can you try the single quotes around parentheses as well?
What if you try:
{{regexMatch (regexMatch data.AccountId "\([^)]*\)" 0) "[^()]+" 0}}
or
{{regexMatch (regexMatch data.AccountId '\([^)]*\)' 0) '[^()]+' 0}}
All of the scenarios I've provided are working for me in the composite lookup step. I'd suggest opening a support ticket and provide them this thread.
Same result with those. I will open a support ticket.
Thanks for helping!