Comment out single quote in OData Filter Query with dynamic content

[Morghan]

Hello,

I have two lists. When an item is created in List 1, which includes a Name column, the flow looks at a Lookup list (List 2) to see if that name already exists. I am using a Get Items action with an OData Filter query in place to look at just the name column. The query:

The issue I am having is that if there is a single quote in the person's name (e.g., Kevin O'Leary), the flow fails with the below error.

How would I go about commenting out a single quote in a name field when dynamic content is being used? I've searched online but not finding a solution.

Thank you in advance for your help!

M

[Jcook]

Hello @Morghan 

You will have to sanitize the name by using the replace() expression:

For example:

See Compose firstname & Sanitize firstname

The replace function is:

replace(outputs('Compose_firstName'),'''',' ')

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Morghan]

Hi @Jcook - thank you for the response! 

Your suggestion is working, but running into a new issue now....

Regarding your response, the Compose actions work but I had to change my Filter Query in the Get Items to the following -

The flow is completing successfully, however, the Condition that follows is now always resolving to false. Because now it's looking at the lookup list for "Kevin O Leary" instead of "Kevin O'Leary"  and never finds it, because the Create Item action is adding what the user entered into the New Hire Name column. What would the syntax be to have the Filter Query look for both the Output from the Sanitizing as well as what was entered into the "New Hire Name" column? 

[Jcook]

Hi @Morghan 

My apologies,

This will work, I tested on a Array I created with the name Kevin O'Leare.

I am basically adding each existing name to an array, than checking the new hire if they exist in this array. Thus by passing the filter all together.

Please try:

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[v-alzhan-msft]

Hi @Morghan ,

I have made a test on my side and the issue is confirmed on my side.

I would suggest that you could Take a try the Filter Array action to filter the items and it is works:

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Morghan]

Hi @Jcook - I'm sorry, I have not used arrays before and am going through your steps but not seeing the Output as an option in the below from your post (sorry it's so zoomed out, it was the only way to fit in one screenshot) -

[Jcook]

Hi @Morghan 

As I mentioned in my post. I am using Outputs but for you switch that to your triggers output.

I am using outputs because I did not have a SharePoint trigger in my flow.

Try putting the "Needed per..." dynamic content from your picture

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Morghan]

Hi @Jcook - thank you for your patience. Got it, choosing the "Needed Peripherals" column from my trigger looked to have worked, thank you. I was confused by that being the only column available from my trigger action - I was expecting to see the "New Hire Name" as an option. When I run the test now, it always resolves to Condition = true. I think perhaps because I need to move my already existing Conditions up into the new condition we added, then move the "If Yes" and "If No" actions up, correct?-

[Jcook]

Hi @Morghan

Yes, so move your condition up into the loop. Than make your condition:
Choose the array on the left side
Contains
The right side you should be able to choose the name now like you expect.

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Jcook · Accepted solution]

@Morghan 

I have replicated your flow as best as I could.

Please try this:

FullName is the names that already exist in the Lookup list

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[Morghan]

Hi @Jcook - that did the trick!! I was also able to conceptually grasp your last suggestion the most, so thank you for helping me learn! You rock. 

Thanks again,

M