Auto Populate a second field based on Multi-Select Combo Box

[doctorscience]

Hi all,

I have a sharepoint list with 'Office Location' and 'Region', where 'Office Location' is a Multi Select Combo Box.

We have staff that can attend multiples sites sometimes across multiple regions.

What I am looking to do is have the 'Region' field auto populate based on the 'Office Location'.

An example of the lookup list data could be

Office 1 | North

Office 2 | North

Office 3 | East

Office 4 | East

Office 5 | South

What I would need to be able to do, is that if a user picks Office 1, Office 2 and Office 5, Region would auto populate with North and South, with North only showing once.

Any ideas of how I could achieve this?

[Dipak_Shaw]

@doctorscience Would you confirm if your region field is a text field or a choice field?

[doctorscience]

It can be either, whichever is simplest to implement as it won't be user editible.

[Dipak_Shaw · Accepted solution]

In that case you can directly show the region either in a label or if you have a text field then in the default of a text control with following expression -

Concat(Distinct(cmbOfficeLocations.SelectedItems,Region),Result,", ")

Output: North, South

If you find my answer helpful, Please Accept as Solution. It will help others with same issue. Thanks

[doctorscience]

@Dipak_Shaw Perfect that is exactly what I was looking for, thank you!

[doctorscience]

@Dipak_Shaw apologies to ask again, I am using a sharepoint list for the lookup, so don't think I can use your exact method. I have been trying to get distinct to work with it but I can't quite get my head around it.

Concat(
       ForAll(
      cmbOffice.SelectedItems As aSel,
      LookUp(
          'OfficeList',
          Office = aSel.Value
      ).Region
   ),
   Value & ","
)

I have this above code working, where OfficeList is a sharepoint list being used for the lookup, and contains two columns, Office and Region.

However this displays multiples as seen below. How could use distinct in the above table setup?

[Dipak_Shaw]

@doctorscience 

May I know where you are using this above line of code? In a button or Text Property?

[doctorscience]

@Dipak_Shaw that is in the text property yes. And it will show the corresponding Region for each office, I just need to remove duplicates. Thanks.

ChatGPT had suggested the below code but it doesn't work.

Distinct(
   Filter(
      'Your SharePoint List Name',
      Office in OfficeLocationComboBox.SelectedItems
   ),
   Region
).Result

[Dipak_Shaw]

Here is my question,

Why you need LookUp or Filter?

If the same combo box is connected to the office list then you can directly get all the regions selected in the combo box using cmbOffice.SelectedItems but this code return selected items as a table so now you have to distinct the selected values and thereafter concatenate using the concat function.

[doctorscience]

@Dipak_Shaw

Apologies, maybe I should have explained what I am doing with the form.

I am using the form to populate a sharepoint list. 

The list with the corresponding Offices and regions is a separate list that I need to lookup to match the corresponding region to the office.

I am using lookup fine for single selection dropdowns in other cases, but it's the fact that this is a multi select combo box and that there are more than one office per region which is causing me woes. Hopefully that makes sense of what I'm trying to do?

Maybe I'm not understanding your initial suggestion properly, apologies I am still very new to Power Apps.