Specifying a record in FMP precisely (aka record ID broken or just misunderstood?)

[nilness2]

I'm working on an applescript to return data to filemaker, and I'd like to be certain that it's absolutely positively going back to the right record.

Originally I was using the "record number" and the applescript syntax "tell record ###" and that seemed to be working. But occasionally the result isn't returned so in troubleshooting this sporadic issue I found that the "record id" should be a unique specifier for the record and "record number" is not.

I thought using the "record ID" would be the preferred way to do make sure I'm calling the correct record, since the "record number" is relative to the current found set and in theory *could* change.

So I began change my scripts to use the "record ID" instead.

And it seems to work great... except when it doesn't...

I've got a database of over 4000 records already processed. And I'm finding during testing that I can set values in *some* of these records, but in others I get "error: Object not found." number -10006 from cell cell_name". I've gone so far as to create a calculation filed that shows "Get (RecordID)" on the layout just so I can confirm the correct ID.

Here's the basic syntax from hand testing:

tell application "FileMaker Pro"

tell database "Main"

  tell table "Transactions"

  

	tell record ID 4462

	 set cell "Manual Error Entry" to "test entry"	

	end tell

  end tell

end tell

end tell

A note about the "tell"s - normally I calculate the Applescript in filemaker and substitute "Get (FileName)" for hardcoding the database name, likewise "Get (LayoutTableName)" for the table and "Get (RecordID)" for the record ID.

Am I wrong in assuming that "Record ID" is the preferred way to specify a precise record? Is there an error in my syntax or usage?

I'm working in a multi-user production environment, and there *could* be scripts running in Filemaker while this Applescript is running, so it seems risky to assume that "Record Number" is going to be "safe" to use in this environment because the found set could change.

Thanks for any help you can give!

--Nick

[nilness2]

After doing some testing, I've found that the simple answer is the Record ID returned "internally" by using Get ( RecordID ) will differ from the Record ID returned to AppleScript once the Record ID exceeds 127. Seems likely related to http://help.filemaker.com/app/answers/detail/a_id/703/kw/recordid but that's my speculation.

As a workaround I've created a field, recordIDFromAppleScript and set it's value using a calculated AppleScript, so that I can store and use the "external" RecordID that AppleScript requires.

Gotta say it's pretty disappointing that you can't simply pass the value of Get ( RecordID ) to an AppleScript to use as the correct RecordID to call via AppleScript to return results. The extra step means more potential to break down the line, and the extra half day of work just testing to figure out the issue to develop a work around is a bummer.

Hopefully this can help someone else out...

[Vaughan]

That article relates to an issue that affected FMP 6 and earlier, it states that non-contiguous IDs are now FIXED in FMP 7 and later.

Why not use a unique serial number to identify the record? Note that FMP's record ids are not something that developers should rely upon. They too can change if the records are exported and imported.

Where does the applescript get the record id 4462 from in the first place?

[Vaughan]

Also, make sure the Get( RecordID ) calculated field is UNSTORED.

[comment]

Gotta say it's pretty disappointing that you can't simply pass the value of Get ( RecordID ) to an AppleScript to use as the correct RecordID to call via AppleScript to return results.

I don't see what difference it makes - you can use AppleScript to get the old-style ID of the record and ignore the new Get (RecordID) completely. It's also possible to calculate one from the other (I think).

non-contiguous IDs are now FIXED in FMP 7 and later.

The AppleScript record ID remains non-contiguous.

[Vaughan]

So where is this AppleScript ID stored or generated from?

[comment]

I don't know. But if you run:

tell application "FileMaker Pro Advanced"

    ID of current record

end tell

while on record 128, you'lll get 3.2768E+4 i.e. 32,768. So the old algorithm is still there. My guess would be that the "new" RecordID is only a translation of the old value into a contiguous domain.

[nilness2]

Thanks for the replies. I'll try to answer your questions as best I can, but perhaps I should try to start with a brief overview of what I'm working on.

