How to force a table to create an entry for each day of year?
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?