AnsweredAssumed Answered

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

Question asked by RobGriffiths on Feb 3, 2012
Latest reply on Feb 4, 2012 by philmodjunk


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?