I’ve been working on some scripts to search and update tables in a FM application. I’ve basically written Applescript functions to do this. Each has used the same type of code to create a new record when necessary:
…
create new record at database irisTitles
save database irisTitles
set lastRecord to count record of irisTitlesDB
set cell “Work No.” of record lastRecord of layout 0 of irisTitlesDB to workNumber
set cell “Title” of record lastRecord of layout 0 of irisTitlesDB to workTitle as text
set cell “Title Qualifier” of record lastRecord of layout 0 of irisTitlesDB to titleQualifier as text
set cell “Title Notes” of record lastRecord of layout 0 of irisTitlesDB to "SPIRO " & titleNote as text
save database irisTitles
…
For some reason, this code doesn’t work for ONE of the tables. I’m working with updating tables from a purchased FMPro application and we are importing data into the system. I’ve been looking at the field info, layout properties, etc. to determine what if anything is different from the other tables and can’t find anything different.
I’m baffled as to why the code shown above will work for the majority of the tables I’m updating and generating the error
“FileMaker Pro got an error: Set Data failed. Invalid data was supplied.”
for another table. I’m running this from withing Script Editor so that I can see the events and results and nothing odd appears there.
I don’t believe the issue is data type related. By now I’ve probably triple checked the field names and data types to make sure there’s nothing wrong.
I should mention that this code works if I change the record number to one that already exists in the table instead of trying to write to the newly created record, which I think is odd. Does that shed any light for anyone?
Here’s the type of script that generally works for me.
tell application "FileMaker Pro"
tell database irisTitles
set nr to create new record at end
tell nr
set cell "Work No." to workNumber
set cell "Title" to workTitle as text
set cell "Title Qualifier" to titleQualifier as text
set cell "Title Notes" to "SPIRO " & titleNote as text
end tell
end tell
save database irisTitles
end tell
Thanks for that suggestion. But that produces the same error for me when I use it. Like I said, the code works if I don’t have lastRecord pointing to the newly created record. So I’m wondering if there’s some issue with getting FileMaker to recognize the new record for writing for that table?
The only other thing to say is that every field I’m writing to is indexed in the application, but does not require the value to be unique so it’s not like I’m trying to write to a record that already exists and can’t have another record with its value.
Yes, the first SET statement after the create record.
If I comment out that SET and run again, then the next SET after that commented out one is highlighted and I get the same error. The log events show the new record created and if I go look at Filemaker I can find the new record at the end with the fields empty except for those that are defined at new record generation (basically the primary key - auto sequential number) and some global variables (that are used when working via the usage of the cataloguing application and that I know nothing about).
Here some syntaxes which work well for me (to be adapted, of course, at your data base):
-The very shorted version ;-)
tell application "FileMaker Pro"
tell database 1 to create new record at end with data {"One", "Two"}
end tell
--With a variable for the new record
tell application "FileMaker Pro"
tell database 1
set NewRcrd to create new record at end
tell NewRcrd to set data to {"Three", "Four"}
end tell
end tell
--With a variable and data for each cell
tell application "FileMaker Pro"
tell database 1
set NewRcrd to create new record at end
tell NewRcrd
set data cell "Txt01" to "Five"
set data cell "Txt02" to "Six"
end tell
end tell
end tell
--Data for each cell with another syantax
tell application "FileMaker Pro"
tell database 1
set NewRcrd to create new record at end
tell NewRcrd
set cellValue of cell "Txt01" to "Seven"
set cellValue of cell "Txt02" to "Eight"
end tell
end tell
end tell
It is a related table. If there is a syntax issue with that, I do need to understand it.
The table I’m having trouble with is called Titles.
I’ve got a Works table that has a portal with the ability to see mutiple titles. The title for the work is stored in the titles table and the work number in the works table is stored as a foreign key in the titles table.
In addition, there is another table, wrelated that contains two foreign keys that relate works to other works. So the portal in Works actually involves both the related works and titles table since both show up in that same portal.
I did originally try something like
tell database works
create new record at works
create new record at titles
set “titles::title” (or set “title”)
…
But that was causing problems as well.
If you could provide an example of the syntax required when working with related tables I’d appreciate it.
I won’t even pretend to understand the structure that you’ve outlined but maybe I can explain with pseudo code. Lets say that your are trying to create the new records in a database named “Master” and some of the fields in Master are related to fields in a database named “Slave”. Without doing a mockup, I think that the following is what you would use.
tell application "FileMaker Pro"
tell database "Master"
set nr to create new record at end
tell nr
set cell "Slave::somefield" to "some value"
end tell
end tell
end tell
‘somefield’ is a field in Slave that is displayed in Master. I haven’t done much scripting of related files but I’m fairly confident that this is accurate.
Is (are?) the data you are using the correct type for the field? i.e., if you try to use Applescript to set a date field to “Scripting FileMaker is a breeze!” FM will return “FileMaker Pro got an error: Set Data failed. Invalid data was supplied.” And that would be true, because it was. :?
Appreciate the suggestion. The field and data in question is text. I am aware of that problem and make a point of specifying AS TYPE where ever possible even if the type is the same - just as a precaution.
