Multi Select Cascading Dropdowns/Comboboxes via multiple Lists which save correctly

[mariominiaci]

I'm fairly new to Powerapps so apologies if this question has already been answered. I have read many answers and none seem to work for my situation. I have learned the lesson to delete the datacard and re-add the field when I get too confused !

My question -

In my app I want to create new entries in my main Sharepoint list called Requests. It has several columns and there are 2 particular ones which are Lookups both with multiple entries allowed.

The first column is Site and it is a Lookup from a separate Sharepoint list called Sites which itself contains the columns 'Site' and 'Site Description'.

The second column is Department and it is a Lookup from a separate Sharepoint list called Departments which itself contains the columns 'Department', 'Department Description' and 'Site'.

I do not wish to store the descriptions in the Requests list.

I have a first Combobox ddSite which allows multiple sites e.g. 'GB', 'US' and they save correctly to the Sharepoint list. I had set up a combo which showed Site and Site Description successfully but it would not save on Submit or Patch so I have abandoned that for now..

The second Combobox is ddDepartment eg 'HR', 'Legal' and this one will only save correctly to Sharepoint if I do not attempt to filter or constrain it. I want to restrict the contents of the ddDepartment choices based on the selected ddSite items (using the Department.Site to link the two I guess). However, whilst I have managed to restrict the contents, the values are not saved into the Request list when I do this.

Minimum needed - restricting ddDepartment by the selections in ddSite and have them both save in Sharepoint Requests list. (eg, I choose 'FR' and 'DE' and 'IT' in ddSite, and 'HR' and 'IT' in ddDepartment).

I nearly got this apart from the save of ddDepartment:

Filter(Departments As Depts, 
Sum(ForAll(ddSite.SelectedItems, If(ThisRecord.Value in Depts.Site, 1, 0)), Value) > 0)

This gives me the right list of values but does not save ddDepartment, only ddSite.

Ideal - same as the minimum but both ddSite and ddDepartment dropdowns show their descriptions in the app and are searchable too.

Thank you in advance!

(Edited - I wasn't using any Dropboxes. Current state is I can save if I don't constrain the ddDepartment combo at all)

[RandyHayes]

@mariominiaci 

You mentioned both patch and submit...so which are you using?  Are you using a form?  If so, what are the Update properties of the datacards for the fields you are having problems with?  And also, what is the Items property of your dropdown controls (and are they actually dropdowns or comboboxes)?

[mariominiaci]

Hi @RandyHayes , I knew I would forget some essential details.

I'm using Patch in the following way:

Patch(
    'Requests',
    varFormData,
    Form1.Updates,
    Form1_1.Updates,
    Form2.Updates,
    Form4.Updates,
    Form1_4.Updates,
    {
        Date_Raised: Now(),
        Requestor_Name: User().FullName,
        Requestor_Email: User().Email
    }
);

 My app is split across multiple screens and all the forms refer back to varFormData.

Update properties are:

ddSite card: DataCardValue20.SelectedItems <-- the name of the actual dropdown control.

ddDepartment card: DataCardValue3.SelectedItems<-- the name of the actual dropdown control.

