Dynamically update field counts in one table based on filters in another table

[DonC]

I am VERY brand new to Knack. I have been looking through the community topics and answers and cant seem to find exactly what I am looking for. if this has been dealt with already please direct me to proper location.

I have one table that lists personnel, what musical ensemble they are in, and the particular musical instrument they play. In another table, I have a list of the instrument choices that is also connected to the personnel table. Additionally, in the instrument table, I have a count field that shows the total count of each instrument overall personnel. (ie. Flutes = 20, trumpets=45, etc)

However, how do I get it to dynamically update based on the filter parameters entered in the personnel table. When I filter the personnel table to show me personnel in “Ensemble 1” it should show me a flute count = 20 but when I change the filter to show me personnel in ensemble 2, it should update to flute count =5, etc. for all instruments regardless of filtered personnel table.

I hope this make sense. If someone can direct me where I can find the answer if this issue has already been dealt with or please advise a possible solution.

Thanks so much - “Knack Newbie”

[StephenChapman]

Hi @DonC, welcome to Knack and the forum!

Unfortunately count fields are static in that they don’t update with filters.
I suggest instead to use a pivot table view for your Personnel table with the ability to filter by ensemble, and use a count of each personnel record as the calculation.

You could even group it by ensemble, and then by instrument like so:

I hope that helps!