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?

[R3dKap]

Ok, here you go... I've made an app to show you how to do it... On this app there are 2 screens.

On the first one, when you change the selection of a parent combobox it removes the wrong items from the children comboboxes. But there is a bug: sometimes, the selection of the children is right but when you open the combobox the items don't appear selected in the list, thus allowing the user to select an item twice. I can't figure out why...

On the second screen you have a fully functionnal version but where the children are reset when a parent combobox's selection is changed.

Hope this helps you in any way...

Emmanuel

https://gallisdev3-my.sharepoint.com/:u:/g/personal/emmanuel_gallisdev3_onmicrosoft_com/EaJm2FLJkwxN...

[SachinKukreja]

Hi Emmanuel (@R3dKap )

I tried logging into the App but the data sources could not be refreshed and hence could not see the screens in action. Attaching a screenshot of the error.. Is this because I don’t have the access to the file you loading on your one drive? Attaching the error message for your reference.

[R3dKap]

Hi @SachinKukreja,

That's because when you import an app in your tenant, you have to remove all datasources in it and replace them with your own datasources. The package does not include the Excel file which, in addition, still points to my own OneDrive to which you don't have access.

So what you need to do is remove the Table1 OneDrive datasource from my app and add you own link to your own Excel file (which, of course, should have the same structure as the one you sent to me) located in your own OneDrive. During the process you might see errors everywhere but everything will come back to normal once you add your own datasource.

Note: on my side I had to remove the ID column from the file you sent me because it had formulas which is not supported by the OneDrive for Business connector. Make sure you don't have any formulas in your table as well.

[sswarna]

HI (@R3dKap ) I am facing the same issue. App did not open for me. i could only see MS visual studio and few other random files. Am i missing anything?Could you help?

[R3dKap]

@sswarna, do not decompress the zip file. Go to make.powerapps.com, click Apps on the left pane, then click Import canvas app. The system asks you for a package file. Click Upload, select the zip file and click Import button. Once the application has been imported, edit it with the Power Apps Studio, remove the Table1 datasource and add your own datasource.

[SachinKukreja]

@R3dKap thanks so much for your help! This worked very well.

[ehabatme]

I need your support since the formula is not working to cascade combobox from SharePoint Lists

My case is very simple, I have 3 Lists:

1. Category (1 Column)
   1. CatName
2. SubCategory (2 Columns)
   1. SubCatName
   2. CatID (Lookup from Category List)
3. Product (3 Columns)
   1. ProdName
   2. CatID (Lookup from Category)
   3. SubCatID (Lookup from SubCategory)

How can I get the list of Subcategories based on the Category Selection

[R3dKap]

Hi @ehabatme,

Have you tried using the native feature for connecting combo boxes provided by Power Apps?

[TonySuslovich]

Hi @R3dKap I've already spent so much time trying to figure this out so I decided to go straight to the source (you).

I have a scenario (this is connecting to Dynamics 365) where I have 2 entities:

Business

Sub-Business

I have 2 combo-boxes (1 for Business and the other one is for Sub-Business). Sub-Business entity is a child of Business and has GUID attribute that points to a record from Business entity. When value of the Business combo box changes, I want to filter Sub-Business combo-box to only show Sub-Business records that have parent as Business selected. Pretty common scenario I believe. I can do any sort of filtering on Sub-Business combo-box and it works fine but the moment I try filtering on GUID (Business ID), it breaks. I tried leveraging Depends On functionality and I believe I'm setting it up correctly, but it's giving me an error:

Any pointers would really be appreciated.

Best regards,

-Tony Suslovich.