Dynamic filter based on value.
I would like to be able to click on a part number from my schedule and have it bring up all of the PO's that include that part number without having to create reports for every part number ahead of time.
Is there a way to have a report of all open orders, but when I click on a part number in the schedule, it applies a filter to the Open Order Report based on that part number?
Answers
-
Hi @Heald33
I would use Sheet Summary fields to show the filtered POs.
For the filter, I added the [filter] checkbox column.
The [filtered PN] filed gets the (first) checked [filter]'s part number with this formula;
=IFERROR(INDEX(COLLECT([part number]:[part number], filter:filter, @cell), 1), "")
Using the value, the [all orders]# field lists orders that include the part number with this formula;
=JOIN(COLLECT({order}, {part numbers}, HAS(@cell, [filtered PN]#)), CHAR(10))
For open orders, the formula is;
=JOIN(COLLECT({order}, {part numbers}, HAS(@cell, [filtered PN]#), {open}, @cell), CHAR(10))
In those formulas, the ranges, {order}, {part numbers}, and {open} correspond to the same name columns of the Purchase Order sheet below.
The sheets with the links below are published-editable, so you can check how the formula works by changing the filter checkbox, etc.
https://app.smartsheet.com/b/publish?EQBCT=cd04062d317145419b78e3c65035b282
https://app.smartsheet.com/b/publish?EQBCT=12c717be774b4659a57a537bc700ac64
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!