cascading combo box with 'allow multiple values'

Like (0) Share

Report

Posted on 2 May 2023 01:03:14 by rutica

Hi all

Please help! I have 3 lists:

System list:

SystemID (number)

SystemName (single line of text)

Project list:

ProjectID (number)

ProjectName (single line of text)

EPPSystem (lookup that allows only one value. Gets info from the SystemName field in the System list)

State (choice drop down. Only one selection allowed: In Progress; On Hold, Closed; Withdrawn)

Risk list:

RiskID (number)

RiskName (single line of text)

ImpactedSystem (lookup that allows multiple values. Gets info from the SystemName field in the System list)

ImpactedProject (lookup that allows multiple values. Gets info from the ProjectName field in the Project list))

The user will access the Power Apps form to enter a new risk or edit an existing risk.

I would like the user to be able to choose multiple impacted Systems. Below that I would like a cascading control where the user can select multiple impacted Projects. The impacted Projects should only contain records where the EPPSystem from the Project list equals the impacted System combo box control. And I only need to see Projects that are State = In Progress or On Hold.

I’m not a fan of the multi select combo box, so if list boxes could be used instead, that would be ideal!

I have struggled with this for a long time. Can anyone help?

Thanks so much!

Categories:

General Questions

All responses (8)

Answers (0)

WarrenBelz 146,743 Most Valuable Professional on 09 May 2023 at 03:52:49

Like (0)

Report

Re: cascading combo box with 'allow multiple values'

Hi @rutica ,

DefaultSelectedItems are the same, but you do not need the Id section in the Update.
rutica 71 on 09 May 2023 at 03:24:34

Like (0)

Report

Re: cascading combo box with 'allow multiple values'

Thanks to @WarrenBelz for getting me almost all the way there! His guidance and knowledge is beyond valuable. But I changed my fields from a lookup to a text box, so some of Warren's code had to be tweaked. See below.

I have 3 lists: System, Project, Risk

Instead of using lookup fields in my Risk SharePoint list, I created two new multi-line text boxes called ImpactedProjects and ImpactedSystems.

In my Power App form, I have 2 combo boxes:
cbSystem (combo box)
Items: Sort(System.SystemName,SystemName,SortOrder.Ascending)
Select Multiple: true
DefaultSelectedItems: ForAll(Split(Parent.Default,"; "),{Title:ThisRecord.Value})
In the Property tab of the combo box, click Edit near the Fields row. My selection for Primary Text and SearchField are both: Title

cbSystem (DataCard)
DisplayName: DataSourceInfo([@Risk],DataSourceInfo.DisplayName,"ImpactedSystems")
Default: ThisItem.ImpactedSystems
Update: Concat(cbSystem.SelectedItems,SystemName,"; ")

cbProject (combo box)
Items: Distinct(Sort(Ungroup(ForAll(cbSystem.SelectedItems As aSel, Filter(Project, 'EPPSystem'.Value = aSel.SystemName && (State.Value = "In Progress" || State.Value = "On Hold"))),"Value").Title,Title),Title)
Select Multiple: true
DefaultSelectedItems: ForAll(Split(Parent.Default,"; "),{Value:ThisRecord.Value})
In the Property tab of the combo box, click Edit near the Fields row. My selection for Primary Text and SearchField are both: Value

cbProject (DataCard)
DisplayName: DataSourceInfo([@Risk],DataSourceInfo.DisplayName,"ImpactedProjects")
Default: ThisItem.ImpactedProjects
Update: Concat(cbProject.SelectedItems,Value,"; ")

Thanks
rutica 71 on 03 May 2023 at 22:36:41

Like (0)

Report

Re: cascading combo box with 'allow multiple values'

Hi Warren, Thanks again for writing. I took your advice and instead of using the 2 lookup fields, I created two new multi-line text boxes called ImpactedProjects and ImpactedSystems in my Risk list.

In those 2 new cards, I have the cbSystem and cbProject combo boxes with the Items properties discussed above.

