cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AcesOverPacific
Regular Visitor

Creating cascading Dropdowns using a static table and NOT a sharepoint list?

Hello All,

 

I have a situation I can't seem to get working right. I have an edit form that is pulling from a static excel table (table1) that I imported. I used this important rather than a sharepoint list because it contains more than 2,000 records and encountered issues with delegation when pulling options for the combobox dropdown. So now that I have a static list, delegation issues have been resovled, however, now I cannot figure out how to get a second combobox dependent on values in the first.

 

So in combobox1 I have the following formula

 

Items = Distinct(SortByColumns(Filter([@Table1], StartsWith(Part_Num, TextSearchBox1.Text)), "Part_Num"), Part_Num)

 

This give users a choice of part numbers to select.

 

Combobox2 allows users to choose a mold number. However, I only want molds listed that actually create the parts from Combobox1. So for example if a user selects Part Number 100000, and only 2/1200 molds run part number 100000 only those two mold number should appear as choices.

 

Currently my formula for combobox2 is as follows:

 

Items = Distinct(SortByColumns(Filter([@Table1], StartsWith(Mold_Num, TextSearchBox1.Text)), "Mold_Num"), Mold_Num)

 

For a sharepoint list I could simply append , Result = ComboBox1.Selected.Result) to this formula to get the correct filter. However, this does not seem to work on static lists. Is there a way I can modify the formula to still get my results filtered correctly?

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @AcesOverPacific ,

 If ComboBox1 Items is this

Distinct(
   SortByColumns(
      Filter(
         [@Table1], 
         StartsWith(
            Part_Num, 
            TextSearchBox1.Text
         )
      ),
      "Part_Num"
   ), 
   Part_Num
)

Try this on the second one Items - you also need to specify the column in the table you are filtering.

Distinct(
   SortByColumns(
      Filter(
         [@Table1], 
         StartsWith(
            Mold_Num, 
            TextSearchBox1.Text
         ) &&
         Part_Num=ComboBox1.Selected.Result
      ), 
     "Mold_Num"
   ), 
   Mold_Num
)

 

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.

 

View solution in original post

3 REPLIES 3
WarrenBelz
Super User
Super User

Hi @AcesOverPacific ,

 If ComboBox1 Items is this

Distinct(
   SortByColumns(
      Filter(
         [@Table1], 
         StartsWith(
            Part_Num, 
            TextSearchBox1.Text
         )
      ),
      "Part_Num"
   ), 
   Part_Num
)

Try this on the second one Items - you also need to specify the column in the table you are filtering.

Distinct(
   SortByColumns(
      Filter(
         [@Table1], 
         StartsWith(
            Mold_Num, 
            TextSearchBox1.Text
         ) &&
         Part_Num=ComboBox1.Selected.Result
      ), 
     "Mold_Num"
   ), 
   Mold_Num
)

 

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.

 

While the formula you suggest is accepted by program, it still does not filter results, instead it still lists all results without any filtering based on the first combobox result.

Okay, nevermind after playing with the formula I did get it to work by pointing to the correct column. Thank you!

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (3,064)