Upon new record creation, create related entries?

[torifile]

I've got a database with patient information. Each patient has a number of different types of notes associated with them. Some of these notes are "one time" types of things, like an intake report. Others are multiples, like session notes.

Is there an easy way to create entries in related tables automatically upon creation of those these entries? For example, I've got a "master treatment plan" table, with patientID as a foreign key. When a new patient is entered into the database, I'd like a way to have the master treatment plan table populated with the foreign key so I'm ready to go when it's time to write that report. Any ideas?

Right now, I've created a script that uses globals to pass along the foreign key between the layouts and create a new record in that particular layout. Seems a bit cumbersome and probably wrong.

[Fenton]

It is my opinion that the pre-creation of child records is not needed. I don't see why passing a value to create a new child record would be "cumbersome." It happens in a fraction of a second. If the global field seems awkward, then use a script variable; that is a more modern method.

Alternatively, you could type something into a portal, with the [x] Allow creation of related records turned on. But I'm assuming this is not what you want, so I'll continue.

I would tie the creation of the child record into scripted navigation to the child form. First, check that at least one child exists. If one does not exist, create one. Then go there. No muss, no fuss, no one has to know.

If [ not IsEmpty [ child relationship::parent_id ]

# there's one there

Go To Related Record [ child relationship; show related; Child Form (Child table occurrence) ]

Else

Set Variable [ $parent_id; Parent_ID ]

Go to Layout [ Child Form (Child table occurrence)]

New Record

Set Field [ Parent_ID; $parent_id ]

Commit Record [ No dialog ]

# In order to Look Up any data

Go to Field [ preferred field ]

# or just Go To Next Field

End If

[torifile]

It's certainly not "cumbersome" in the sense that there's too much to do, it just didn't to be the most straightforward solution (to be honest, I figured since it was the only way I could figure out how to do it, there must be some other way!).

What you're telling me is that my method is the "right" way to do it and now that I've created the script to implement it, it's not a problem at all.

My only remaining concern is that there should only be one of particular types of child records; that is, a 1:1 relationship, I think. My script doesn't prevent multiple entries. It's not a huge deal because this is just for me and a few colleagues, so I'll just tell them about that quirk, but I like to try to design something bulletproof and correct. But it looks like the script you put up there addresses that concern, so thanks. :

I was wondering what you meant by "script triggers". Can you elaborate some on that?

Edited April 28, 2009 by Guest

[torifile]

It works wonderfully! Thanks! One more question: when I perform the script the first time, my results are filtered to the one child entry, but subsequent runs of the script just add them to the found set. How do I get it to "reset" the new window?

[Fenton]

"Script triggers" are an addition in FileMaker 10, so you don't have them. I said "Script Variable", which is a script step, which you do have; it allows you to create "named variables" during a script(s), which you can then use to pass values (or just store results). You should read up on them.

I'm not sure what you mean about the 2nd question. I would not open a new window. In the Go To Related Record step I would choose the (•) Show only related records, current record only. In this case that would be a found set of one.