What should the Update value of those 2 cards be? What should the DefaultSelectedItems of the 2 combo boxes be?

And when should I use the visual name showing on the list versus the DataField name? For example, in my System list, the name showing in the list is: SystemName; but the DataField name is: Title.

Also, do I need to enter code for the OnChange and Reset for the 2 combo boxes?

Thanks for sticking with me!
WarrenBelz 146,743 Most Valuable Professional on 03 May 2023 at 01:53:30

Like (0)

Report
Re: cascading combo box with 'allow multiple values'

@rutica ,

Just one small example of my comment on Lookup fields - you need to "get back" the Id of the item being looked up (you do not need this for a multi-choice field) - Update of Data Card

ForAll( cbSystem.SelectedItems As aSel, { Value: aSel.SystemName, Id: Lookup( 'Impacted Project(s)', Impacted_x0020_Project_x0028_s_x = aSel.SystemName ).ID } )

DefaultSelectedItems

ForAll( ThisItem.test, {SystemName: Value} )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps
rutica 71 on 03 May 2023 at 00:38:09

Like (0)

Report

Re: cascading combo box with 'allow multiple values'

Hi Warren

Thanks again for writing!

You’re right, I’m so sorry about that. I had an error in my cbSystem combo box. I fixed it now.

The Items property of my cbSystem is now: System.SystemName.
The Items property of my cbProject is what you have above.

The System combo box is called cbSystem.
It’s connected to my Risk list to a Lookup field that allows multiple values, with the unfortunate DataField name of: test. However, the name showing on the list is: Impacted System(s).
That Lookup gets its info from the SystemName field in the System list.

The Project combo box is called cbProject.
It’s connected to my Risk list to a Lookup field that allows multiple values, with the also unfortunate DataField name of: Impacted_x0020_Project_x0028_s_x. However, the name showing on the list is: Impacted Project(s).
That Lookup gets its info from the ProjectName field in the Project list.

(I always get confused which name to use-the visual name showing on the list, or the actual name of the field shown at the bottom of the screen when hovering my mouse over the field in the list settings).

So now I see values in the cbSystem and the proper values cascades to cbProject. I can select multiple items but they both don’t save back to the list.

Thanks again and sorry for my initial confusion with the cbSystem combo box.
WarrenBelz 146,743 Most Valuable Professional on 02 May 2023 at 21:15:02

Like (0)

Report

Re: cascading combo box with 'allow multiple values'

Hi @rutica ,

You might note my assumption at the start of the post about SystemName (you said it was a Single Line of Text) - but you are obviously using a Lookup (which you honestly do not need and only causes unwanted restrictions - but that is another discussion). What is the name and field type you are writing back to and the name of the combo box ?
rutica 71 on 02 May 2023 at 16:38:05

Like (0)

Report
Re: cascading combo box with 'allow multiple values'

Wow Warren! Thanks. I'm almost there...
I had to change your line of code above from:
EPPSystem.Value = aSel.SystemName
to:
EPPSystem.Value = aSel.Value
It only gave me the choices of Value or ID.

It's not saving the Impacted Projects. For the Update property of that data card, I have: cbProject.SelectedItems. But it's not saving the selections back to the list.

Also, do I need to enter code for the DefaultSelectedItems, OnChange, and Reset for the 2 combo boxes?

Thanks!
WarrenBelz 146,743 Most Valuable Professional on 02 May 2023 at 02:45:32

Like (0)

Report
Re: cascading combo box with 'allow multiple values'

Hi @rutica ,

Assuming your first Combo Box (I will call it cbSystem below)has the Items (or a sorted version of this)

System.SystemName

then your Project Combo Box would be something like this

Sort( Ungroup( ForAll( cbSystem.SelectedItems As aSel, Filter( Project, EPPSystem.Value = aSel.SystemName && ( State.Value = "In Progress" || State.Value = "On Hold" ) ) ), "Value" ).ProjectName, ProjectName )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps