9 Replies Latest reply on Jun 10, 2013 9:40 AM by CraigAlderson

    Trigger date redux



      Trigger date redux


           OK, I don't know why I'm having so much trouble with this, but I am. Phil was really helpful getting me straightened out a few months ago on one aspect, but I'm now refining my system and hitting another mental block. Here's the situation:

           I have a membership database with master table "MT." Related table "A" has a trigger date for various updates that need to be made when that trigger date is reached. Related table "B" (also related to MT) has two other date fields that also need to be monitored, and action taken when the current date >= either of those two date fields.

           I have a flag on my main data entry screen that I would like to display whenever *any* record in the table has [ current date  >= (A::trigger-date or B::date1 or B::date2) ].

           I set up an unstored calculation field in the master table to be true when current date is >= (A::trigger-date or B::date1 or B::date2), thinking this would be the way forward. But now I'm stumped getting beyond that.

           Ideas? Thanks.

        • 1. Re: Trigger date redux

               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.

          • 2. Re: Trigger date redux

                 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.

            • 3. Re: Trigger date redux

                   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.

              • 4. Re: Trigger date redux

                     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.

                • 5. Re: Trigger date redux

                       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.

                  • 6. Re: Trigger date redux

                         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.

                    • 7. Re: Trigger date redux

                           OK, let's try that as a jpeg now.

                      • 8. Re: Trigger date redux

                             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.

                        • 9. Re: Trigger date redux

                               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.