calculation from a different table
I use filemaker pro 9.x, and in my database I have 3 tables:
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?