Does anyone have an angle on this...? Trigger create new record by date (Say, July 1.)

[coolnige]

If I have an existing record for web hosting (Jan - June), how can I automatically trigger on July 1, the creation of a new record for the next billing period (July - December)? and so on?

And how cool would it be to copy the information in the previous record into the new one... maybe I am asking too much:)

Edited May 14, 2010 by Guest

[Zig13]

First set up a global field somewhere to record whether the record has already been created for that year.

Next create this as a custom function:

Let ( [

Da = Day ( theDate ) ;

Mo = Month ( theDate ) ;

Yr = Year ( theDate )

] ;



Yr*10000 + Mo*100 + Da

)

I call it DateNo. Set theDate as a parameter.



The rest is done as a script:

If [Last ( Table::YearsDone ) ≠ Year ( Get ( CurrentDate ) )]

   If [DateNo ( Get ( CurrentDate ) ) ≥ DateNo ( 1/7/Year ( Get ( CurrentDate ) ) )]

   Go to layout [Relevant Layout]

   Go to record [last]

   Copy Record/Request

   New Record/Request

   Paste [select]

   Set Field [Table::YearsDone; List (Table::YearsDone ; Year ( Get ( CurrentDate ) ))

   End If

End If

Set the script to run on start-up (or integrate with your existing start-up script)

The only thing I am not sure of is the copy and pasting an entire record as I haven't used either of those script steps before.

I'm sure a more experienced user will eventually post a more elegant way of doing it but for now it's worth a try. Good Luck!

[comment]

If you know now you're going to need that record, why not create it right away?

[coolnige]

Yes, good point. This database is in an office where we run a list of projects. (jobs). Active jobs have a status of 'Open' so my thinking is to create a new record only when it is valid.

I.e. From the date the billing period begins.

Btw. The Forum is great.

Nige.

[coolnige]

Wow thanks, am away from the office but can create this and trail this remotely. Weekend here in Oz. Will let you know. Gosh I love FM.

[coolnige]

I have begun and will let you know. And thanks...

[comment]

I'd still suggest you rethink the entire strategy. Do you intend the billing to be fully automated, or is user intervention required at some point?

The former IS possible, but not quite as simple as you describe. And you certainly wouldn't want to hard-code any dates into it, so that it can repeat periodically.

If the latter, there's plenty of time to create the necessary records when someone initiates the process.

[comment]

The only thing I am not sure of is the copy and pasting an entire record

There are plenty of other things you shouldn't be sure of, such as using a global field "to record whether the record has already been created for that year" or the result of:

DateNo ( 1/7/Year ( Get ( CurrentDate ) ) )

to mention only two examples.

BTW, you don't need to convert dates to numbers in order to compare them; Filemaker knows what a date is and can compare two dates directly.

[coolnige]

The thinking is to trigger the new record on expiry of the previous (hosting) period.

A new record will then appear in with other 'Open' jobs.

We are in a graphic design agency where we are opening new projects 'jobs' recording tasks 'expenses' related to the unique 'job' number and once completed 'closing' them for billing.

So triggering the creation of a new job to appear in with existing open jobs is a great thing. We can them manually update text etc and close them for billing.

Nige.