Items of the dropdowns (they are dropdowns now as the comboboxes weren't saving for me):

ddSite = Choices([@'Requests'].Site)

ddDepartment = Choices([@'Requests'].Department) <--- I have removed all my attempts to filter it down, as the app has to save data.

[RandyHayes]

@mariominiaci 

Yes, so first, you should not split forms with that method.  Using a patch across your forms will break features and functionality of the forms...which really kind of wastes the whole purpose of forms!

If you want to learn how to split forms properly and not break the form, then please review this video on the topic.

There is quite a bit of confusion I am having on your scenario - you mentioned the Update properties are referencing the controls of DataCardValue20 and DataCardValue3, but you are also indicating that the dropdowns are called ddSite and ddDepartment.  So the update is not referencing the right controls. 

Also, your Update properties reference the SelectedItems property of the other controls...dropdowns will not have a SelectedItems property!

As for your dropdowns in general...the Items properties are correct for the most part.  I realize you want to actually have the department list only show the departments related to the site selections.  We'll cover that once you have something working to start with.

So, if you could clarify some of the above...that would be helpful!

[mariominiaci]

Thank you for the video link, I will review it today.

The columns in the Requests list are called Site and Department, and I have renamed the cards (which contain error, star, dropdown and key) to ddSite and ddDepartment in the tree of my app. Inside of ddSite there is a dropdown called DataCardValue20 and inside of ddDepartment there is a dropdown called DataCardValue3. I hope that is a bit clearer.

I have undone the Filter/Sum from my original post as, whilst it was allowing me to pick the values with restrictions, it wasn't saving and I didn't fully understand the logic so I want to avoid extra risk.

[RandyHayes]

@mariominiaci 

Okay...a little clearer.  But, there is ONE part that is glaring out at me.  If you truly have a DropDown called DataCardValue20, then you should have an error on the Update where you have DataCardValue20.SelectedItems as Dropdown controls do NOT have selected Items.

Also, the fact that you refer to ddSite.SelectedItems (in your original post) is very confusing because you are stating that ddSite is a datacard (which would not have a selectedItems property) and you are stating that it is not a combobox (DataCardValue20), it is a dropdown (which again, would not have a selectedItems property)

Very confused on what you currently have, so finding it hard to provide the next steps for you to help resolve.

[mariominiaci]

Apologies for the confusion, I can assure you I am at least as confused. They are in fact both comboboxes and not dropdowns - it's been a long day! 😅

It's probably best to ignore the code snip in the original post as I have unwound that change and can no longer recreate the behaviour of 'limiting and selecting departments ok but not saving to the Requests list'. I think I had that in the DefaultSelectedItems, but it's now royally ignoring me and showing all the Departments anyway (which does not make sense data wise).

I have watched your video about Patching in the interim (thanks) and will implement those changes. I had followed a method which duplicated the form and cut away the different parts but I like the idea of using SubmitForm instead so will try and add it carefully without breaking more stuff...

Additional - I've seen some solutions using concatenation of values but I'm not sure if they are still current so I've stayed away from trying them. I really want to understand what I'm doing to maintain it. I have another similar case which requires one final value only (i.e. cascade of comboboxes which filter down to one value) and it works fine, but I am still getting my head around collections.

[RandyHayes]

@mariominiaci 

No problem.  I understand that starting out things can get a little confusing pretty quickly!

Don't waste your time on collections!!  They are way overused in examples and not needed unless you have a need to have all of your data in-memory and the ability to then add/remove/update records in the in-memory table.  In your case (so far) there is no need for them.

The next big thing to keep in mind is...keep it simple!!  Most of what you are trying to do, PowerApps will do for you.  The problem (especially being new to it), is that you don't yet know how and where it will do that, so you proceed to try and make it all work on your own...and that just complicates things!

But, since we shuffled around a bit of confusion in the prior posts, I want to get clear on your scenario now.

Please clarify to following and correct as needed:

- You have a Combobox called DataCardValue20 in a datacard called ddSite

- You have a Combobox called DataCardValue3 in a datacard called ddDepartment

- The underlying column for ddSite is a column (most likely called) Site.  That column is a Lookup column. 

- The underlying column for ddDepartment is a column (most likely called) Departement. That column is a Lookup column.

If the above is correct so far, then What is the name of the list that the Site lookup is set to?  And, what is the name of the list that the Department lookup is set to?

[mariominiaci]

Your clarifications are correct:

- You have a Combobox called DataCardValue20 in a datacard called ddSite

- You have a Combobox called DataCardValue3 in a datacard called ddDepartment

- The underlying column for ddSite is a column called Site.  That column is a Lookup column. The list for the Lookup is called Sites which itself contains the columns 'Site' and 'Site Description'.

- The underlying column for ddDepartment is a column called Department. That column is a Lookup column. The list for the Lookup is called Departments which itself contains the columns 'Department', 'Department Description' and 'Site'.

[mariominiaci]

I've managed to get some progress using the following, but it only lets me pick the Departments for the last Site in DataCardValue20, not for all of them. The Department saves successfully at least.

Filter(Choices([@'Requests'].Department),
    Value in Filter(
        Departments,
        Site in DataCardValue20.Selected.Value
    ).Department
)