How do I automatically create a related record in Table 2 by creating a record in Table 1?

[Billy Bob]

Hi folks

I've trawled through the forums but I can't see anything on this topic that I can relate to my circumstances.

I've created a job tracking DB. Currently, when I create a new job I then add it to a related work schedule manually. I'd like to know how to get the DB to automatically add the new job to the schedule once it is created.

Each job record has a unique reference number. Currently, to add a new job to my schedule I create a new record in the schedule then select the job reference number I want from a drop down list. This populates a portal in the schedule record with information from the job record that relates to scheduling. I hope that makes sense...

In the 'Edit Relationship' dialogue box I've now selected 'Allow creation of records in this table via this relationship' for the Schedule table, but nothing has changed. When I create a new job record I still then have to schedule it manually. Is there something more I need to do? Perhaps with the fields in the schedule layout itself?

I'd be grateful for any help anybody can provide!

[Genx]

...Script it.

Create the job with a button, save the id, go to your other layout, create a new record, set the ID...

e.g.

Freeze Window

Set Error Capture[On]

Allow User Abort[off]

Create New Record / Request[]

Set Variable[$id ; Jobs::JobID]

Go To Layout[TheOtherOne - shedule or whatever]

Create New Record / Request[]

Set Field[ Sched::JohID ; $id ]

Go To Layout[original layout]

[Billy Bob]

Hiya Jenx

I was afraid somebody would say that. But, since you've already done the script for me, I'll give it a go...

I've had a dabble with the scripting (and successfully too, which surprised me) but I find that generally I learn more by looking at examples than reading a list of functions and whatnot, so the script you've given me will be more than just a means to an end. Thanks again! I'll post to let you know how it goes. As a noob I think that's about the only useful contribution I can make to this forum right now.

I'm still confused by one thing though: if 'Allow creation of records in this table via this relationship' doesn't automatically create a related record in the 'Schedule' table based on a record in the 'Job List' table, what does it do? Is it one of those features that doesn't actually work in practice? I say that because I have a lot of experience with QuarkXpress, where 'features' that are, in practice, too buggy to rely on are just a fact of life...

[comment]

What precisely is the relationship between Jobs and Schedule? Can a Job have more than one related record in Schedule? Or do you have items in Schedule that are not jobs?

[Billy Bob]

Hurrah - it works. It took me five or six attempts to understand your script and relate it to what I was trying to do but I got there in the end. Thanks Jenx!

[Billy Bob]

Hi Comment

A job can't appear more once in the schedule, and only jobs can appear in the schedule.

The job record is a central dumping ground for all of the information about a given job. Each job has a unique ID.

The schedule record actually only contains one field: the job ID, which I use as a match field for the job ID in the job record. The rest of the schedule record is just a portal displaying the portions of info from the job record that are relevent to scheduling, such as the delivery date and important notes. The idea is that when a job is delivered I can remove it from the schedule but still retain the main job record.

I'm happy with Genx's answer to my question, so I shan't be offended if you don't follow this up, but I'd be most interested in any 'comments' you want to make.

[comment]

It seems there's no information in the schedule table, so you don't really need it. Moreover, this sentence puzzles me:

The rest of the schedule record is just a portal displaying the portions of info from the job record

If a schedule record has only one related job record, then there can only be one record in the portal, no? But then we don't need a portal - we can place related fields directly on the layout. A portal is for displaying MULTIPLE related records, like line items of an invoice.

It sounds like all you need is another layout of the Jobs table, "displaying the portions of info from the job record that are relevent to scheduling".

To answer your other question: 'Allow creation of records in this table via this relationship' means that entering data into a related field - whether placed directly on the parent layout, or in a portal - will automatically create a new related record. That's why portals based on such relationship have the one last empty row - typing into it creates a new record.

[Ian Murphy]

I think Billy bob found a workaround that sufficed, but I'm coming by looking for exactly what his first question was - is it possible to mandate that creation of a new record run a script?

In this scenario, I've got a main record, then a variable number of editions that might or might not exist. These are aretworks - so there might be only one perfect copy, there might be eight. There won't be zero. They will be priced differently and sold to different people, so they need to track differently. Straightforward enough - a serialized relation and a portal [and some automation] take care of the editions. What I'm figuring out, though, is that life would be much easier if we defaulted to one related record, rather than zero.

Easy enough to write a script that creates a record, inserts a serial number, then goes to the second database and creates a related record with the same serial. The client is using FileMaker already, though, and I'm sure is used to Cmd-N-ing for a new record, and I'd sure like this to function under that circumstance.

[comment]

is it possible to mandate that creation of a new record run a script?

No, it is not - at least not without a plugin. Nor should it be necessary it most cases. A new related record, automatically created, has by definition no information. Therefore it is redundant. As I have explained, you can create a new related record automatically by entering data into a portal. Thus the record is created only when needed, and there are no "stock records" to mess up reports.

[Genx]

I'm with comment on this one, most of the time entering the data directly into a portal is more than sufficient and appropriate.