4 Replies Latest reply on Sep 3, 2014 6:18 AM by RyanBuckley

    Tally and list unique values from related field

    RyanBuckley

      Title

      Tally and list unique values from related field

      Post

           I have a table "Images", which has a child table "Prints" containing all information on printed versions of each particular image.

           Each print has a Size field.

           From the Images table, I want to be able to show a list of all sizes of any given print and the quantities of each for that image.

           Currently i have a calculation field in "Images" with List ( Prints::Print Size ), which gives me all the information I want, but not how I would like to see it. For example, this method lists the images like so:

           8 x 10

           11 x 14

           11 x 14

           11 x 14

           16 x 20
           16 x 20
           20 x 24

           But what I'm trying to achieve is:
           8 x 10 (1)

           11 x 14 (3)

           16 x 20 (2)
           20 x 24 (1)

           I've played around with the "sum the reciprocal" technique, but haven't been able to find a way to make it work.

           Any suggestions would be greatly appreciated!
            

        • 1. Re: Tally and list unique values from related field
          philmodjunk

               Sum the reciprocal isn't the method to use for a related table.

               If you were willing to produce this list on a layout based on Prints, rather than images, there's a simple summary report format that can give you one row of data for each size with a subtotal counting the number of prints in that size. Performing a find could limit that report to those for a single image or you can pull up records for multiple or all images and group your sub totals under a sub heading for each image record.

               To display this data on an Images based layout would use one of two methods. Do you have FileMaker 12 or newer? If so, we can use ExecuteSQL to produce this list. If you do not, there's a way to use an intermediary table that has just one record for each print size and a global field that "reaches through" in order to match to only the print records for the current image and then also by size to produce subtotals for each print size, but only for the current image.

          • 2. Re: Tally and list unique values from related field
            RyanBuckley

                 Thank you PhilModJunk. We are using Filemaker 11, and Filemaker 10.

                 The intermediary table sounds like a good solution, but the problem is that Print Size can be almost anything. Some prints are odd sizes, some are fractions of an inch off from the standard sizes...etc.

                 Having the list appear on the Images layout is pretty key, as we would like to see at a glance what we have, as most of the other information is also presented on that page.

            • 3. Re: Tally and list unique values from related field
              philmodjunk

                   The intermediary table works like this:

                   Images------<Sizes-----<Prints

                   Images::__pkImageID = Sizes::_fkImageID
                   Sizes::Size = Prints::Size AND
                   Sizes::_fkImageID = Prints::_fkImageID

                   Then a count calculation defined in Sizes can count all related records in Prints. A portal to Sizes can then list the sizes and number of prints in that size for the given image record.

                   The trick is to use the specified size in Prints and the _fkImageID field in Prints to create a new record in Sizes if one for that combination of size and image ID does not already exist. If you enabled "allow creation of records via this relationship" for Sizes in the Sizes to Images relationship, this script step would create a new Sizes record, but only if that related record did not already exist:

                   Set Field [Sizes::Size ; Prints::Size ]

              • 4. Re: Tally and list unique values from related field
                RyanBuckley

                Clever workaround, for sure, but I was hoping it would be simpler.