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

    calculation from a different table

    tutorl

      Title

      calculation from a different table

      Post

      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".

       

      HOW TO CALCULATE SUCH FIELD?

      Many thanks

      Tommaso (Italy) 

       

        • 1. Re: calculation from a different table
          mrvodka
             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
            tutorl
              

            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

             

            Tommaso 

              

            • 3. Re: calculation from a different table
              mrvodka
                

              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
                tutorl
                  

                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

                 

                bye

                Tommaso 

                • 5. Re: calculation from a different table
                  mrvodka
                    

                  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::ID_FELLOW = FELLOWS_vs_ITEMS::ID_FELLOW

                  AND

                  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
                    etripoli
                      

                    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.