cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davies Advocate II
Advocate II

ODATA substringof not valid in GET ROWS

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

 
1 ACCEPTED SOLUTION

Accepted Solutions
davies Advocate II
Advocate II

Re: ODATA substringof not valid in GET ROWS

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

View solution in original post

10 REPLIES 10
Dual Super User
Dual Super User

Re: ODATA substringof not valid in GET ROWS

HI @davies 

 

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!

Jobbo83
Frequent Visitor

Re: ODATA substringof not valid in GET ROWS

You've got a spelling error:

 

"message": "An unknown function with name 'subsringof'

That should be substringof

Dual Super User
Dual Super User

Re: ODATA substringof not valid in GET ROWS

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

davies Advocate II
Advocate II

Re: ODATA substringof not valid in GET ROWS

@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.

Dual Super User
Dual Super User

Re: ODATA substringof not valid in GET ROWS

Hi @davies 

 

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!

 

davies Advocate II
Advocate II

Re: ODATA substringof not valid in GET ROWS

@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

Dual Super User
Dual Super User

Re: ODATA substringof not valid in GET ROWS

@davies 

 

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!

Dual Super User
Dual Super User

Re: ODATA substringof not valid in GET ROWS

@davies 

 

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

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

davies Advocate II
Advocate II

Re: ODATA substringof not valid in GET ROWS

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.

davies Advocate II
Advocate II

Re: ODATA substringof not valid in GET ROWS

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

View solution in original post

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (6,732)