Skip to main content

Notifications

Power Automate - General Discussion
Answered

Sharepoint List Filter Query IN Operator

Posted on by 27

Hello, guys,

I have two Sharepoint Lists. From One I take relevant Event IDs and from the second I take people who participated.

I first get all Event IDs and then I want to filter the second Sharepoint Lists only by the Event IDs I took from the first list.

 

So something like this (written in Filter Query):

VojtechSimaCZ_0-1676835806481.png

I already have the EventIDs stored in Array Variable (TrainingsList) but I can do whatever I am supposed to do if that helps.

 

I really wanna filter it in the source so I don't take all the data.

 

Could someone help, please?

Thank you

  • Verified answer
    Re: Sharepoint List Filter Query IN Operator

    Hi @VojtechSimaCZ ,

     

    I am very sorry for my wrong explanation at the beginning. The way substringof() is used in the filter query is substringof('string', column), and the logic of operation is to find all the fields containing string in the column.
    But in this case, what we are actually looking for is the column field contained in the string, so the formula should be substringof(column,'string'), but the column does not support being placed on the first parameter in the substringof(). So I suggest you to use filter array action.

     

    Best Regards,

    Dezhi

  • VojtechSimaCZ Profile Picture
    VojtechSimaCZ 27 on at
    Re: Sharepoint List Filter Query IN Operator

    @v-dezhili-msft ,
    In my case it's an array of strings, like this "Power BI - 22/10/2022", "Power Automate - 21/2/2022".

    Would this work? Could you gimme example how could I write the syntax, using variable with array of strings?

  • Re: Sharepoint List Filter Query IN Operator

    Hi @VojtechSimaCZ ,

     

    Yes, in the filter query we need to use substringof() if you want to determine inclusion, but the ID column is a numeric column and we don't convert the ID column to a string type in the filter query.

     

    Best Regards,

    Dezhi

  • VojtechSimaCZ Profile Picture
    VojtechSimaCZ 27 on at
    Re: Sharepoint List Filter Query IN Operator

    Hello, @v-dezhili-msft ,

    thank you for this, so I assume there is no possibility to filter the data right in the Get Items connector, right?

    I will definitely use your solution if the it's not possible to directly filter it in Odata Query Filter.

  • Verified answer
    Re: Sharepoint List Filter Query IN Operator

    Hi @VojtechSimaCZ ,

     

    Do you want to filter records that the current list is related to another list?
    I have a test for your reference.

    vdezhilimsft_1-1676862352733.png

     

    vdezhilimsft_0-1676862338841.png

    vdezhilimsft_2-1676862376625.png

    vdezhilimsft_3-1676862411922.png

    vdezhilimsft_4-1676862463854.png

     

    Best Regards,

    Dezhi

     

     

     

     

     

     

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

October 2024 Newsletter…

October 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #4 How to Conntact Support…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 142,733

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,750

Leaderboard

Featured topics