Lookups and new records

[Wikisnoodle]

I have 2 related databases. Contacts and Jobs. A relationship is set up on urn fields, with create/delete records via relationship enabled on the Jobs side.

Contacts contains details of clients, contractors and suppliers and will remain ‘on site’. Jobs, whilst also on-site, will be copied onto laptops and used off-site. Some of the contact details needs to be held in the Jobs database and I’ve set this up to work using lookups.

The lookup fields successfully update via a relookup script but, when new records have been created in Contacts, relookup does not create new records in the Jobs database. Should they? Am I looking for an error in the way I have set things up or am I expecting too much?

[jdu98a]

There is not necessarily a problem with your setup. There may be a more efficient way, such as instead of having two separate database files you can simply have two tables in a single database, one for contacts and one for jobs. You may have reasons why you want separate files, and like I said it's not a broken setup it just makes keeping everything in sync a little more challenging.

In any case, the re-lookup script should not be expected to create new records. Without seeing your database it's hard to say what the most efficient way of creating new records is, but most likely the simplest solution would be to have new records in Jobs database created automatically (via a script) at the same time they are created in the Contacts database.

Here is the simplest method:

Assuming you have a key relationship field between the two tables (databases) such as an "ID" field. Once a new record has been created in the Contacts database and has been given a value in the "ID" field the simple script step of Set Field [Jobs::Any field other than "ID"] will force the creation of a new record in the Jobs database and will automatically assign the appropriate value in the Jobs::ID field, therefore relating the two records. This method has been called "Obligatory Create" by Matt Petrowsky and it saves you from having the script navigate between tables (or databases in your case) and copying over ID field values just to create related records.

And, actually now that I think about it, that Set Field script could target ANY field in the Jobs database even the ID field. You would use: Set Field [Jobs::ID, Contacts::ID] so the relationship would be created correctly.

[Wikisnoodle]

Thanks for the swift response ...great stuff!

I couldn't get it to work at first, writing a lengthy description of my woes here, eventually forced out of my weary head that I should allow creation/deletion of records on the Jobs side of the relationship (of course!)