5 Replies Latest reply on Dec 4, 2012 1:38 PM by philmodjunk

    Hi, I would like to check each table in my database to see if there are any new or updated...

    brian.curran

      Post

           Hi,
           I would like to check each table in my database to see if there are any new or updated records. A dialog box would then be displayed stating whether data had been updated or not for today.

           I tried the following script:


      Enter Find Mode [ ]
      Set Field [ Site::gz_ModTimeStamp; // *.*.* ]
      Set Field [ AlarmCo::gz_ModTimeStamp; // *.*.* ]
      Set Field [ Arc::gz_ModTimeStamp; // *.*.* ]
      Set Field [ Police::gz_ModTimeStamp; // *.*.* ]
      Set Field [ Group::gz_ModTimeStamp; // *.*.* ]
      Set Field [ Client::gz_ModTimeStamp; // *.*.* ]
      Set Field [ Address::gz_ModTimeStamp; // *.*.* ]
      Set Field [ KeySummary::gz_ModTimeStamp; // *.*.* ]
      Set Field [ People::gz_ModTimeStamp; // *.*.* ]
      Set Field [ SyncJoinPeopleSite::gz_ModTimeStamp; // *.*.* ]
      Set Field [ SiteDetailsForDocuments::gz_ModTimeStamp; // *.*.* ]
      Set Error Capture [ On ]
      Perform Find [ ]

           Unfortunately, the above didn't work! Any suggestions?

           Thanks
           Brian.

        • 1. Re: Hi, I would like to check each table in my database to see if there are any new or updated...
          philmodjunk

               Using your method, you'd have to perform one find for each table on a layout based on that table.

               A simpler approach might be to define an unstored calculation field returning date as: Get ( CurrentDate ) then use it as a match field to a modification date field in each of these tables.

               Then:

               Not IsEmpty ( RelatedTable::ModDateField )

               will return true if there are any records in the related table with today's modification date.

          • 2. Re: Hi, I would like to check each table in my database to see if there are any new or updated...
            brian.curran

                 Hi Phil,
                 I already have the current date in a Global field along with Modification fields in each table but I'm not sure what you mean by "use it as a match field". Could you expand on this point please?

                 Thanks
                 Brian.

            • 3. Re: Hi, I would like to check each table in my database to see if there are any new or updated...
              philmodjunk

                   You would add some new table occurrences for your existing records and link them in relationships by modification date.

                   Example:

                   Main::cTodaysDate = SiteByModDate::ModificationDate

                   where "main" is the occurrence on which you base your layout for this check and SiteByModDate is a new table occurrence that also refers to Site.

                   Do the same for each table.

                   But I just took a closer look at your field names. Does the g in gz_ indicate that these fields have global storage specified? Are they calculation fields with global storage?

                   You can't enter find mode and specify find criteria in global fields so that is another reason why your original script doesn't work. But if these are calculation fields with global storage specified, you could modify them to return only the date and then:

              Site::gz_ModDate = Get ( CurrentDate ) OR AlarmCo::gz_ModDate = Get ( CurrentDate ) or Arc::gz_ModDate .... // include all of your global fields in like manner.

              will evaluate as true only if at least one record in at least one of these tables has today's date as a modification date. (Global calculation fields pull data from the most recently modified record according to FileMaker Help.)

              This may not work, however in a multi-user environment where I do know that the relationships I spelled out will work in both cases.

              • 4. Re: Hi, I would like to check each table in my database to see if there are any new or updated...
                brian.curran

                     Ok, I've created the duplicate TO's and linked them to Main::cTodaysDate. The 'gz' prefix purely relates to GoZync, so not Global fields. They are set as TimeStamp fields and auto-entered whenever a record is modified.

                     So should I use the 'not Is Empty' line from above still?

                     Thanks
                     Brian.

                • 5. Re: Hi, I would like to check each table in my database to see if there are any new or updated...
                  philmodjunk

                       Note that I am using DATE fields not time stamp fields in my example. You can either add date fields to your table that auto-enter a date or you can define a calculation field that extracts the date from the timestamp field. You don't want to use a timestamp field for this as the data in a timestamp field is stored as seconds since Midnight 12/31/0000 where a date fields is stored as the number of days since 12/31/000.

                       

                            So should I use the 'not Is Empty' line from above still?

                       You could use that line but

                       If [ RelatedTable::ModDateField ]

                       should produce results identical to:

                       If [ Not IsEmpty ( RelatedTable::ModDateField ) ]

                       either way, you can use the Or operator to string together all off the related field references to get a result of "True" if at least one table has a newly modified/added record.