I have a database for scheduling work. Most work is scheduled in meetings, and descriptions, due dates, etc are generated manually. However, a certain amount of work is preventative maintenance; so it reoccurs on a weekly, biweekly, monthly, etc. basis. So, I have a database for work orders, and a source database for Preventative maintenance tasks. All of the records in the PreventMaint database are "generic", that is they are a description

I want to write a script that loops through all of the Preventmaint database's records, calculates which tasks are due, and creates any necessary records in the work database. No problems on the calculations to separate what I need, but I'm at a loss as to how to script creating records based on a separate database. I have no idea how to go about doing this. Any suggestions?

Well, we don't really know how you're creating records normally. But I think that the piece you're missing is the use of a global ID key to establish a link between files. Once a global field is set, it can be used to reference any data in the other file.

First, to easily manage complexity you must have unique IDs for your Maintenance records, should be auto-entered increment. If not, create them, serialize with the Replace command.

So your Loop in the Maintenance file goes through the records. From each record it can use a regular relationship from its ID to the Work file to look at its linked entries (must have/know an ID field in Work to link to).

You're probably interested in the latest date, which should be available as either Last (ID relationship::date), or, if necessary, Max(ID relationship::date); the former being much faster if dates are always consecutive . There are other possibilities also, like "this month, next month," etc..

In any case, once you find out that a new Work record is needed, you set the Maintenance ID into a global ID field, in either file; but fewer steps to set it into Work. You can use ANY relationship to set the global. (In v.7 you don't even need a relationship, just a Table Occurrence.)

You need a relationship in Work, from gMaintanenceID global back to the Maintenance file.

In Maintenance, in the Loop, if, after comparing dates, a new record is needed in Work.

Perform Script [External, "New Maintenance Record"] <--in Work

This will flip you to the Work file, where you'll do the following:

Exit Record

New Record

Set Field ["SomeID", "gMaintanenceID"]

Set Field ["Work", "gMaintanenceID relationship::Work"]

Exit Record

That's it. The Loop will continue in Maintenance.

I don't really know what fields to set in Work, how you tell whether work is maintenance or other. You certainly can, by setting either a separate ID field for maintenance (my preference), or by making sure the Maintenance IDs are uniquely prefixed (MN001, etc.).