5 Replies Latest reply on Apr 9, 2013 10:52 AM by philmodjunk

    Setting a flag based on a background count



      Setting a flag based on a background count


           This seems like it should be simple, but I'm stuck. My file opens to a main data entry layout. I want to be able to display a reminder flag on the screen -- "Records need processing," for example -- if records exist in the database having a trigger date = current date. After I've processed the records, the reminder needs to disappear. 

           I can use conditional formatting to make the reminder appear and disappear, and it seems like I should be able to do a find in the background, then Get(FoundCount), and change the formatting based on count = 0 or >0. This can be attached to the OnRecordLoad trigger of the main layout. I'm just not sure how to get that count. Do I have to open a new window and a new layout every time, run the find, then close the window? Seems like there should be a better way.

           Unfortunately, my years of MS Access coding are creating a mental block on this.


        • 1. Re: Setting a flag based on a background count

               Use a relationship to count the records where trigger date = today's date.

               Assuming that the records with the trigger date are in the same table as that referenced by your "main data entry" layout, this requires a self join relationship:

               MainData::cToday = MainData 2::TriggerDate

               In Manage | Database | relationships, make a new table occurrence of MainData by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

               We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

               Now your conditional format expression can be either:

               MainData 2::TriggerDate   // if a true result makes the message visible

               Not Maindata 2::triggerDate // if a true result makes the message invisible

          • 2. Re: Setting a flag based on a background count

                 Ah, right. The records are in a different table, but that's not a problem. Thanks for the conceptual kick in the pants.

            • 3. Re: Setting a flag based on a background count

                   I forgot to specify that cToday must be defined with get ( CurrentDate ) and "do not store..." must be selected for it in storage options.

              • 4. Re: Setting a flag based on a background count

                     OK, I'm just feeling really obtuse here. The main data layout has the current data in a membership table. We get advance notices of future updates to that data, which I enter into a related table, "Futures." I enter a trigger date in the related table so I know when to apply the change to the current data. I can manually run a find with trigger date <= today's date to find those records I need to process.

                     I want a flag on the data entry screen that displays whenever any record in the database has a trigger date <= today. That will alert me to process the future updates. Once they're processed, the flag should disappear. This should be easy to handle with conditional formatting, right?

                     Per Phil's suggestion, I tried setting up separate occurrences of the future updates table and related it to itself, Futures1::Today's date >= Futures2::TriggerDate. No problem there. I then set the conditional formatting as follows:

                     Futures2::TriggerDate // visible
                     not Futures2::TriggerDate // invisible

                     The flag disappeared, but it stayed invisible when I entered test trigger dates that should have displayed it. I tried using the IsEmpty function in a similar way. No difference.

                     What am I doing wrong?

                • 5. Re: Setting a flag based on a background count

                       Set up an UNSTORED calculation field, cToday, defined in your layout's table to compute today's date.

                       Define this relationship between your layout's Tutorial: What are Table Occurrences? (I'll call it Main) and your Futures table:

                       Main::cToday > Futures::TriggerDate

                       then this logic, in either a calcualtion field or a conditional format expression will be true if any records in Futures with a Trigger date less than or equal to today exist:

                       Not IsEmpty ( Futures::TriggerDate )