How to search for multiple known record IDs in a single Lookup() or Filter() Call

[martinav]

Let me explain this a little.  I am trying to make a single, efficient call to a Sharepoint database (>5000 records).  I know a unique single ID to search that willl return one value.  I have a list of ID to search.  I want do do this in a single call in order to make this a quick and efficient call.  It would be like stringing 'or' statements together in a filter.  That would capture the essense of what I want to do.  I'm avoiding ForALL() because that would make a call for every single item in the criteria array.

My example:  I think this shows what I'm wanting to do, even though this is not a workable syntax, etc.

ClearCollect(ResultingTable, With({ListIDs: [123, 423, 163,133]}, Filter(SPList, ListIDs in SPList.ID)))

[RandyHayes]

@martinav 

Unlike some other functions (like Remove) where you can provide a table of ID's, there is no function in PowerApps where you can provide a table of ID's and have only those returned.  

Your Option would be to utilize the ForAll to return a table of all the records based on the ID's

With({ListIDs: [123, 423, 163, 133]}, 
    ForAll(ListIDs,
        LookUp(SPList, ID=Value)
    )
)

I hope this is helpful for you.

[martinav]

@RandyHayes ,

Yea, that works, but breaks my premise.  Its expensive to make separate calls with a ForAll().  Especially as the table if ListIDs gets larger.  I think I will attempt to get a grab of the lastest 100 or 200 rows of data, then do the forall() statement to the resulting filtered collection.

[RandyHayes]

@martinav 

Yes, it is expensive to call any datasource functions in a ForAll (which is another reason it should be used as a function to create a table and not as a ForLoop!)

However in this case, the goal is to mimic the Filter function with specific ID's.  So, the formula I provided would do just that...a table of records just like any other filter statement, but only for those ID's.  And...at a cost!

I'm not sure that you would gain any performance changes by gathering 100 or 200 rows of data and then a ForAll over them as you will now incur the cost of gathering the 100 or 200 rows of data.

I would test that against the data first.

My assumption is that the ID list will change dynamically, but if it didn't then the ID's could be incorporated into a filter statement...but that is not the case here as I assume.

[martinav]

@RandyHayes  or anyone!!

Is there any new creative methods to handle this?