Answered

Dynamic Content in filter query behaving very strangely all of a sudden

(0) Share

Report

Posted on by jnpb

I have a Get Rows action in a flow that has a filter query with a Dynamic Content item in it. It has been working before this point.

I had modified a step a few actions later in the flow and then noticed that the flow started failing. I expected it to be to do with the step I had modified, but it was actually failing way earlier - at the Get Rows step containing the filter query entry!

The error message implies it isn't passing the expression that is represented by the Dynamic Content object. Here's what it's showing:

Screenshot 2022-11-07 150148.png

Screenshot 2022-11-07 150255.png

Screenshot 2022-11-07 150314.png

What the heck is going on with this - I tried to find reference to any similar issue online and couldn't find any relatable experience documented. Has anyone seen this happen, and how can I get past this? This breaks a fairly important process and there's no clarity on why this would simply be getting removed.

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

Sort by

Pstork1 69,383 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

What data type is the Registration Id column in Excel? And is there an actual Id column in the SQL table. Also, normally unless its an integer or a date you need single quotes around the Registration Id

Was this reply helpful? Yes No
jnpb on at

Like (0)

Report
Copy link

Link copied!

Hi @Pstork1

Registration ID column in Excel: I've tried classifying this as both a Number as well as just text to rule this out. The problem persists across both.

The SQL column does indeed have an Id field.

I did try the single quotes around the Registration ID dynamic content element and, while the flow then completes successfully, doesn't produce the actual results. The raw input at that step then looks like this:

"host": {
"connectionReferenceName": "shared_sql",
"operationId": "GetItems_V2"
},
"parameters": {
"server": "(removed).database.windows.net",
"database": "(removed)-reports",
"table": "[Operations].[RegistrationsReport]",
"$filter": "Id eq ''"

It's like the dynamic content entity is simple being ignored and considering this was working yesterday, I'm really not sure why it isn't now or what's changed.

I did add a column to the excel sheet and map that column to an SQL column in the final step of the flow (Update a row) this morning, but I can't identify why that would impede the Dynamic Content in such a way.
I even rebuilt the entire flow step by step to rule out that maybe the entity was impacted by that column change but even the rebuilt version has this same problem.

Was this reply helpful? Yes No
Verified answer

Pstork1 69,383 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

The input is suggesting that the Registration ID is null when you are using it in the OData Query. If you changed the schema of the Excel sheet today by adding a column to the table that is the issue. You need to remove the Excel connection from the flow completely, add a new one, and then wait. It usually takes overnight for the cached connection to clear. Building your flows in a solution and using a Connection Reference makes it much easier to clear the old connection and update the schema.

Was this reply helpful? Yes No
jnpb on at

Like (0)

Report
Copy link

Link copied!

OK, I've gone and rebuilt the flow end to end mirroring the existing design: presuming this is the issue, I'll wait out the caching and check in on it tomorrow. I'll circle back if things are no better - thank you so much for the insight: I really appreciate it!

Was this reply helpful? Yes No
Pstork1 69,383 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Did you create a new connection to the SQL or use the existing shared connection?

Was this reply helpful? Yes No
jnpb on at

Like (0)

Report
Copy link

Link copied!

Well in the new / recreated flow I invoked the SQL connection at the same time with the same parameters. I'm not sure how else to articulate that! (note: i am limited in my subject matter expertise here)

Was this reply helpful? Yes No
jnpb on at

Like (0)

Report
Copy link

Link copied!

Well in the new / recreated flow I invoked the SQL connection at the same time with the same parameters. I'm not sure how else to articulate that! (note: i am limited in my subject matter expertise here)

Was this reply helpful? Yes No
jnpb on at

Like (0)

Report
Copy link

Link copied!

It did indeed appear to be the cached connection issue you suggested - as of this morning the same flow, unchanged, is now working again.

Thank you for all of your insight and help with this - I really appreciate it!

Was this reply helpful? Yes No
Pstork1 69,383 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Glad its working again. I wish I knew a faster way to update/refresh a connection after a schema change.

Was this reply helpful? Yes No