cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Build a Table using values from 2 multi select controls

We have a PowerApp that we use to manage a business process that involves multiple locations and multiple services. The scenario is that based on the selections in 2 separate multi select controls we need to build a table that allows the user to manipulate a start and end time for every combination of the two controls. 

 

Location Field Selected Values: 

  • China
  • US
  • Europe

 

Services Field Selected Values: 

  • Interview
  • Business Analysis

 

Resulting Table

ChinaInterview  
ChinaBusiness Analysis  
USInterview  
US

Business Analysis

  
EuropeInterview  
EuropeBusiness Analysis  

 

I tried using a nested ForAll, which doesn't seem to work, because the resulting values in the Collection in the below code is a Table Value and my Table isn't expecting Table values in the fields in my Table. I'm assuming I need Text, Int, DateTime, or other base types rather than the Table type.

 

 

 

ClearCollect(Tracking, {LocationID:0,locationName:"",BServiceName:"",BServiceID:0,Start:Today(),End:Today()});   
ForAll(Gallery.Selected.'Locations',
       Collect(locationDetails, {LTempId: ThisRecord.Id, LTempName: ThisRecord.Value});
       ForAll(Gallery.Selected.'Business Services',
              Patch(Tracking, {LocationID: locationDetails.LTempId, locationName:locationDetails.LTempName, BServiceName:ThisRecord.Value, BServiceID:ThisRecord.Id})));

 

 

 

I've been banging my head on this one for a bit, can one of you smart people point me in the right direction? 

 

Thank you in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Thank you for the response @FabianAckeret and sorry it took so long to report back. I was able to figure it out not too long after I posted the question. This is what I did, I wrapped each ForAll() in an As identifier like you do in TSQL, so I was able to access the outer ForAll as you mentioned. Code below. 

 

 

ForAll(DataCardValue42.SelectedItems As Location,
  ForAll(DataCardValue43.SelectedItems As Service, 
    Collect(Metrics, {
      Title:Concatenate(DataCardValue8.Text,":", Location.Value, " -  ", Service.Value),
      IncidentID: Gallery.Selected.'Incident ID',
      LocationID: Location.Id, 
      LocationName: Location.Value, 
      BServiceID: Service.Id, 
      BServiceName: Service.Value, 
      Start: DateValue2.SelectedDate + Time(Value(HourValue2.Selected.Value), Value(MinuteValue2.Selected.Value), 0), 
      End: DateValue3.SelectedDate + Time(Value(HourValue3.Selected.Value), Value(MinuteValue3.Selected.Value), 0),
      Duration: DateDiff(DateValue2.SelectedDate + Time(Value(HourValue2.Selected.Value), Value(MinuteValue2.Selected.Value), 0), DateValue3.SelectedDate + Time(Value(HourValue3.Selected.Value), Value(MinuteValue3.Selected.Value), 0),Minutes)
      });
      ));

 

This gave me the collection I needed. Now to compare this collection to existing values in a SharePoint list and reconcile the two...  

 

ugh... 

View solution in original post

2 REPLIES 2
Highlighted
Solution Supplier
Solution Supplier

@gabeconsults 

 

I hope I got it right... or at least my way can bring you on the right path.

I created 2 comboboxes storing the locations and the services. The buttons next to them fill them to a collection (collections are needed because when you are using a nested ForAll, in the inner-ForAll, you need to be able to access the outer-ForAll's value. Afaik this isn't possible with Comboboxes, yet.

// "Add to colLocations" Button
Clear(colLocations);
ClearCollect(colLocations,ComboBox1.SelectedItems)

// "Add to colServices" Button
Clear(colServices);
ClearCollect(colServices,ComboBox2.SelectedItems)

 

I then loop through both collections with this formula:

Clear(colResults);
ForAll(
    colLocations,
    ForAll(
        colServices,
        Collect(colResults,{Location: colLocations[@Value], Service: ThisRecord.Value})
    )
);

 

...and here's how it looks in action

nested-forall.gif

 


Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.

Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.
Highlighted

Thank you for the response @FabianAckeret and sorry it took so long to report back. I was able to figure it out not too long after I posted the question. This is what I did, I wrapped each ForAll() in an As identifier like you do in TSQL, so I was able to access the outer ForAll as you mentioned. Code below. 

 

 

ForAll(DataCardValue42.SelectedItems As Location,
  ForAll(DataCardValue43.SelectedItems As Service, 
    Collect(Metrics, {
      Title:Concatenate(DataCardValue8.Text,":", Location.Value, " -  ", Service.Value),
      IncidentID: Gallery.Selected.'Incident ID',
      LocationID: Location.Id, 
      LocationName: Location.Value, 
      BServiceID: Service.Id, 
      BServiceName: Service.Value, 
      Start: DateValue2.SelectedDate + Time(Value(HourValue2.Selected.Value), Value(MinuteValue2.Selected.Value), 0), 
      End: DateValue3.SelectedDate + Time(Value(HourValue3.Selected.Value), Value(MinuteValue3.Selected.Value), 0),
      Duration: DateDiff(DateValue2.SelectedDate + Time(Value(HourValue2.Selected.Value), Value(MinuteValue2.Selected.Value), 0), DateValue3.SelectedDate + Time(Value(HourValue3.Selected.Value), Value(MinuteValue3.Selected.Value), 0),Minutes)
      });
      ));

 

This gave me the collection I needed. Now to compare this collection to existing values in a SharePoint list and reconcile the two...  

 

ugh... 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (8,959)