cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MNixey
Advocate II
Advocate II

Dynamic table inside forall loop

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

 

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

10 REPLIES 10
WarrenBelz
Super User
Super User

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
Helper V
Helper V

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

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? 

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 ?

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

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

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

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
      

 

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,494)