3 Replies Latest reply on Feb 14, 2011 7:36 AM by bagheraa

    Counting calculation field results



      Counting calculation field results



      I have a related table with a calculation field that can have one of four results: "to init", "ongoing", "approved" and "delivered", and I want to count the related reccords for each of this results.

      How can I make this?

        • 1. Re: Counting calculation field results

          Use that field in a relationship to the related table, and then use the Count function to count the related records' IDs.  If the record is an 'Approved' record, for example, it will show the count for all Approved related records.

          • 2. Re: Counting calculation field results

            Easiest way to count these is on a layout based on the related table instead of the parent table. Then you can define a "count of" summary field, sort your records by your calculation field and place this summary field in a sub summary part "when sorted by" your calculation field.

            Then you can see the count for each category by sorting your records by your calculation field. If you don't want to see the individual records in this report, remove the body layout part and just keep the sub summary part. (And you can use a script that uses either a Find or Go To Related Records to bring up just the records related to a given parent record.)

            If you want to see these counts from a layout based on a parent record, it takes a bit more work. If you have FileMaker 11, you can create 4 one line portals to your related table and define a separate filter expression for each that filters by your four categories. Put just the summary field I described above in this one row portal and you'll see your total for each category.

            If you don't have FileMaker 11, you would need 4 relationships, one for each of your categories.

            If your current relationship is:

            parent::PrimaryKey = Child::Foreignkey

            You'd create 4 new relationships like this:

            Parent::PrimaryKey = OngoingChild::ForeignKey AND
            Parent::cOngoingKey = OngoingChild::YourCalculationField

            cOngoingKey would be a calculation field that returns "Ongoing" and OngoingChild would be a new table occurrence of Child.
            For the other three categories, you'd need additional calculation fields like cOngoingKey and relationships to additional table occurrences lik OngoingChild.

            Then you can either create the same 4 one row portals I described, but each would refer to a different one of these new table occurrences, or you can define 4 calculation fields in Parent such as Count ( OngoingChild::ForeignKey ).

            • 3. Re: Counting calculation field results

              Thankyou very much, PhilModJunk.

              As I needed the report ordered by other field than the related calculation field "state", I have used the the one line portals trick in a subsumary, and it worked. This is simply great.

              Thanks again for your attention.