Dynamic table inside forall loop

[MNixey]

Morning All,

Matthew Devaney has a post that talks about exceeding the 2000 row limit for collections. It's a great post and almost exactly what I need. Is there a way to make the table in the below code dynamic:

Clear(colCars);
ForAll(
    ["Ford", "Dodge", "Toyota"],
    Collect(colCars, Filter('Car Inventory', CarMake=Value))
);

Instead of Ford, Dodge and Toyota, I have six names. I could obviously hard type the six names but I was hoping there's a way to all ow me to create the list of names dynamically. My thoughts were to collect a distinct list of names from the sharepoint list and then dynamically create a table from those names. Is that even possible? Any help appreciated.

Cheers,

Mark

[WarrenBelz]

Hi @MNixey ,

If you had a multi-select combo box and (for instance) the Items were

CarModel.Make

then your code would be

Clear(colCars);
ForAll(
   ComboBoxName.SelectedItems, 
   Collect(
      colCars,    
      Filter(
         'Car Inventory', 
         CarMake = Make
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Waegemma]

Hi @MNixey ;

@WarrenBelz is right.

I only want to add, since you asked about populating the list of carmakes: you can't get the different carmakes via Distinct because Distinct can't be delegated.

An option is to make carmake a choices column in your list and setting the items of the above multi-select combo to Choices(YourList.YourCarMakeColumn).

Marc

[MNixey]

Thanks @WarrenBelz and @Waegemma . I am wanting to run this on start. I have a sharepoint list with about 600 records per person. The number of records per person will be pretty consistent but people will come on and drop off the list. Do you reckon that's doable? 

[WarrenBelz]

Hi @MNixey ,

When you say 600 per person, are you wanting a list of distinct values based on a lookup to their email or name ?

[MNixey]

Thanks very much for the quick replies Warren. Each person (based on name) has about 600 records against their name. The column containing the names is called field_18...

[WarrenBelz]

Hi @MNixey ,

I am guessing a bit here, as your initial post was an example. Do you have a list of people you want to apply this to ?

If so

ForAll(
   PeopleSPList As aCol,
   Collect(
      colMyCol,
      Filter(
         MySPList,
         Field_18 = aCol.ListPersonField
      )
   )
)
      

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[MNixey]

Thanks so much. I'm still struggling a little but I feel like it's my lack of understanding (and I'd like to work through it for a while). I'll have a crack with this, see if I can get it to work and let you know

[MNixey]

Sorry Warren,

My ignorance has got the better of me. I've never seen the "as aCol" syntax before but I've commented what I think is going on. 

And just in case I haven't explained myself very well. I have a sp list (called Karbon) with about 5000 records in it. I'm trying to get this into a single collection on start by creating a collection that uses a for all loop to loop through each person's records from the Karbon list. I can get this to work with static names but it would be ideal to make it dynamic as names change. Thanks again.

// this collects the distinct values from the SharePoint list's people field (this people field is type text)
Collect(PeopleSPList, Distinct(Karbon, field_18));
// colMyCol is the collection that I want to use as my output containing all the records matching the names
// in the PeopleSPList collections
Clear(colMyCol);
ForAll(
// i'm not what this next line means
   PeopleSPList As aCol,
   Collect(
      colMyCol,
      Filter(
         Karbon,
         // .Result is the only option provided in the automcomplete - it gives no error but colMyCol is empty
         field_18 = aCol.Result
      

[WarrenBelz · Accepted solution]

Hi @MNixey ,

That is why I asked you if you had a list with the names you wanted to include - you have to get the data from somewhere. If you are saying the names are only in the list with the 5,000 items, then you cannot get the Distinct Values you are after as Distinct() is not Delegable. Please confirm where the relevant names are located. The only other possibility is if all the names are present in the newest 2,000 records, but I will not go there until you confirm.