FileMaker record updating not working

[jackinva]

Folks,

I've only been working with Applescript for two weeks (and FileMaker for about a month) and gotten some help from these forums. I'm at a point where I'm stuck. I'm reading from one FileMaker (which is working fine from the event log debugging) and trying to update records in another FileMaker database. Both files are opened as references. The loop I'm using looks like this:

repeat with i from 1 to 3

-- Get the Spiro values to use to populate the IRIS system

set spiroCurrentRecord to (a reference to record i of layout 0 of spiroSlideViewDB)

-- Go get an IRIS record and update it

set irisSurrogateRecordNumber to irisSurrogateStart + i - 1

set irisSurrogateRecord to (a reference to record (irisSurrogateRecordNumber) of layout 0 of irisSurrogateDB)

-- Map the data

set digitalFileName to cellValue of cell "imageID" of spiroCurrentRecord

set cell "View Description" of irisSurrogateRecord to cellValue of cell "viewDet" of spiroCurrentRecord as text

set cell "View Type" of irisSurrogateRecord to cellValue of cell "viewDesc" of spiroCurrentRecord as text

-- NOTE: picDate does not contain valid data

set cell "View Date" of irisSurrogateRecord to cellValue of cell "picDate" of spiroCurrentRecord as text

-- Format the color Spiro metadata to the required value in IRIS before setting the record value.

set imageColor to cellValue of cell "color" of spiroCurrentRecord

if (imageColor = "c") or (imageColor = "c ") then

set imageColor to "color"

else if (imageColor = "b") or (imageColor = "b ") then

set imageColor to "b/w"

else

set imageColor to ""

end if

set cell "Color" of irisSurrogateRecord to imageColor as text

-- Format the orientation Spiro metadata to the required value in IRIS before setting the record value.

set orientation to cellValue of cell "orient" of spiroCurrentRecord

if (orientation = "l") or (orientation = "l ") then

set orientation to "horizontal"

else if (orientation = "p") or (orientation = "p ") then

set orientation to "vertical"

else

set orientation to ""

end if

set cell "Orientation" of irisSurrogateRecord to orientation as text

--save irisSurrogateRecord

display dialog irisCollection & " record " & i & " (" & digitalFileName & ") has updated Surrogate record " & irisSurrogateRecordNumber & "."

end repeat

I've tried doing the set statements as "set cell" and "set cellValue of cell". Neither works. I tried using the commented out "save ...Record" thinking I needed to do that. I don't get any error, I just don't see the new values appearing in the records. Can anyone tell me what I'm doing wrong?

Maybe it is FileMaker related?

The FileMaker things to mention are that the database I'm writing to was purchased. It requires a password when opening it. Does anyone know if FileMaker would require a password to update records via AppleScript? I tried adding a "with password ..." to the set statements or save statement but I get a syntax error.

Also does FileMaker allow you to disable updating records via AppleScript calls to the application? I'm wondering if that's why the records aren't updating (if the syntax above is correct).

Thanks,

Jack

[keshalyi]

Well, there are a lot of things that COULD be going on... I think you'll need to narrow this down with a bit of debugging! First off, you need to figure out where your lack of data is coming from. The easiest way, I've found, is to put a display dialog command in the script, displaying the value of, in this case, imageColor. I'd put one right after you first mine the first database for a value, to start, and see if it equals anything. If not, then yu need to focus on getting data out of the old database. Start with that, and just move the dialog later in the script, to trace what happens to the value as it goes. Once you have a better idea of where you're losing your data, you'll have a better idea of how to permanently fix the leak.

BTW - I don't really get EXACTLY what you're trying to do, but I think you're probably working too hard, all this work cuold be done more quickly and efficiently, I think with a scriptmaker script, from the looks of it....

[jackinva]

Thanks for the tips. I actually got it figured out. Everything was writing to the database but to a record 50 away from the one I was expecting. Turns out that the "reference to record X" was not what I thought it was.

set irisSurrogateRecord to (a reference to record (irisSurrogateRecordNumber) of layout 0 of irisSurrogateDB)

What is expected is the physical record number of the record. I thought that the primary key (when it's numeric auto-incrementing) was what would be specified. When records are deleted these are no longer in sync.

RE: BTW - I was originally planning to use FM script. However, I don't know how that could be done when I really need to be working between two files simultaneously and the FM scripting options don't appear to support the programming concept of "file handles".