Introduction
In one of our blogs we have seen how to update existing records in CDS (Common Data Service) using Microsoft flow in Dynamics 365 CRM.
But recently we came across a scenario where we wanted to update a Contact record based on First name, Last name & EmailAddress using Microsoft Flow.
However, while retrieving the list of Lead records we faced an error 400. This error was caused because there was special character like Single Quote (’) in the Last name.
So to overcome this issue we replaced the single quote while retrieving the contact records. In order to achieve this, we have to write a query to replace a single quote from the attribute. We will store this query in the variable and use the variable to retrieve entity records.
Please follow the below steps to add a variable:
1. Before retrieving the lead record we have to add a step to initialize variable. For that add an Action and in the action search for Variables as shown in the given image.
2. After selecting Initialize variable in the name attribute you can give a name to your variable and in String type select type as a ‘String’.
3. In the value we have to write our expression to replace the Single quote from the received attribute in the body. Next, we will store the value in this created String variable. In our scenario we are removing single Quote from LastName attribute. You can find the below query to replace the single Quote from the attribute.
Query = replace(triggerBody()?[‘text_1’],””,”””)
4. Finally, we can use this variable in our retrieve Filter Query as shown below.
5. As shown in below image you can see the successful run history record to retrieve the contact record.
Conclusion
Thus by using this approach we can successfully retrieve the record from Dynamics CRM and perform the operation on records.
Marketing4Dynamics – Mailchimp and Dynamics 365 CRM integration to plan effective sales strategies, increase sales and improve ROI
- Sync Audiences, Members and Tags from Mailchimp to CRM
- Sync CRM Marketing List (Contacts/Leads) to Mailchimp
- Sync Campaigns and Member activities from Mailchimp to CRM
- Monitor and analyze Mailchimp campaign statistics through Dashboards in CRM
Can you please double-check the code you pasted?
Query = replace(Body()?[‘text_1’],””,’”‘)
It appears that it has come out as: double quote, double quote, commar, apostrophe, double quote, opening single quote, close round bracket.
Which makes no sense.
I have a similar problem, where I’m trying to compare a column with a concatenation of text and a 2nd column. Unfortunately the 2nd column sometimes contains an apostrophe and so the filter ends up with:
Title eq ‘1-Freddy’s name’
Which just bombs 🙁
So I’m trying to find some kind of: replace(myString, ”’, ‘~”)
Where ~ is apparently the escape character, but Flow doesn’t like it 🙁
1. Please find our query below:
replace(triggerBody()?[‘text_1’],””,'”‘)
Which is four times single quote, comma, single quote, double quote , single quote.
With this your variable value will look as below:
Then again while retrieving the records from Dynamics we have to again use the replace expression to retrieve records which contains single quote in lastname. Consider my lastname = Mah’i is stored in CRM with the single quote in my Dynamics.
Query to use in List Records:- replace(variables(‘Lastname’),'”‘,”””)
It is single quote , double quote , single quote, comma , single quote , single quote , single quote , single quote , single quote , single quote.
We used this because in the background flow triggers in OData query to retrieve the list of records where lastname = Mah’i . So it will divide my last name as lastname eq ‘Mah”i’ and this will fetch the records from Dynamics.
2. I ended up just removing the apostrophe with:
replace(myString, ””, ”)
– With your formula we can only remove the single quote from string , but in our scenario we have to store the string with single quote as well as retrieve the list of records along with it.
Thanks!
I ended up just removing the apostrophe with:
replace(myString, ””, ”)
that’s apostrophe, apostrophe, apostrophe, apostrophe, commar, apostrophe, apostrophe, close round bracket.
But it does mean there’s an issue if you need to compare/filter on something that must keep the apostrophe 🙁
Hi Sam, thanks for this which has come in very useful almost 2 years later! Just to check my understanding if I had a text value (containing an apostrophe) coming into a Flow and wanted to list rows (Contact records) whose last name exactly matched the text, could I combine the two expressions mentioned above (and avoid the use of a variable in the first place)?
So within a List Rows step my singular filter query would be:
lastname eq ‘replace(replace(triggerBody()?[‘text_1’],””,'”‘),'”‘,”””)’
And the reason this can’t be simplified further into a single replace is because ‘escaping’ an apostrophe requires a certain syntax which we can only produce through combination (or something like that).
Thanks again!
We tried your provided expression and yes we can use this expression without using a variable. Also, you can simply add the existing expression (i.e. provided in the blog) in the filter criteria without using variable.
Thanks!