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

# calculation from a different table

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

Tommaso

• ###### 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

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

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

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.