6 Replies Latest reply on Jul 31, 2009 9:21 AM by etripoli

    calculation from a different table



      calculation from a different table


      I use filemaker pro 9.x, and in my database I have 3 tables:


      table1: FELLOWS

      table2: FELLOWS_vs_ITEMS

      table3: ITEMS


      The fields in table1 (FELLOWS) are

      ID_FELLOW (number, unique, not empty, it is the table key) 

      NAME (text, name of the fellow)

      ISGOOD (calculation based on the records of TABLE2 and TABLE3, the result must be "Yes" or "No", see below for explanation)


      The table2 (FELLOWS_vs_ITEMS) is used to relate FELLOWS with ITEMS. One fellow can own different items. The fields in table2 (FELLOWS_vs_ITEMS) are:

      ID_FELLOW (number, identifies the fellow)

      ID_ITEM (number, identifies the item)

      PURCHASE_DATE (date,  date when the fellow purchased the item)


      The fields in table3 (ITEMS) are

      ID_ITEM (number, unique, not empty, it is the table key)

      DESCRIPTION (text, item description)

      BRAND (text, brand of the item) 



      Now, the problem is the following: for each record in table1 (FELLOWS), the calculation field ISGOOD must be "Yes" if the fellow purchased on 12/06/2008 an item by the brand XXX; otherwise the result must be "No".



      Many thanks

      Tommaso (Italy) 


        • 1. Re: calculation from a different table
             Shouldnt the IsGood calc be in the FELLOWS_vs_ITEMS table considering that it is testing a date on items? Or is it for a specific item? Basically, what is the purpose here.
          • 2. Re: calculation from a different table

            A FELLOW can own more than one ITEM and the ISGOOD calculation must refer to the FELLOW record: you can think to this field as a boolean FLAG field, which is "Yes" or "No" depending on what items the fellow has purchased.

            In other words, the ISGOOD calculation should perform like this:


            If the fellow has purchased at least one item from brand XXX on 12/6/2008 then the result is "Yes", otherwise the result is "No".


            Many thanks in advance for reading my post




            • 3. Re: calculation from a different table

              Yes but is brand XXX and the date field of 12/6/2008 chooseable? IOW, can they change or is this is one time thing. You still havent stated a purpose to why you are doing this... The reason we ask is because depending on what your purpose here is, the solution will vary between a quick fix verses a proper more complicated fix.

              • 4. Re: calculation from a different table

                Thank you for your kind reply.

                The brand XXX and the date 12/6/2008 are fixed in the calculation.

                The problem I have is more sophisticated, but I have proposed this equivalent database which summarize the point.

                I need to know if a fellow have purchased at least one item by brand XXX on 12/6/2008, and XXX and 12/6/2008 are a one-time thing (fixed).

                For this reason I want to use the field ISGOOD




                • 5. Re: calculation from a different table

                  Create 2 global fields. gDate, and gBrand.

                  gDate is a global date field, gBrand a global text field.


                  Create a new relationship keyed from this global text field to the Brand field in the ITEMS table.


                  Create a calc field cListBrandItems with formula: List ( ITEMS 2::ID_ITEM ).


                  Now create a new relationship to FELLOWS_vs_ITEMS keyed on:




                  FELLOWS::cListBrandItems = FELLOWS_vs_ITEMS::ID_ITEM



                  Now your IsGood calc will be: not IsEmpty ( FELLOWS_vs_ITEMS 2::ID_FELLOW ) 

                  Technically you dont really need an IsGood calc as you can put the related field on the layout and format as boolean.




                  I dont know what this calc is going to be used for, but if you are just trying to find out any fellow who has Brand XXX Items on a certain date, you can do this without all of this by using finds and or Go to Related Records.

                  • 6. Re: calculation from a different table

                    The simply solution could be:


                    isgood = [Case ( Items:: Brand = "XXX" and Fellows_vs_Items:: Purchase_Date = "12/6/2008"; "Yes", "No" )]


                    The complex solution would be to create two global fields in Fellows, one to contain the brand, one to contain the date you want to match, and substitute those in the calculation.