There 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 does not require that the relationship be defined to "allow creation of related records."
It also does not require any other relationship other than the Constant one. So it can be used when there is 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.
In this article we will take advantage of the fact that it can have a value even when there are no records, and that value can be set from other files, even if there are no records, and therefore no valid relationships either! As far as globals are concerned, all relationships are valid. We use the Constant relationship in this method, because it's the most basic, but really any other would work.
The reason this is important is because, in every file's life, there may be a time when it is brand new, and has no records; it could have been cloned, for example. You should always check to see if your record-creation scripts will work with 0 records. This method will. It is also perhaps the fastest.
Two files, Parent and Child
Parent fields:
ID, text, auto-enter serial ID, increment by 1
Constant, calculation, =1 (number)
Child fields:
ID, text (not auto-enter)
IDg, global (text)
Constant, calculation, =1 (number)
Relationship: Parent --> Child; "ID" relationship, ID =::ID (the regular relationship, but not used here)
Relationship: Parent --> Child; "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.
"New Record" script (in Parent file)
| Set Field ["Child-Constant::IDg","ID"] | <---- Puts the current ID into the Child global field |
| Perform Script [External, "Child"] | <---- "New Record2<--Parent" script |
"New Record2<--Parent" script (in Child file)
| New Record/Request | |
| Exit Record/Request | <---- Allows Constant=1 and ID_g to evaluate |
| Set Field ["ID","IDg"] | <---- Sets the global ID_g into the ID field |
| Exit Record/Request |
The last "Exit" allows the Child ID field to be registered. All relationships and lookups using it 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"] <-----"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
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. This is NOT the Constant relationship. It is the ID relationship.
If you wanted to do something else in Child, you could use its global ID field in a different relationship to go to other "filtered" records.
Setting Fields between Files with No 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, and set it with a Constant relationship.
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 Tool -->Child"
| Set Field ["Constant::Tools g","Tools"] | <------ Sets the tool into the global in Child |
| Perform Script [External, "Child"] | <------ Set Tool Script |
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. A global field takes up very little space; it is not stored.
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 (incl. 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. 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 (which can be tedious to do with lookups).
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; though it's awfully slow.
Send comments, questions, toys to Fenton Jones