cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smileamile
Helper II
Helper II

Cascading filter in form between 2 single select combo boxes

I have a (single select) combo box that is pulling fields from a list and concatenating them. "Result" is the Site ID, and the displayed value that writes to the SP list on form submission is "ConcatenatedColumn".

Site/ComboBox2:

Items = Sort(AddColumns(Distinct('CompanySiteInfo','Site ID'),"ConcatenatedColumn",Concatenate(Result," - ",LookUp('CompanySiteInfo','Site ID'=Result).'Site Name')),Result,Ascending)

 

This works great. Now I have a second (single select) combo box in the form that pulls from another list "SiteDeptInfo". I need the options in this box to be limited based on the Site ID. Is there a problem because these are two separate lists? Any Site ID that can be selected from the Site combo box will be in this list. 

Department/ComboBox3:

Sort(AddColumns(Filter('SiteDeptInfo', 'Site ID' = ComboBox2.Selected.Result),"ConcatenatedColumn",Concatenate('Department ID', " - ",'Department Name')),"DepartmentID", Ascending)

 

This keeps giving me "invalid argument type" because it doesn't like the equals sign. What am I missing?

6 REPLIES 6
WarrenBelz
Super User
Super User

Hi @smileamile ,

In the first one (I have simplified the Concatenate for ease of reference), you needed a Lookup to get the values for each record for the new column

Sort(
   AddColumns(
      Distinct(
        'CompanySiteInfo',
        'Site ID'
      ),
     "ConcatenatedColumn",
      Result & " - " & 
      LookUp(
         'CompanySiteInfo',
         'Site ID'=Result
      ).'Site Name'
   ),
   Result,
   Ascending
)

and this works as expected. You need to do the same for the second one except you have two references to look up. I have used a With() statement only to avoid repeating code - you can do two LookUps if you want to

Sort(
   AddColumns(
      Filter(
         'SiteDeptInfo', 
         'Site ID' = ComboBox2.Selected.Result
      ),
      "ConcatenatedColumn",
      With(
         {
            wDept:
            Lookup(
               'SiteDeptInfo',
               'Site ID' = ComboBox2.Selected.Result
            )
         },
         wDept.'Department ID' & " - " & wDept.'Department Name'
      )
   ),
   "DepartmentID", 
   Ascending
)

 

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.

@WarrenBelz  Hmm. I need to do a lot of studying on this syntax. The logic makes sense, but it's all lit up in red. Says invalid number of arguments (received 2, expected 3 or more) but I have no idea where it's missing them.

@smileamile ,

Free-typing gremlins again - moved something and forgot to put it back - try it now as amended.

@WarrenBelz  I tried a few things, but still doesn't like the Site ID = ComboBox2.Selected.Result. WHat would the syntax be with two lookups instead? I noticed that this formula also took away the "search" option on the combobox, which I need.

 

It is a 1:Many relationship. Site is unique on the CompanySiteInfo list, but can show up multiple times on the SiteDeptInfo list, because there are multiple departments for each site.

 

This syntax is hard - I can do anything in SQL, but haven't figured out the key to the PowerApps formulas.

Thanks @smileamile ,

I cannot see your data structure, but the output of the piece of code should be from your list CompanySiteInfo with two columns Result (which is from SiteID) and ConcatentatedColumn (which is the CompanyInfo). So the output of ComboBox2 is ComboBox2.Selected.Result, which will be the SiteID.

So moving on to your second list SiteDeptInfo. You are trying to match a column in this list (again) called SiteID with the SiteID from the drop-down. So the first things I will ask are

  • Is this the correct column name for this list?
  • If so what type of field it it?

One small other thing - you have enclosed a number of values that so not have spaces or special characters in single quotes - is there a reason for this?

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.

Hi @smileamile ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,869)