I've been tasked with taking our existing POS FileMaker solution and integrating it with online credit-card processing through our bank's website. The web browser "Fake" makes this possible by allowing scripting of web interactions and processes.

The recommended method of communication between FileMaker and Fake is AppleScript. FileMaker calculates an AppleScript which will launch Fake, tell it which workflow (think script for Fake) to run, and passes it a list of variable names and data, in this case the data to process the cc transaction and data about what FileMaker record to pass the results back to. Fake runs, then executes an AppleScript to pass it's results back to FileMaker, using the record data it was supplied with as the destination for its results.

Initially I was using Get (RecordNumber) and passing this info to Fake so that it would return the data via the AppleScript command "tell record ###". And things worked great during testing and rollout. But I began to get feed back that every so often, maybe once a day, the results wouldn't make it back into FileMaker. The CC processing succeeded, but FileMaker wasn't getting the results.

Investigating the issue I came to the conclusion that either sometimes Fake wasn't executing the AppleScript properly, or the results weren't going back to the correct record. Since there wasn't much to do about Fake, I investigated the possibility that the results were going to the wrong record. After all, I was new to AppleScripting FileMaker, so perhaps I should re-check what I was doing.

That's when I found some discussion of RecordNumber vs RecordID. And it sounds like it could explain my issue - if the found set was changing in the 1-2 minutes it takes to process the transaction, maybe the results were getting put into the wrong record. Either way, it sure sounds like RecordID is the more appropriate way to specify the record. And while I realize the record ID can change, we aren't doing ANY importing/exporting of records in this table, and the ID only has to be constant for the 1-2 minutes it takes the transaction to process.

So I did some hand testing. Made a simple table and filled it with a few thousand records. Checked the RecordID manually - and yes, using unstored calculations (also had a script with display custom message, just to make sure I was getting fresh data). And the results were clear - when record xyz asked its own ID via Get ( RecordID) and I compared it with what AppleScript returns from "ID of record xyz" the results would differ once the ID > 127.

Vaughn - the 4462 was gotten from Get (RecordID) in filemaker, and then hand-entered into this AS for testing. The Applescript id is generated by calculating and running an AppleScript in Filemaker which

looks like this:

"tell record " & Get(RecordNumber) & ¶ &

"set cell \"recordIDFromAppleScript\" to ID of record " & Get(RecordNumber) & " as integer" & ¶ &

"end tell" & ¶

consultant - I don't think you can calculate one from the other. At lower values the difference seems to be 32,640 but at some point the difference changes to 12,566400. There could be other differences.

Thanks for reading this. If you've made it this far, perhaps you can offer some (I hope simple!) advice on how you would handle this situation. It seems simple - use AppleScript to pass information to an external application including all the information that the external application needs in order to pass it's results back into a specific record. But you can't rely on the target record to still be the current record! I've been using 'tell database abc/tell table def/tell record ID xyz"... what would you use?

Thanks for taking the time to read and comment. I'm not a full time developer; I have been developing and maintaing this DB system for over a decade now, and have the CS background to make me think I'm not a total noob.

Also, found a discussion from Apple's AppleScript mailing list dated about 6 years ago that seems to discuss exactly this issue, but no clear resolution: http://lists.apple.com/archives/applescript-users/2005/Jan/msg00486.html

[nilness2]

And if anyone has any good resources - preferably with examples! - to learn more about AppleScript & FileMaker I'd be grateful for any links.

So far the most useful and commonly repeated link I've seen seems to be http://www.mactech.com/articles/mactech/Vol.22/22.04/ScriptingFileMakerPro/index.html but I can't seem to find much else, and next to nothing current.

Thanks!

[comment]

I feel like we're not making progress here. If you want to make sure the result is passed back to the same record you have started with, use AppleScript to get the originating record's ID. This way you will have the correct ID to use in the returning AppleScript.

Another option is to prevent the found set and the sort order from changing while your script is running - I don't think that should be too difficult to achieve.

I don't think you can calculate one from the other.

Of course you can - once you discover the logic that the ranges follow. I didn't say it would be simple, but I am quite sure there is a logic to it.