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
couldn't get ODATA to work. in the end a created a SQL view that did the filtering I needed and used that.
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.
@Anonymous
Please have a look at this documentation on limitations and offerings on the SQL Connector:
@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!
@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
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!
@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.
Good catch @Jobbo83 . @Anonymous Please try this out and make the changes and check if you are continuing to face the issue.
You've got a spelling error:
"message": "An unknown function with name 'subsringof'
That should be substringof
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!