1 2 Previous Next 20 Replies Latest reply on Mar 2, 2017 1:26 PM by philmodjunk

    max value from summaries for given field

    philkhor

      I have two related tables, honeybee colonies and colony inspections. I have (with much help from philmodjunk) managed to display previous records when I'm entering new data, and I have created summary fields so that I can display each colony with the average/total or whatever for given fields. What I now want to do is show at the bottom of the page the colony with the best score for a given field, e.g.:

      colonyave temperamenttotal honey yield
      Ave sizetotal amount fed
      C15342
      C241037
      C32254
      C43257
      Overall summary
      best temperament (max)highest yield (max)best size (max)most thrifty (min)
      C15
      C210
      C35
      C45
      C27
      C47

       

      Any chance?

        • 1. Re: max value from summaries for given field
          philmodjunk

          Are you able to display the first part of this report where you have one row for each colony.

           

          Have you ever used SQL to query data in a data base? We don't have to use SQL here, but it will simplify a lot of the work needed to produce the "summary recap" at the bottom.

          • 2. Re: max value from summaries for given field
            philkhor

            Yes I have the top part as shown in the table above. It's the overall summary I'm after. I can of course just browse up & down the table looking for the smallest or biggest values but in reality I have more than 40 colonies and more than 10 attributes and I'd like to make this possible for 150 colonies or more....

             

            SQL is not within my skill set! I was rather hoping that I could achieve what I want without getting into scripts but hey, I'm game to try anything,,,

            • 3. Re: max value from summaries for given field
              philmodjunk

              SQL isn't quite a script, it's used in a calculation in FileMaker. It's got a rather steep learning curve if you haven't worked with SQL before.

               

              Is this layout a table or list view based on the Colonies table? (there are ways to do this from the history table also.)

               

              Assuming that it is, we can use List View, not table view and add a trailing grand summary for the bottom section. But we have to do a bit of setup here first under the hood:

               

              1. In place of each of these summary fields from the history table, define a calculation field in colonies that uses the Sum() or Average () functions to compute the same value. (Sum ( RelatedTable::Field ) )
              2. Then define summary fields in Colonies that return the Max value of each of these calculation fields.
              3. Go to Manage | Database | Relationships, select the Table Occurrence (box) for Colonies and click the duplicate button (two green plus signs). Drag from any field other than a summary field in the first occurrence to any such field in the second occurrence to link them. Doubleclick this relationship line to open a dialog where you then change the operator from = to X to make it a Cartesian Relationship.
              4. Create your List View layout based on Colonies, give it the trailing grand summary layout part (see Part setup...) and put a portal to this new occurrence of Colonies on the grand summary part. Put the fields for colony, and your calculation fields into the portal row.

               

              Let me know when you have this done and we can look at how to make the values disappear if they don't match the max values from your summary fields, unless you can figure that part out for yourself.

              • 4. Re: max value from summaries for given field
                philkhor

                so, I'm showing colony ID and calculated field in main body and Colony ID, calculated field and summary max go calculated field. I get all colonies showing in the trailing grand summary, each with their own calculated field and the maximum against all colonies. How do I hide the rows where the calculated field does not match the maximum?

                • 5. Re: max value from summaries for given field
                  philmodjunk

                  From your example, you don't want to hide the rows, just the fields that don't show the maxium for that category. You also don't need the maximum against all colonies in the trailing grand summary though it might help to confirm a correct set up.

                   

                  You can use Hide Object When expressions to make the fields with data less than the max for that category invisible and then you should get the results shown in your original post.

                  Colonies 2::CalculatedColonyMax < Colonies::SummaryFieldMax

                  • 6. Re: max value from summaries for given field
                    philkhor

                    Ok but I can't see where to do this....

                    • 7. Re: max value from summaries for given field
                      philmodjunk

                      In layout mode, select a field. Then go to the Inspector's data tab and set up the Hide Object When expression in the Hide Object When box.

                      • 8. Re: max value from summaries for given field
                        philkhor

                        I've been looking there and sill can't see it. Is this something available only on the Advanced version?

                        • 9. Re: max value from summaries for given field
                          philkhor

                          I'm using Pro 12

                          • 10. Re: max value from summaries for given field
                            philkhor

                            yep, just looked. Object visibility came in with Pro 13. It looks like I have to fork out for an upgrade or be satisfied with what I have....

                            • 11. Re: max value from summaries for given field
                              philmodjunk

                              See in the red circle. It's not only found in FileMaker Advanced, but it does not exist in older versions of FileMaker.

                               

                              HideObjectWHen.png

                              • 12. Re: max value from summaries for given field
                                philkhor

                                I didn't think it would be this difficult!

                                • 13. Re: max value from summaries for given field
                                  philmodjunk

                                  And what is difficult?

                                  • 14. Re: max value from summaries for given field
                                    Licorice

                                    Hi Phil,

                                     

                                    I am not sure if I understand this correctly, but...

                                    If all you need is the maximum values of a certain field, there is a really quick way to do this. It requires 2 fields.

                                     

                                    Field 1 = c_Count

                                    Calculation with number result with this formula:

                                    Get( RecordNumber)

                                     

                                    Field 2 = c_Max

                                    Calculation with number result with this formula:

                                    Last( YourTOName::c_Count)

                                     

                                    Your FileMaker version does not have the capability to hide field by calculated conditions, as stated in earlier replies. But there are ways around it that might meet your need through Conditional Formatting as a for instance.

                                     

                                    If this is what you need, here is a practical example:

                                     

                                    Table 1 contains Projects.

                                    Table 2 contains Project Tasks.

                                    There is a TOgroup with these two tables by Project ID.

                                     

                                    Field 1 has been created in Table 2.

                                    Field 2 has been created in Table 1 and the result shows how many tasks are connected to the project.

                                    This is not the same as the Count function in FileMaker, which is pretty expensive in terms of time needed for calculation when the field is on a layout. The result of this approach is calculated instantaneously, without delay.

                                    If you have multiple fields you need to track this for, you need create additional fields and that may not be what you want - although the speed with which the result is displayed by FileMaker might be worth it.

                                     

                                    Using Conditional Formatting you could set certain parameters that make the background of the field go red to alert you to certain values, e.g. Self > 5 or whatever your benchmark is.

                                     

                                    I am using these calcs a lot in client solutions where a lot of max calculations are requested for a total set of related records. Of course it reacts differently than summary fields but each has their own place in a solution. But it might be what you need.

                                     

                                    Let me know if you need more details and I hope this helps!

                                    1 of 1 people found this helpful
                                    1 2 Previous Next