Skip to main content

Notifications

Community site session details

Community site session details

Session Id : xpTrfFH8C1InKF67nk7Act
Power Automate - Using Connectors
Answered

SubstringOf Query SharePoint Get Items Referencing Expanded Fields

Like (0) ShareShare
ReportReport
Posted on 26 Sep 2019 03:41:05 by 55

Hi All,

 

I am having a difficult time figuring out how to create a filter based on nested data. If I add a Filter Query to my [SharePoint] Get Items action referencing CN, I have no problems. However, when referencing the nested data, I cannot get the query to return data. What is the proper syntax for getting the substring of a nested value?

 

Sample Filter Query

substringof('T',CN)

Nested Sample Filter Query (Not working)

substringof('C',V_ID/Value)

Sample Return Array Record

{
"@odata.etag": "\"3\"",
"ItemInternalId": "5859",
"ID": 5859,
"CN": "999-T06-99-AS-01",
"MA_ID": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1352,
"Value": "Q24389PT"
},
"MA_ID#Id": 1352,
"C_RF": "Alice Smith",
"C_PM": "Georgia Muldrow",
"V_ID": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 42,
"Value": "ABC Company"
},
"V_ID#Id": 42,
"S_ID": {
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 2,
"Value": "Kamasi Washington"
}
}

 

  • ZMSPAC Profile Picture
    6 on 29 May 2020 at 17:59:14
    Re: SubstringOf Query SharePoint Get Items Referencing Expanded Fields

    Hi @Olivia_Dudley ,

     

    I have a similar issue. How did you setup your filter array?

     

    Thank you

  • Olivia_Dudley Profile Picture
    55 on 30 Sep 2019 at 17:55:52
    Re: SubstringOf Query SharePoint Get Items Referencing Expanded Fields

    Thanks @v-bacao-msft,

     

    I am currently using the Select action to bring any nested fields to the same level. My workaround will have to be utilizing the Filter array action as suggested. Although, I'd like to see if someone has a solution where I could call on the nested string in the Filter Query property of the SharePoint Get Items action. This way, my data set does not need to be filtered twice.

     

    Thanks for the link... John Liu is always a great resource!

  • Verified answer
    v-bacao-msft Profile Picture
    on 27 Sep 2019 at 01:13:23
    Re: SubstringOf Query SharePoint Get Items Referencing Expanded Fields

     

    Hi @Olivia_Dudley ,

     

    I did a test on my side, it seems that the parameter required by the substringof() function is a field of type string.

    Please consider using Filter array or Condition to filter items.

    In addition, you could also use Parse JSON to parse the Body of Get items, which can also easily refer to the corresponding field value.

    Please refer to this blog to learn more about the use of Parse JSON.

    http://johnliu.net/blog/2018/6/a-thesis-on-the-parse-json-action-in-microsoft-flow

     

    Best Regards,

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

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,745 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard

Featured topics

Loading started