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