cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davies
Level: Powered On

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
Level: Powered On

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.

10 REPLIES 10
Super User
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
Level: Powered On

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

Super User
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
Level: Powered On

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.

Super User
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
Level: Powered On

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

Super User
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!

Super User
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
Level: Powered On

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
Level: Powered On

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.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Users Online
Currently online: 135 members 4,578 guests
Please welcome our newest community members: