Skip to main content

Notifications

Power Automate - General Discussion
Answered

ODATA substringof not valid in GET ROWS

(0) ShareShare
ReportReport
Posted on by

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

 
  • Verified answer
    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.

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

  • yashag2255 Profile Picture
    yashag2255 24,442 on at
    Re: ODATA substringof not valid in GET ROWS

    @Anonymous 

     

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

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

  • yashag2255 Profile Picture
    yashag2255 24,442 on at
    Re: ODATA substringof not valid in GET ROWS

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

  • 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

  • yashag2255 Profile Picture
    yashag2255 24,442 on at
    Re: ODATA substringof not valid in GET ROWS

    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!

     

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

  • yashag2255 Profile Picture
    yashag2255 24,442 on at
    Re: ODATA substringof not valid in GET ROWS

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

  • Jobbo83 Profile Picture
    Jobbo83 40 on at
    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

  • yashag2255 Profile Picture
    yashag2255 24,442 on at
    Re: ODATA substringof not valid in GET ROWS

    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!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard