Your analysis of why this doesn't work for you is spot on. You need to calculate your total from the other side so that only "amountOfTools" values for the specifed programs are included in the "Max" evaluation.
How are you listing the tools required for a given program? In a portal or in a list view layout based on Rel_Parts_Tools? (both can be made to work for that purpose).
The basic method is to define a "Maximum of" summary field in Rel_Parts_Tools. In a list view report based on Rel_Parts_Tools, you can sort your records by ID_Parts and put this summary field inside a sub summary layout part set up to be "when sorted by ID_Parts".
Thanks for your prompt reply. I indeed knew the mistake, I didn't know the solution. I still don't however, because I want the Max info to be in a portal on the layout based on the Event table.You suggest it can be made to work. I would very much like to know how you;'d go about doing that.
A portal will require quite a bit more set up. That's why I suggested the summary report, it doesn't require as much to get the resulting list.
Frankly, I'm still mulling this one over a bit. I thought it would be possible but now am not so sure. I can get to the point where a portal to rel_parts_tools would seem to list the needed parts and that same summary field (I think) would then work, but you'll get duplicate entries every time 2 or more parts call for the same tool.
Listing the tools needed was as far as I got too. Showing it in a portal is indeed the problem. It seems to work now however thanks to your idea of using the summary field. I defined a portal based on the tools table to show a summary "maximum of" field in the rel_parts_tools table.
I will have to test it some more though...
I thought of that and discarded it, thinking that if you have Rel_parts_tools records for the same part but from other programs, they would be part of the summary. That might not be evident at first as you are likely to get the same value in many cases. (Only when it's greater than the max will you be able to spot a discrepancy...)
Keep in mind that with one button click, you can pull up a summary report of your tools list for a given event that will not have any duplicate entries and will count your max tools correctly...
Hmm, Yes, I did some testing and you're rigth in that the summary isn't correct. To cling on to the idea of a portal, might there be a way make a script do some checking in the rel_parts_tools-table, and when the ID_Parts is included in a program added to the current event fill a boolean with 1. Then, get the max for those Tools.
I don't think there is a script step that "knows" about relationships and can act on that, except for Go to Related Record which gets you to a related record. Do you know whether that exists?
In FileMaker scripts, "table context" is established by Go to Layout. That puts your script on the current record in that layout's found set. From there access to other records in other tables is via the layout's table occurrence. That's what you select in "show records from". It's the name of one of the boxes in Manage | Database | relationships and the relationships found there determine what record or records can be accessed via your script steps.
In theory, you could set up an events tools table and use a script to create those records and a number field could be used to record the max tool count for each tool. The script wouldn't be simple to set up, however.