Cascading Combo Box

[Anonymous]

Hello,

I had previously customized a SharePoint list form using PowerApps successfully early in January. However, after the 1/18/2018 updates, I can no longer customoize a SharePoint list form the same way.

I have a SharePoint list, Expense Report, that is going to be used to record expenese incurred on projects.  This list has 2 SharePont lookup fields from 2 other SharePoint lists - Clients and Projects.  We'll first select the Client and then based on that the associated Projects would be displayed for selection.  Previously, I had done this using galleries and within the edit form the Client and Project fields were automatically populated with the selections made within the galleries (I used WonderLaura's videos/blog posts).  The fields within the edit form were the Lookup control that was provided; however, now it seems the Lookup control has been replaced with a Combo Box control.

I have not been able to find much guidance on the use of Combo Boxes and it is unclear to me what properties to use and modify in order to be able to record the selection of Client and Project from the galleries set up.  This is the formula i had previously used for the Lookup controls for the Default property of the data card:

{ '@odata.type' : "Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", Id : Value(GalleryClient.Selected.ID), Value: GalleryClient.Selected.Title}

Does anyone have any recommendations with how to achieve cascading combo boxes and create records for a SharePoint list using customized PowerApps forms?

[Anonymous]

Hi @R3dKap ,

Thank you. it worked. 

Another question w.r.t Comboboxes: 

Is there a way to set Display field of combobox as ColA from table and value field as ColB. i.e if i select any value in the combobox, i want to store its corresponding value into excel.
If this is acheivable, Also can you please help me how to make this work by using Distinct.

thanks,

-Dileep

[R3dKap]

Hi @Anonymous,

Can you please give me a detailed example of what you want to do?

Thanks,

Emmanuel

[Anonymous]

Hi @R3dKap ,

Lets say i have a Emp table with 3 columns EmpName, EmpId, DeptName. Now i need to setup a combobox that shows distinct values of EmpName. Now When i hit save button, I need to save comma seperated EmpIDs of selected EmpNames from combobx into my output table. 

Can you please help me in acheiving this functionality.

thanks,

-Dileep

[R3dKap]

@Anonymous,

Ok, I see. Well to do that you could use the Concat() function like this:

Concat(ComboBox1.SelectedItems, EmpId & ",")

This will generate a string with the ids of the selected items like this:

3,5,7,1,

Then, use string manipulation functions to remove the last "," if needed.

Hope this helps,

Emmanuel

[Anonymous]

Hi @R3dKap ,

It is not working when i use concat function it is only accepting Concat(ComboBox1.SelectedItems, Result & ",").

one question here. if i am using EmpName as the field for combobox will the EmpId be accepted?

thanks,

-Dileep

[R3dKap]

That's because we're using the Distinct() functions which returns a "Result" single-column table.

So, if you keep the Distinct() inside your Items property of your combo boxes, you will not be able to retrieve the list of selected ids unless you use a function like ForAll() to loop through your items and get their ids. But then your app starts to be more complicated.

Maybe if you expose here what is your original need, we might find a better way of implementing it...

[Anonymous]

This solution did not work for me. Could you please help me with cascading combo boxes. I have created a custom power app form from a sharepoint list and it has created the drop down fields as combo boxes and the values are grayed out. when it try to customise anything for making them or cascading drop downs.

[R3dKap]

Hi @Anonymous,

Have you unlocked your datacards?

[SachinKukreja]

@R3dKap  Hi i am looking for your guidance on a similar problem I am facing. I am trying to make a 5 cascading combo boxes in the power App from an excel file. My requirement is to have a ability to select unique multiple selections in the first combo box and then the subsequent combo box gives the user a ability to select multiple unique drop downs for the multiple selection made in the previous drop downs. I am attaching the sample file which i am working on.. the original file has more than 10k records in it.. 

I am trying to design in a way that the user can select a country( so this does not need to be a combo box, it can be a normal drop down as well), basis which the company codes combo box 1 comes out, user select 1 or many company codes , basis which another combo box 2 comes into play which displays the "Category" based on the multiple selections done in the the previous combo box , then basis selection of 1 or many categories , combo box 3 comes into play and displays the related " Sub Categories" , then combo box 4 comes in which shows various "GL Accounts" based on the various sub categories selected in the previous combo box and then Combo box 5 comes in to play which shows the "Tax codes" associated with the various GL Accounts. 

This is what i have on my Combo boxes till now

Combo Box 1

Items- Distinct(Filter(Table1, Country = Dropdown9.Selected.Result), 'Company Code').. drop down here is representing the country listings

On change- UpdateContext({locCol2WrongValue: IsBlank(LookUp(Table1, 'Company Code' =ComboBox1.Selected.Result && Category =ComboBox2.Selected.Result))})

DisplayFields -> ["Result"]
    SearchFields -> ["Result"]
    SelectMultiple -> True
Reset- false

Combo Box 2

Items- Distinct(Filter(Table1, 'Company Code' = ComboBox1.Selected.Result), Category) 

On change- UpdateContext({locCol2WrongValue: IsBlank(LookUp(Table1, Category =ComboBox2.Selected.Result && 'Sub Category'=ComboBox3.Selected.Result))})

DisplayFields -> ["Result"]
    SearchFields -> ["Result"]
    SelectMultiple -> True
Reset- false

  Combo Box 3

Items-Distinct(Filter(Table1, Category = ComboBox2.Selected.Result),'Sub Category')

On change- UpdateContext({locCol2WrongValue: IsBlank(LookUp(Table1, Category =ComboBox1.Selected.Result && 'Sub Category'=ComboBox2.Selected.Result && 'GL Account' =ComboBox3.Selected.Result))})

DisplayFields -> ["Result"]
    SearchFields -> ["Result"]
    SelectMultiple -> True
Reset- locCol2WrongValue

 Combo Box 4

Items-Distinct(Filter(Table1, 'Sub Category' = ComboBox2.Selected.Result),'GL Account')

On change- UpdateContext({locCol3WrongValue:false})

DisplayFields -> ["Result"]
    SearchFields -> ["Result"]
    SelectMultiple -> True
Reset- locCol2WrongValue || locCol3WrongValue

Combo Box 5

Items-Distinct(Filter(Table1, 'GL Account' = ComboBox4.Selected.Result),TAXITEMGROUP)

On change- UpdateContext({locCol3WrongValue:false})

DisplayFields -> ["Result"]
    SearchFields -> ["Result"]
    SelectMultiple -> True
Reset- 

Results till now-

I have reached a stage in the App where i loaded the excel as data source got the combo boxes going.. it gives the user to select multiple selection in first combo box but in the subsequent combo box it picks the related fields of the first or the last selection in the first combo box. what am i missing? Really appreciate your guidance here

Thanks

Sachin

[R3dKap]

Hi @SachinKukreja, I haven't forgotten you but I didn't find any time today to have a look at your problem. I'll definitely have a look at it by this week-end.

See you soon,

Emmanuel