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.
Overview:
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]"
Details:
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
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.
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 @Mattimus You're welcome, I'm glad tweaking the syntax helped.
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])
Thanks for sharing your screenshots, I'm happy to help
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:
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.
Alyssa.
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!
