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

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.

Outcomes