by Bob Cusick /
Clickware <clickware@aol.com>
Creating new records using relationships
This is a HOT TIP that I stumbled across one day...
I wanted to create multiple records in a related database (in my case it was for a double-entry system), and I used the "normal" (2.x) way of going to the file, performing a script that created the records, then going back to the "original" file, copying the data, going back to the related file, pasting the data... well, you get the idea.
[Ed - beginners, if you are unfamiliar with the process of creating records in a second file from within a first file then read the information found at the end of this article to understand the older process of creating records in another file.]
Then it HIT ME! These files are related! After playing around for a while, I came up with a relational way to add records. Here's the theory:
When you have a relationship defined, FileMaker tries to "match" records between two files based on some common value. If there is NO common value (and you've checked "Allow creation of related records" for that relationship), FileMaker will then create a record and automatically fill in the common value.
Ok, so far, so good. The next step is to come up with an "indexable" field (i.e. not a global, container, or summary field) to define the common value. I just use a calculation field I call "Constant" that is a number field with the value of 1. This "Constant" field will be the same for every record in my database, so I don't have to worry about being on (or going to) a particular record to make this technique work.
Next, in the file that I wanted to create the records in, I made a blank number field (I call it "Temp ID" - you'll see why in a minute), then I created a relationship between the two files based on "Constant" and "Temp ID" (and I made sure to check the "Allow creation of related records" checkbox!).
Now I created a script that relies on the "Set Field" command to create the new record by setting the field "Temp ID" (temporarily) to "1" (thus making a new record), then I set all the data for the new record, and most importantly reset the "Temp ID" field to 0 (or any other value besides 1). That's it! I now have a new record filled out, and as long as I set the "Temp ID" field to anything other than 1, there will never be any "common values" between the two files and FileMaker will always create a new record.
This technique is fantastic for "Loop" routines!
Example ScriptMaker Script:
Go To Record/Request/Page [First]
Loop
Set Field [My Relationship::Temp ID, "1"]
--This creates the new record
--Set as many fields as you want here
Set Field [My Relationship::Temp ID, "0"]
--This resets the id so that no records "match"
Go To Record/Request/Page [Next, Exit after last]
End Loop
This is part of "101 FileMaker Pro Secrets" available for $19.95 from ClickWare. You can visit their Web Site at <http://www.clickware.com/>.
[Ed - The Old Way - When working in FileMaker 2.X, and needing to create a new record in another database, a user would have to set up a somewhat complex system. First the user had to create a script in the target database that basically made a new record. Then in the primary database a user would have to create a script that would call that external script from within the target database. Getting data over to the other side took a copy and paste step in the right sequence to get the job done.]
## END ##
Bob Cusick is a FileMaker Pro developer who creates custom databases, database tools, freeware, shareware, commercial products, and is the president of ClickWare. Check out ClickWare's Web Site at <http://members.aol.com/clickware> for more FileMaker Pro Tips and free downloadable files. Please feel free to send any comments or suggestions to clickware@aol.com.