How to replace Single Quote from attribute in Microsoft Flow

By | July 18, 2019

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.

Replace Single Quote from attribute in Microsoft Flow

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.

Replace Single Quote from attribute in Microsoft Flow

 

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(Body()?[‘text_1’],””,'”‘)

Replace Single Quote from attribute in Microsoft Flow

4. Finally, we can use this variable in our retrieve Filter Query as shown below.

Replace Single Quote from attribute in Microsoft Flow

5. As shown in below image you can see the successful run history record to retrieve the contact record.

Replace Single Quote from attribute in Microsoft Flow

Conclusion

Thus by using this approach we can successfully retrieve the record from Dynamics CRM and perform the operation on records.

PowerApps

2 thoughts on “How to replace Single Quote from attribute in Microsoft Flow

  1. Craig

    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 🙁

    Reply
    1. inogic Post author

      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:

      Microsoft Flow

      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.

      Microsoft Flow

      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!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *