7 Replies Latest reply on Jan 19, 2012 4:02 PM by philmodjunk

    Get Max count only of related records

    PeteTheFreeze

      Title

      Get Max count only of related records

      Post

       Dear all,

      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:

      Max(rel_Parts_Tools::AmountOfTools)

      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.

      Regards

      relationships.jpg

        • 1. Re: Get Max count only of related records
          philmodjunk

          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".

          • 2. Re: Get Max count only of related records
            PeteTheFreeze

             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.

            Greetz

            • 3. Re: Get Max count only of related records
              philmodjunk

              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. 

              If feels like there should be a filtered portal that will work for this but can't quite dot every i and cross every t to make it happen at the moment...Frown

              • 4. Re: Get Max count only of related records
                PeteTheFreeze

                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...

                Thanks!

                • 5. Re: Get Max count only of related records
                  philmodjunk

                  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...

                  • 6. Re: Get Max count only of related records
                    PeteTheFreeze

                     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?

                    • 7. Re: Get Max count only of related records
                      philmodjunk

                      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.