New Records via a Constant and globals
There are a few ways to create a new record and get the proper ID into it. The following is a fairly universal scripted method, using a “Constant” relationship and a global field to temporarily hold the ID value. It doesn’t require that the relationship be defined to “allow creation of related records.”
It also doesn’t require any other relationship other than the Constant one. So it can be used when there’s no other obvious way to pass data from one file to a new record in another file. Constant is a calculation field, equal to 1, hence it is always one in every record. A relationship from a Constant field (or any field equal to 1) in one file to a Constant (or 1) field in another relates to all records in the other file.
A global field can only hold one value for all records in a file, and it is accessible from all records in the file. (Strangely enough, it can hold a value even if there are no records; it is independent. It can also hold many items, if they are separated by carriage returns. But this is still only one value.)
Two files, Parent and Child.
Parent fields:
ID, text, auto-enter serial ID, increment by 1
IDg, global (text)
Constant, calculation, =1 (number)
Child fields:
ID, text (not auto-enter)
Constant, calculation, =1 (number)
Relationship: Parent –>Child; ID relationship, ID =::ID
Relationship: Parent<– Child; Constant relationship, Constant =::Constant
There are two scripts, created separately in the two files. The first one in the Parent file calls the second one in the Child file as an external script, so that they run as one script.
I added Comment steps, like FileMaker shows them; but you can delete them.
New Record Script
Set Field [“IDg”,”ID”]
Comment [“Puts the current ID into a local global field.”]
Exit Record/Request
Perform Script [External, “Child”]
Comment [“New Record2 <–Parent Script”]
New Record2 <–Parent Script
New Record/Request
Exit Record/Request
Comment [“Allows Constant =1 to evaluate”]
Set Field [“ID”,”Constant::IDg”]
Comment [“Reaches back and gets the ID from Idg in Parent. It can use the Constant relationship because there is one and only one IDg value in either file, available to all records.”]
Exit Record/Request
Comment [“Allows the ID relationship to evaluate. Other script steps using the ID relationship will now work.”]
Check for Records First (slight detour)
If you don’t necessarily want a new record every time; if you just want to go to the records if there are any, only creating a new one if there aren’t, then add these steps first, in the New Records script:
Exit Record/Request
If [ not IsValid (ID relationship::ID)]*
Perform Script [“New Record”]
Else
Go To Related Record [Show, “ID Relationship”]
Perform Script [External, “Child”]
Comment [“List View <–Parent”]
End If
Add this short script in the Child file, to be called from the above script.
List View <–Parent
Go To Layout [“List View”]
Halt Script
Comment [“Halt is needed to stop all scripts; otherwise you’d be returned to the Parent file by just the End If step.”]
*The ID field could be any field that you’re sure has data. I often use the Constant field, which would look like, ID relationship::Constant. But I didn’t want to confuse you using the name twice.
If you wanted to do something else in Child, you could set the ID into a global field, then use that in a different relationship to go to other “filtered” records.
Setting Fields between Files without an ID Relationship
Even if there is no ID relationship possible between two files, you can use the Constant relationship and global fields to set any field between two files. Just create a “mirror” global field for any field that you want to set into the other file, much like in the ID example above.
You can create this field in either file. This is because, as we said before, there is one and only one value in a global field for one file, and the Constant relationship can access it from any record in the other file, in either file. So you can set it in either file and retrieve it from either file.
We used the first method, setting a local global, in the New Record script above. It is the safest way to create a new record if there is a possibility of no records in the other file.
But to set other fields, there is another similar method you can use:
Create another Constant relationship, this time from Parent –>Child;
Constant =::Constant
Create a text field, Tools, in Parent.
Create a text field, Tools, in Child.
Create a global field, Tools g, (text) in Child.
Set Fields Script
Set Field [“Constant::Tools g”,”Tools”]
Comment [“Sets the tool into the global in Child”]
Perform Script [External, “Child”]
Comment [“Set Tool Script”]
Set Tool Script
Exit Record/Request
Set Field [“Tool”,”Tool g”]
As you can see, it’s fairly simple once the fields are there. And there can be many fields in each script. They all just need their mirror global field in the Child file. You can set them one after the other, all with the same script.
One advantage of this method is that all the extra global fields can go in the Child file, where they make more sense, and don’t clog up the Parent file with fields. Remember that a global field takes up very little space; it is not stored. To make it really clean, you can clear out all the globals after running the script, with a series of:
Set Field [“Tool g”,””]
(for each global field).
Just make sure that you’ve set the global into a regular field first. (I forgot once that I’d moved the Set Field [“Field”,”Field g”] step later in a script. I couldn’t figure out why Tools was always empty; I’d emptied Tool g already!) It’s not really necessary, as Set Field replaces whatever was in the field every time anyway.
Other uses
The global via Constant method is also useful when you are setting things temporarily into fields during scripts, or performing a series of calculations or validations before setting a value into the other file.
Other methods
New related records can be created directly if you check “allow creation of related records” in the relationship definition box. All that is needed is to populate a related field (not the ID key field) with data. This can be done either manually, by typing into a portal based on the relationship, or by populating a related field via a script.
Lookups
Another way to set the fields, after using the first Global method to set the ID, or importing just the ID field, would be to simply define the related fields as Lookups, in the Options dialog, using the ID relationship.
If, however, you have fields that depend on there not being a Relookup on the relationship (such as First Mark fields, which would lose their mark), then you wouldn’t want to do that. Remember that a Relookup refreshes all fields that use that ID field in their lookup definition (including ones you’ve forgotten about and those pointing to other files the ID has a relationship to). Both of these are also good methods; they each have their uses, limits, and difficulties (which are more than I can go into right now, even if I knew them all, which I don’t).
The great thing about using the global method is that you can read the steps in the script and see what is happening and when. I often use it first, to get things working. Then I consider whether to use a lookup for some of it instead. It’s easy enough to change for just a few fields. For a lot of fields, however, such as when moving practically a whole record, I would go with lookups whenever possible; importing only an ID field, then Relooking up if necessary. This method is slow, but it’s a safe way to go.
I will try to go into all the ins & outs of these other methods in a later article. Next month, though, we’re taking an extensive look at documenting your database.
Fenton Jones
Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com
Leave a Reply
You must be logged in to post a comment.