Get Max count only of related records
I ran into a problem I can't solve.
I have concatenated several tables (see image) so that for every event I create in the events table I can add one or more Programs. These programs can have one or more Parts. For every part different Tools can be used and these tools all have a certain amount of them needed for the part. So, if I create a part called "making sure all the screws are tight" I will need the tool Screwdriver and I only need one of them. For the part "screwing the sides together" I need one screwdriver and 2 levellers. If I have the part "screwing the roof on the four sides" I will need 2 screwdriver and 1 leveller.
If I put these three parts in one program and add this program to my Event, I would like to have the total of tools needed displayed in a portal. I defined a field in the Tools table "MaxAmountOfACertainTool" which calculates the total amount by checking the Max of tools needed, as follows:
This works fine if I have only the three parts mentioned. However, when I add a new part to the Part table and make sure that for this part three screwdrivers are needed, then every event that uses a program with a part which needs screwdrivers will show that 3 screwdrivers are needed, independent of the fact that the part is not included in the current program that was added to the event.
The problem is that the max is evaluated from the Tools table and the tools table doesn't "know" it should only calculate the Max of those parts that are included in the Program currently selected for the Event. So, the calculation shows the max tools needed for all of the parts and not the Max of tools needed for the currently active parts in the currently selected program.
I hope I have made myself clear. Help is certainly appreciated.