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.
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,,,
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:
- 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 ) )
- Then define summary fields in Colonies that return the Max value of each of these calculation fields.
- 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.
- 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.
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?
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
Ok but I can't see where to do this....
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.
I've been looking there and sill can't see it. Is this something available only on the Advanced version?
I'm using Pro 12
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....
I didn't think it would be this difficult!
And what is difficult?
1 of 1 people found this helpful
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:
Field 2 = c_Max
Calculation with number result with this formula:
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!