2

I have a PowerApps form connected to a SharePoint Online list. My SharePoint list titled "Log" has a multi-select Choice column named "Service", which appears in PowerApps as a combo-box. The user can select 1 or more "Services" from this combo-box. Based on the selection of services, a number of related "Agencies" should appear in a Gallery on the form. The Agencies are defined against each Service in the Services list.

Log List:

  • Services: Multi-select lookup

Service List:

  • Title
  • Agency (Multi-select Choice, all possible agencies for this Service)

While I can get the selected services from the combo box, the problem is that I am unable to retrieve the values from the Agency multi-select Choice field in the same list. What I have tried is to get the selected Services from the dcvServices control (into SelectedServices) and then collect the related Agency values (into AvailableAgencies) during the OnSelect of the Service dropdown:

ClearCollect(SelectedServices, Filter([@Service], Title in dcvServices.SelectedItems.Value));
ClearCollect(AvailableAgencies, SelectedServices.Agency);

When I try to hook up my Gallery control to the AvailableAgencies collection, I can't quite getthe Agency values to display. I get the following error:

enter image description here

DRVR
  • 163
  • 2
  • 12

1 Answers1

2

Let's walk through an example, which should shed some light on this problem. If my Service list has these values:

Example services

And the services 1 and 4 are selected in the combo box:

enter image description here

Then the expression ClearCollect(SelectedServices, Filter(Service, Title in dcvServices.SelectedItems.Value)) will assign to the SelectedServices collections two items (Service1, Service 4), each of them having a list of agencies associated with it.

If we try to collect the agencies in a separate collection as is, with the expression ClearCollect(AvailableAgencies, SelectedServices.Agency), you will not have the individual agencies in the collection; instead, you will have two records, each of them with a collection itself:

[
  Agency: [ { Value: "Agency 1" }, { Value: "Agency 2" }, { Value: "Agency 3" } ],
  Agency: [ { Value: "Agency 1" }, { Value: "Agency 4" }, { Value: "Agency 7" } ]
]

And if you have this collection in a gallery, and try to display the value of the agency in a label (which requires a single text value), this won't work (as you stated):

ThisItem.Agency.Value

Because this is not a single text value, but a collection of them (for example, the first item would be the collection of agencies 1,2,3). You could show all values for that specific record using the Concat function, as shown below:

Concatenating values for each record

This may not be what you need; if you want all of the individual agencies that are associated with the selected services, you will need a different expression:

Clear(AvailableAgencies2);
ForAll(
    SelectedServices,
    Collect(AvailableAgencies2, ThisRecord.Agency.Value))

And with that you will have the agencies only in your collection:

Agencies only

Notice that since both service 1 and 4 were associated with the Agency 1, it appears twice. If you don't want that, you can use the Distinct function to filter any repeated values out:

Clear(AvailableAgencies2);
ForAll(
    SelectedServices,
    Collect(AvailableAgencies2, ThisRecord.Agency.Value));
ClearCollect(AvailableAgencies3, Distinct(AvailableAgencies2, Value))

And that should give you the list of unique agencies associated with the selected services.

carlosfigueira
  • 85,035
  • 14
  • 131
  • 171