Form filter dropdown depending on previous dropdown

I've taken the PO requests template and expanded it to handle budgets so that the PO requests automatically deduct from the budgeted line item.

In a form I'm trying to get a picklist called "Budgeted Item List" that pulls from a table called "B_Budget" to filter its contents based on a previous selection "$[Item Type Picklist]"

B_Budget table has a column named "Item Type" that has three options: Hardware, Software, Other.
In the form when I select "Hardware" from "$[Item Type Picklist]" I want another field "Budgeted Item List" to show only hardware that is on the list. And if I select Software I want that field to only show the software on the table.
Does that make sense?
My best attempt was this...
=FILTER(B_Budget,"B_Budget[Item Type]", $[Item Type Picklist]=B_Budget[Item Type]) but it only evaluates to 0

Thanks in advance for your help!

Hi @Mattimus, nice to see you again! :honeybee:

From what you've described, it sounds like you have your tables and screen set up correctly, so you may just need to tweak your formula. Give this formula a try (I just adjusted the syntax, and it may result in what you need):

=FILTER(B_Budget,"B_Budget[Item Type]=%", $[Item Type Picklist])

Hope that helps, and let us know if you have any other questions. :slight_smile: :honey_pot:


Thank you for your quick response.

When I use the code you gave me it returns a value of 34 (which is how many items in the list that have the Item Type set as Hardware.) I'm looking to simply reduce the list down to just hardware items and exclude software and other.

In addition I added another table called "M_Budget_Year" and a field in the form called "Budget year" which outputs the options 2021, 2022, and 2023 in the dropdown. I would like the "Budgeted Items List" reduce based on budget year as well as Item Type.
Is that possible?

Hi ,

I've got the similar situation, but it doesn't work. Do I miss any step ?

Here is my data.
2 tables as the blows.

In the form , set filter source "=FILTER(Table2,"Table2[Type1]=%",$[PL_Type1])" for the picklist.

But the result likes this. It should be 2 values in the picklist.

Thanks in advance for the help.

Hi @Mattimus :slight_smile: You're welcome, I'm glad tweaking the syntax helped. :honeybee:

For your second ask, yes, you can achieve this by adding another field that allows the user to select the Budget year as well. (Have two fields to select an item: $[Item Type Picklist] and $[Budget Year Picklist])

Then in your filtered list, the FILTER formula would include AND, and looking something like this:
=FILTER(B_Budget,"B_Budget[Item Type]=% AND B_Budget[Budget Year]=%", $[Item Type Picklist], $[Budget Year Picklist])

Hope that helps! :honey_pot:

Hi @Kath-6ba0, welcome to the community! :smiley: :honeybee:

Thanks for sharing your screenshots, I'm happy to help :slight_smile:

The reason why it is not returning results is because the select field is a rowlink, and the filter is looking to match that selected row (rather than only text).

This means that in Table2, you'll want to format your Type1 column to a rowlink to Table1:

This set up will allow the filtered field in your screen to return the results you're looking for. :slight_smile: :honey_pot:

How do I get it to show a list of Hardware/Software/Other items instead of just a number?
I need a selectable dropdown list for the form. A number doesn't do me any good.

Thank you very much. After your reply with @Kath-6ba0 I figured out that I needed to change my B_Budget[Budget Year] to a picklist and tie it in with my other table. And it worked!
Thanks so much for your help!

1 Like

@Mattimus Great! :star2:

Glad you got it figured out :slight_smile: You're welcome, please feel free to reach out again if any other questions come up.

Hi @Alyssa ,

Great! :smiley:
It works. Thanks for the help.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.