5 Replies Latest reply on Feb 4, 2012 8:16 AM by philmodjunk

    How to force a table to create an entry for each day of year?

    RobGriffiths

      Title

      How to force a table to create an entry for each day of year?

      Post

      I have a fairly complex (for me, anyway) sales tracking database that uses a series of linked tables. One of these tables (named DAILY) calculates daily summary values from a few other tables, and it does this by creating a new record for each day of the year.

      I created a 'run at open' script that basically looks at the DAILY table, and if it doesn't find a record for today's date, it creates one. This works great, except when I don't open the database for a few days. Then I have to manually insert the missing records for the days I didn't open the database.

      (Because of the way I use the data in other applications, I can't create DAILY records ahead of time - the data from FileMaker is dumped and used in a series of Excel worksheets.)

      To fix this, I think I need a script that would scan the existing entries in DAILY and look for gaps, based on today's date. In theory, it would look like ...

      ---

      Set EndDate to today

      Set StartDate to Jan 1 2012

      loop x from StartDate to EndDate

      if (x is not found in DAILY) then create x

      step loop; exit when done

      ---

      However, the specifics of what that script would look like elude me ... can someone point me in the right direction?

      Thanks;

      -rob.

        • 1. Re: How to force a table to create an entry for each day of year?
          philmodjunk

          Go to layout [Daily]
          Show All Records
          Sort [Restore ; no dialog ] //sort by date in ascending order so most recent date is last record
          Go To record/Request/Page [last]
          Set Variable [$Date ; value: Daily::Date ]
          Loop
             Exit Loop if [Get ( CurrentDate ) > $Date ]
             Set Variable [$Date ; value: $Date + 1 ]
             New Record/Request
             Set Field [Daily::Date ; $Date]
          End Loop

          • 2. Re: How to force a table to create an entry for each day of year?
            RobGriffiths

            Thanks! That's more than I was hoping for, and it seems to work as expected.

             

            -rob.

            • 3. Re: How to force a table to create an entry for each day of year?
              RobGriffiths

              Actually, now that I looked more closely, it's not quite right: this will start at the last record, which will (more than likely) be today's date, due to the run-at-launch script. What I need the script to do is parse through the existing entries in the table, and only insert records if there's not a match. For example, the data for the last 10 days might look like this (assuming today = Feb 3):

              Feb 3
              Feb 2
              [--missing--]
              Jan 31
              Jan 30
              [--missing--]
              Jan 28
              [--missing--]
              [--missing--]
              Jan 25
              etc... 

              The brute force method would be to set the start date to January 1st, and then run a find for January 1st in the table. If it's not found, create the record. If it is found, skip forward one day, then search for January 2nd. Repeat until the check date equals today's date.

              But I was hoping to avoid running that many searches, especially as the year progresses ... but thinking it through, I don't see a more elegant solution.

              -rob.

              • 4. Re: How to force a table to create an entry for each day of year?
                RobGriffiths

                OK, progress, and a solution! I implemented my brute force script, and while doing so, thought of a way to speed things up. The script basically does what I said above: it starts at January 1st 2012, and then searches on each date from there through today. If any records are missing, they're added. It works, but takes a bit of time.

                The timesaving bit is that I'm now writing today's date into a separate table, and I then look up that value as the starting point the next time I run the script - because any dates before that will have been fixed by the last run of the script.

                Thanks for the code idea that got me started going in the right direction! (If anyone wants me to post my brute force script, just ask.)

                -rob.

                 

                 

                • 5. Re: How to force a table to create an entry for each day of year?
                  philmodjunk

                  Once you have the gaps filled in. The script I posted can be used to prevent the occurence of any new ones. (Used that script on one of my systems for most of a year until I thought to replace it with a script that is run once a day from a server schedule to do the same thing.)