There are actually several methods you could use here. The list function could be used to produce a list of Method entries for the job comments records and FilterValues function can test to see if "We called customer" is present in that list, but you also need to limit this to a specific date range so it makes more since to set up a different relationship to the jobComments table that filters out all records except 'we called' entries that fall within a certain date range based on the current date.
In your contact table...
Define two unstored calculation fields:
Today : Get (CurrentDate )
TodayPlus3 : Get ( CurrentDate ) + 3
Define a calculation field to return the specified text for Method in all records:
constWeCalled : "We Called Customer"
Now go to Manage | Database | Relationships and use the duplicate button (two green plus signs) to make a new occurrence of your JobComments table. Double click it so you can change its name to something more descriptive than JobComments 2.
Set up this relationship:
Contacts::ContactID = WeCalled72::ContactID AND
Contacts::Today < WeCalled72::Date AND
Contacts::TodayPlus3 > Wecalled72::Date AND
Contacts::constWeCalled = WeCalled72::Method
Now your conditional format expression can be:
I assume that this change is not retro-active, right? I made the above changes and everything turned blue. :)
I assume it's because a note has not yet been entered with this new relationship?
It is retroactive.
As designed, no note entered will result in your blue color.
If you used:
JobComments::ContactID AND Not WeCalled72::ContactID
You won't get the format change when no related comment record exists.