Getting 'the column of the table wasn't found' even though its clearly there and correctly typed - some sort of wierd interaction with Design view of view its pulling from?

(0) Share

Report

Posted on by Mtelf

I built a flow in Power Automate a while ago to send rows to a Google Sheet.

It makes use of the Get Rows (v2) for SQL

For simplicity in my Select Query I use a view which is built from a much more complex SQL query on the database (so all I have to do is select column names).

This has all worked great. Now the client asks me to add in 3 extra columns. One of the requirements is getting the earliest date from multiple date columns as a single column. I delete the view, add in the three columns and rebuild it. Query it in SSMS, all working fine.

Then I go to add in the 3 extra columns, comma separated into the Select Query in Get Rows (v2) in Power Automate.

I get an error:

"status": 400,

"message": "The column '[thenameofmycolumn]' of the table wasn't found.

I double check my select is exactly the same, down to case sensitivity, and they are.

I remove each column and add it in one at a time to see if its some weird thing with a particular column. One works fine, but the other two don't.

I check if its to do with a column limit by removing other columns, but its not that either.

Finally I try removing a column that errors in the design view in SSMS. It's built in the SQL like:

,(SELECT MIN(ArrayofDates)

FROM (VALUES (Date1),(Date1),(Date1),(Date1),(Date1),(Date1),(Date1)) AS EarliestDate(ArrayofDates)) as ArrayofDates

Which outputs the earliest date of the group of dates. Works fine in running query, works fine in CREATE VIEW, and works fine in Select of the view, but if I right click the view and select design I get:

Error in FROM clause: near 'VALUES'.
Error in FROM clause: near ')'.
Unable to parse query text.

I'm tearing my hair out at this point - It is not a typo on my behalf. Why is Power Automate doing this? Does GetRows have some weird interaction or something I'm not understanding with views that give Design view errors? If I have to I'll resort to a CASE statement to see if it gets around the issue but its a PitA to write a CASE statement compared to the statement above.

Categories:

Using connectors

All responses (2)

Answers (1)

Sort by

DamoBird365 8,942 Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Mtelf24

It's a guess but do you have spaces in the field names or were they renamed at any point?

Hop over to list settings (cog->list settings) and click on the offending two column names as if you were going to edit them. Then take a look at the end of the URL in the address bar where it will list the official name of the column.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here
Verified answer

Mtelf 11 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks @DamoBird365

The fields have no spacing. I could use "" around named fields to keep spaces in my SELECT but I either have no spaces or would use _ with this query.

Not sure I follow when you refer to list settings - that sounds like SharePoint. This is a SQL database for CRM on premises. I did look at the 'Show raw inputs' bit of the log for the failed query and the field its complaining about exactly matches the name of the one in the view.

Anyway, for what its worth, it started working fine when I turned the output from the select sub query in my first post to a CASE statement. Had to make use of COALESCE to deal with the null date scenarios. The name of the output field in both cases was exactly the same, all I did was change the logic behind the output of the field.

Therefore my only assumption is either Power Automate has a problem with parts of queries that cause the design view to error in SSMS (which seems weird, but maybe it uses a similar query method to the design view - I dunno, well beyond my expertise), or the only other thing I can think of is it takes exception to more than one subquery in what sits behind the view (as my query to create the view already has one subquery before I add in the one in my first post). I wouldn't have thought in either case Get Rows v2 would care about that, it should be just a basic select from the view/