I received a request to tie my Studio Manager product to iCal yesterday. This is not the first time I have received the request. I am just recovering from a brief illness today and with that excuse thought I would see how hard it would be to AppleScript between FileMaker and iCal.
I have two books that should help me: AppleScript: The Missing Manual and AppleScript: The Definitive Guide. I looked at their tables of contents but didn’t see any reference to iCal. So, I then typed "AppleScript+FileMaker+iCal" into Google. I got plenty of hits to investigate.
One of my first hits lead me to reading a comment about speed. And Bruce Robertson had a tip. He said if you run the following two line Applescript in FileMaker you can instantaneously duplicate a record and all of its children records.
So I decided to try it myself.
I created a FileMaker database called AppleScript_Playground and added three tables Contacts, Jobs and Tasks. These are tables in Studio Manager. I put in a few fields in each table. I figured eventually I would get to trying to import a set of tasks for a job into AppleScript.
But for now I wanted to try the trick.
So I created a layout in Jobs with a few fields and a portal to tasks. I entered some tasks. See illustration below.
Bruce said you should not have any calculated fields on the layout if you want the trick to work. I have no idea why, but he was right. Until I removed the last calculation field from the layout, it wouldn’t work. You also need to have the ability to create the child records turned on and don’t have the auto-enter serial number on the screen.
Anyway, if you have an auto-enter serial number for Jobs and that serial number is part of the key that links tasks to jobs, you are in business.
Here’s my applescript compliments of Bruce:
Set sourceRec to (get current record)
Create new record with data sourceRec
There’s only one script step in the script: Perform Applescript.
Bruce says it should be instantaneous with up to 50 related records.
At the moment this small triumph encourages me to continue my quest. Maybe this AppleScript stuff is worth looking into. I know a lot of FileMaker buffs swear by AppleScript…
UPDATE: I just tried it with Job Specs, a second related table. The duplicate as is above duplicated the original record plus the related records in both portals on the screen instantly! Try this out as a 20 minute project if you aren’t already an AppleScripter who knows this stuff backwards and forwards.
2 replies on “Duplicate Record with AppleScript Trick”
Glad the portal creation technique worked for you. The reason that you can’t have calc fields on your layout is that the applescript is going to fill all the fields in your new records with data from the original record. But you can’t write into calculation fields. So the simplest solution is just to not include them in the layout. Likewise with the record ID fields. You don’t want to write old values into these fields in the new records.
I started to wonder if you use some of list commands to mold the entire object to having more data in new portal-rows, and then overwrite the old record with the same simplicity as the creation of new records with the same data.
Record ownership is to blame here, it won’t work but if the only requirement is to add a new record in a portal, say logging of calls from this thingy:
What is required here is to locate the callers number in the filemaker base and then make a new logging i the found records portal.
A not yet there record or phantom is not seen by applescript so I needed to tickle a filemaker mechanism… well read my post in fmforums: