ODATA substringof not valid in GET ROWS

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi All

I am trying to replicate this SQL WHERE clause in MS FLOW

SQL = WHERE telephone LIKE '%.%' , i.e. is there a dot in the telephone number

some ODATA documentation I have seen sugests I should use substring of , like this: subsringof('.',telephone)

MS Flow checker doesn't complain

The flow says it completes sucessfully

but the substringof fails with this error

{
 "status": 400,
 "message": "An unknown function with name 'subsringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.\r\n inner exception: An unknown function with name 'subsringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.\r\nclientRequestId: a5e83211-3828-44d7-a1c4-0e2717d7bfe7",
 "source": "sql-ncus.azconn-ncus.p.azurewebsites.net"
}

which is suggesting substringof is not a valid funtion. this is driving me nuts. Anybody know what the issue is?

thanks

Tony

Categories:

General topics

I have the same question (0)

All responses (10)

Answers (1)

yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

HI @Anonymous

Currently ODATA filter queries has limitations for different connectors in Flow. What works in for one service might not work in another type. For example, sertain queries and operations work in SharePoint and those might not work in CDS, SQL or Excel etc.

You might want to have a look at this blog for constructing the queries:

https://veenstra.me.uk/2018/11/12/microsoft-flow-filter-queries-in-sharepoint-get-items/

I would suggest to run a simple substring to check if the SQL connector supports this and if not, you can raise that as an idea or upvote if one already exists here:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
Jobbo83 40 on at

Like (1)

Report
Copy link

Link copied!
You've got a spelling error:

"message": "An unknown function with name 'subsringof'
That should be substringof

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Good catch @Jobbo83 . @Anonymous Please try this out and make the changes and check if you are continuing to face the issue.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
@Jobbo83 thanks. I had ben messing with the command so much, I mistyped it after trying other options. Substringof still gives the same error, albiet with the correct spelling

{ "status": 400, "message": "An unknown function with name 'substringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.\r\n inner exception: An unknown function with name 'substringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.\r\nclientRequestId: f43b1ffe-5de9-4168-9d90-2a12f113059a", "source": "sql-ncus.azconn-ncus.p.azurewebsites.net" }
I do find MS Flow fights you all the way and is very frustrating to use.

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous

Have you tried executing a simple on from the response i suggested? The blog I shared in my response provides a good insight of the ODATA filters. Reiterating that there are limitations with this filter and I am guessing that SQL connector does not support the substring() at the moment.

Hope this Helps!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@yashag2255 the blog page is actually where I found the substringof command. I am not sure what you mean by doing a simple substring, I don't think thats an ODATA command?

I did try indexof(telephone, '.') gt -1 which does run but returns no data. I know the connection works as when I do filter of customer_num eq <value>, I get a single row as expected. I might try a SQL profile to see what is going on at the SQL end, but I expect flow returns the whole table and does the filtering itself?

Thanks for you suggestions. I might just put the code on the server as a stored procedure and execute that.

tony

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Anonymous

That does the job. What I am pointing at is there are some functions that work just fine (like the eq, gt, lt etc) Som functions like Substring have limitations on certain connectors and looks like SQL does not support that right now. For which I asked to check with a simple filter query just having the substring() function and if it does not work we can be sure that it is the limitation fro the SQL connector. BTW, flow is adding new functionalities and patches regularly, so this might be resolved sooner in the next releases.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Anonymous

Please have a look at this documentation on limitations and offerings on the SQL Connector:

https://docs.microsoft.com/en-us/connectors/sql/

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Well that's interesting.

A SQL profile confirms that SQL returns the whole table to flow and flow handles the filtering and column selection. In a table with 50 columns, I only selected 2 columns and kind of expected flow to construct a SQL command with only the columns I specified and also to construct a WHERE. My rational side is telling me to create a VIEW and let flow use that (if it see views as tables) but my stubborn side wants to play with ODATA. the link above is implying IN might work.

I somewhow got subsringof('.',telephone) to return data, I've no idea why this started to return data, it didn't before (maybe I had a typo), but it is not doing what I expected. I expected it to return rows that had a . in the telephone field, but it is returning rows with a black telephone field! I'll experiment some more.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

couldn't get ODATA to work. in the end a created a SQL view that did the filtering I needed and used that.

Was this reply helpful? Yes No