Boxes scenario number one single select option data source SharePoint user can pick a category the relevant subcategories will load based on the subcategory selection.
The products will load if I change the subcategory the products will be reset and if I change the category it will reset the subcategory and.
Product let's understand the schema of the data source first I have a SharePoint list called category list here I have a choice column called.
Category which has the choices defined for my categories the title column is where I am defining what the subcategory is so mobile phones is the subcategory.
Subcategories I have a separate list called the products list the title column is where I'm storing the product information the subcategory column is a column of.
Type lookup that is looking up to the category list column title this is where my subcategories are defined in the category list so iPhone 13 product connected with.
Subcategory mobile phones the product MacBook Air is linked to the subcategory laptops let's add a new product that's the name of my product.
Subcategory is mobile phones save So based on this setup in my SharePoint list let's try and create single select cascading combo.
Boxes in the data source in my power app I have already connected both my lists the category list and the products list I am leveraging modern controls and for that under updates I.
Posts Related:
- Excel Today - Setting Up Teams
- Excel Today - Educon 2022
- How to save Microsoft Forms Responses & Attachments to SharePoint Lists or Libraries & Send Email
Have modern controls and themes turned
On the same technique will also work with classic controls I'll create my category combo box I'll insert the modern combo box.Control item property for this combo box control I need to list the categories the categories are listed in column of type choice to get those.
Values I'll use the function choices of my data source which is category list dot the name of my column which is called category this will list all my.
Categories I'll rename this combo box next I need to add my subcategory combo box so I'll insert another combo box.
Control the items property for this combo box is where I need to list out all the subcategories based upon the category value that is selected in the category.
Combo box and for that I will use the formula filter filter my list of categories.
Where category which is my choice column so category. value is equal to my combo box control that's listing out the categories do selected dot value so if I pick a category let's say.
Electronics this will list out the relevant subcategories note in the modern combo box if you head over to fields and edit you can decide which field from that.
Connected data source that I've linked to the items property I would like to show product I've inserted the combo box the products list I need to show the title values of the products depending.
Upon the subcategory that the user
Selects in that combo box subcategory is a lookup column items will be filter my products list where subcategory which is a lookup.Column do do value is equal to my subcategory combo box do selected dot title so you can see how based on the.
Subcategory selection the products are loading scenario number two my category list for the category I have a text column the remaining setup is the same I.
Have my products list now for this scenario let's build the category so I'll add a combo box items property with the choice column it was.
Easy I could use the choices function but how do I get the unique category values from this text column called category text and for that I would have to use.
The formula distinct distinct of my data source which is category list and the column is category text so now you can see how it's listing.
Up out the distinct categories next step I need the subcategory the items formula for this will be filter my category list.
Where the category text column is equal to the combo box do selected dot value so you can see based upon the category.
Selection the subcategories are loading and then for product the formula will be exactly the same as before filter my products list on the lookup column based.
Upon the value that's selected in the subcategory combo box so I was able to build cascading combo boxes even in this scenario however there is a.
Catch this formula that I have added here called distinct actually results in a delegation warning meaning if my category list had a large.
Number of items then this distinct function will not return accurate results that limit of how many items can be in this category.
List is the delegation limit and that limit right now is set to 500 for my power app you can increase this to a Max of 2,000 so this technique of using.
Distinct will work as long as the number of list items in my category list is 500 and let me prove that there is a delegation warning here I'll change the dat a row limit to.
One now if I look at my category combo box it only lists one item whereas if you look at the other pattern that I used which was use the.
Choices function that will load all the choices so it's important to be aware of the delegation limit when working with the distinct function another aspect is category set to blank.
But subcategory and product are still loaded so what we need to do is whenever category changes I want to reset subcategory so category combo box on change I will.
Reset subcategory and I would also want to reset product so you will notice now I changed it reset.
Subcategory now let's say I make the Mt both subcategory and product reset the next scenario is to create multi select combo boxes with SharePoint as a data.
Source my list setup is same as scenario number one category is a choice column title is defining the subcategories and I have my products list where I have done the mapping with the subcategory.
Via lookup column let's first create the categories combo box this will be multi select allow multiple selection.
On items property choices of my category list the name of my choice column C I have the option to multi select.
Subcategories once again a combo box multi select items filter my category list.
Condition category which is a choice column category do value is in my categories combo box which is multi.
Select dot selected items dot value.
Notice it's not loading the data it does load one option well that's because in is not a delegable query with SharePoint and my delegation limit was set to.
One I'll change this back to 500 this time if you notice it loads all the subcategory values based upon the category options selected in the first combo.
Box so once again there is a delegation warning meaning I have to respect the fact that which SharePoint as a data source in is not a delegable query so I am limited to the data row limit which.
Can be a maximum of 2,000 meaning I am good as long as my category list is around 2,000 rows products combo box.
