What kind of relationship is there between MT and tables A and B? (The alphabet soup of table names, BTW, makes this harder to follow than using descriptive names for each.)
Are there multiple records in A and/or B for a given record in MT?
I can tell you that this syntax is incorrect:
current date is >= (A::trigger-date or B::date1 or B::date2)
but they type of relationship (one to many or one to one) will affect what syntax is correct to get what you want.
If there is at most one related record in A and this is also true for B, then the syntax would look like this:
Get ( CurrentDate ) > A::TriggerDate or Get ( CurrentDate ) > B::Date1 or Get ( CurrentDate ) > B::Date2
But that won't work if there are multiple records in either A or B for the current record in MT.
Sorry about that. I was trying to avoid unnecessary detail but obviously only made it more confusing. The master table is Members. There's a one-to-one relationship with Positions, which tracks employment information that includes dates on leave (begin date and end date) and appointment end date. There's one-to-many relationship with another table, Futures, which tracks reports of upcoming actions (there may be mutliple such records for each member). The Futures table has the trigger date. There may be multiple Member records with the same trigger date in their related records in Futures, and it's also possible that one Member record may have multiple Futures records with the same trigger date. I hope that's clear.
So, what I need is the alert to display whenever the current date is >= to the leave end date, appointment end date, or trigger date. (I had the syntax correct for the machine but tried to render it into something approximating English for my question.)
It seemed to me the simplest approach would be to set up a calculated field in the Members table that would be true whenever the dates in any of those three fields met the condition. But I don't know how to go from there.
Never mind. Got it. I created a calculation field in the main Members table to be 1 when any of the three dates <= current date, zero otherwise. I then created a summary field in the same Members table to sum the calculation field. Any result other than zero means there are records needing updating. On the main data entry screen, I created a flag with conditional formatting to display when the summary field is >= 1 and to hide when the summary field = 0.
Well, maybe not so fast. The summary field is dependent on the found set, so if no records meeting the criteria in any of the date fields are included in the found set, the flag doesn't display. That's misleading. I need the flag to display whenever any records in the the entire database meet the criteria, regardless of what's in the found set. Back to scratching my head. Any suggestions welcome.
Can you post a screen shot of manage | database | relationships cropped to just the table occurrences used for this part of your solution?
What you posted earlier:
There's a one-to-one relationship with Positions, which tracks employment information that includes...
Doesn't sound like it's really a one to one relationshiop.
Here's the screen shot. "Teachers" is the member table mentioned earlier in the thread. Every teacher is going to have one corresponding positions record, although not all the fields in the Positions table will be populated.
OK, let's try that as a jpeg now.
And PositionsForFutureTransactions is your "Futures" table?
And FutureSepEffectiveDate is the "trigger" date?
I can't tell what fields correspond with Date1 and Date2 of your original post, so will continue to use those field names
Flag calculation from the context of Teachers:
Let ( T = Get ( CurrentDate ) ;
T > Positions::Date1 or
T > Positions::Date2 or
T > min ( PositionsForFutureTransactions::FutureSepEffectiveDate )
Using the min function will return the date in FutureSepEffectiveDate that has the earliest date of those related to the current Teachers record.
Sorry. Too much to do this morning.
I need to set an alert based on these fields:
When any of these fields <= current date, the alert should display. My calculation field was slightly different. It returned "Y" if any of those fields was less than the current date. The problem is that that applied only to the found group. If the found group didn't include any records matching that criteria, the alert flag disappeared. I need it to display whenever any record in the database meets these conditions. I duplicated the t.o. of Teachers and tried a Cartesian join on the key field (Teachers::BFTIDNum - TeachersDup::BFTIDNum) but that didn't seem to work. I'll try your suggestion and see what I can do.