Dynamic filter based on value.

Heald33

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?

jmyzk_cloudsmart_jp

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