There’s something I’m not seeing in my attempt to get certain fields out of a certain window called “Layout #2”. Certain clients (master_area) have specified bullet points, noted in a record that has “Bullet Point 1” in a field called VARIABLE_MASTER__R.NAME with its value showed in a field called “VALUE_C”. If they do not, then the default bullet point is used (bullet_ValueDefault) as noted. If a client has its own bullet points, then the below should pick them up and replace the (bullet_array) with its own.
What’s happening is that it seems to skip the code for “Bullet Point 1” and returns: {“bullet 1 default”, “bullet 2 default”, “string that belongs to cobv2’s Bullet Point 2”}. I’m struggling to determine why it’s not copying over Bullet Point 1? As you can see below, there always should be a Bullet Point 1 if a client has any bullet points entered. It does here. And if it finds that value, it should clear out the old default list. So while it returns the default and bullet point 2, it seems to be skipping the Bullet Point 1.
Looking through the Event Log History, it’s finding that record for Bullet Point 1. So I’m not seeing something that may be obvious to a more eagle-eyed contributor here.
Here’s a subset of the trouble area:
set master_area to "cobv2"
set bullet_ValueDefault to {"bullet 1 default", "bullet 2 default"}
tell application "FileMaker Pro"
try
delete every request
end try
tell layout "layout #2"
create request
set cell "AREA_ID__C" of request 1 to ("=" & master_area)
find window 1
set bullet_Value to {}
set totalVar to count of records -->13
if totalVar > 0 then
repeat with thisVar from 1 to totalVar
tell record thisVar
set bullet_Value to bullet_ValueDefault
if cell "VARIABLE_MASTER__R.NAME" is "Bullet point 1" then
if cell "VALUE__C" is not "" then
set bullet_Value to {}
copy cell "VALUE__C" as string to end of bullet_Value
end if --bull 1
end if -- bullits 1
if cell "VARIABLE_MASTER__R.NAME" is "Bullet point 2" then
if cell "VALUE__C" is not "" then
--set cell "VALUE__C" to end of bullet_Value as string
copy cell "VALUE__C" as string to end of bullet_Value
end if --bull 2
end if -- bullits 2
end tell --record thisvar
end repeat --thisVar
end if --totalVar
end tell --layout
end tell --app fm
return bullet_Value
I mightily appreciate any time you may share on my issue, thanx, sam
Model: mac pro
Browser: Safari 525.27.1
Operating System: Mac OS X (10.5)
I’m a little fuzzy on exactly what’s doing and happening, however it looks like you perform a find and then act upon the records found. Yes?
create request
set cell "AREA_ID__C" of request 1 to ("=" & master_area)
find window 1
set bullet_Value to {}
set totalVar to count of records -->13
From there you repeat thru those records found, in this case 13 records.
repeat with thisVar from 1 to totalVar
tell record thisVar
Perhaps this is where the reference to a record is mismatch since record 1 of the find results, for example, is not record 1 of the database. If you have 20 records total, and the find results returns 13 then there are 7 records before, after, or in-between them.
I know it’s suppose to work when referencing records of the layout instead of the database; ie., record 1 of the database is not necessarily record 1 of a layout depending on how the records are sorted and which are omitted from the layout. However I found that FileMaker could easily become confused with references to record number more often than references to record ID (at least it did in version 4; I don’t have the latest version yet). I’d be more assured of results and accuracy if I worked within a ‘tell database X’ instead of ‘tell layout X’ when acting upon records.
After the find command, it might help if you repeat thru record IDs instead of repeating with x from 1 to (record count).
tell layout "layout #2"
create request
set cell "AREA_ID__C" of request 1 to ("=" & master_area)
find window 1
set bullet_Value to {}
--set totalVar to count of records -->13
set recordIDlist to ID of every record
end tell
tell database "[the-database-name]"
if class of recordIDlist is not list then ¬
set recordIDlist to {recordIDlist}
repeat with aRecordID in recordIDlist
tell record ID aRecordID
--do stuff
end tell
end repeat
end tell
However, as I mention, I’m not working with a current version of FileMaker so I’m just guessing and it may not apply.
You should use the following structure to talk to your database
Tell application “FileMaker Pro”
Tell Database “My database”
end tell
end tell
Now, when you talk to your database this way, referencing a record # will be on the WHOLE database, not on the found set. If you perform a find, and you then want to cycle thru only the found records, you must switch to this structure
Tell application “FileMaker Pro”
Tell document “My database”
end tell
end tell
When referencing to your file with the “tell document” structure, record 1 will be the first record of the found set instead of the first record of the database.
Thank you very much. The entire tell structure was left out of this snippet for readabillity, but the snippet behaved exactly as it was in the much bigger structure.
But it appears that my problem was a little more pedestrian. I was pulling in the bullet_valueDefault on each iteration of each record and it was overwriting each bullet except for the ultimate one.
However, your efforts are appreciated because the tell structure of Filemaker is not very straightforward as I’ve read it. These explanations should help me to clean up my code. You’re kind to have helped me, thanx, sam
Scripting FileMaker works best when avoiding code that mimics user interaction and acting upon the object model accordingly. There’s no need to display records in a layout to find certain ones and get or set values of cells. For example, if your database name is “THEDATABASE.fm”
set master_area to "cobv2"
set bullet_ValueDefault to {"bullet 1 default", "bullet 2 default"}
tell application "FileMaker Pro"
tell database "THEDATABASE.fm"
--find ID of records you want to change
set records_to_change to ID of every record ¬
whose cell "AREA_ID__C" is equal to master_area
--if one record matches, result is a number (a record ID)
--if more than one record matches, result is a list of numbers (record IDs)
--normalize result to work within a repeat loop
if class of records_to_change is not list then ¬
set records_to_change to {records_to_change}
--change records
repeat with an_ID in records_to_change
--that form of repeat works best using "contents of [variable]"
-- instead of working directly with the variable
set thisRecordID to (contents of an_ID)
--act upon each record by referencing each record ID
tell record ID thisRecordID
set bullet_Value to bullet_ValueDefault
if cell "VARIABLE_MASTER__R.NAME" is "Bullet point 1" then
if cell "VALUE__C" is not "" then
set bullet_Value to {}
copy cell "VALUE__C" as string to end of bullet_Value
end if --bull 1
end if -- bullits 1
if cell "VARIABLE_MASTER__R.NAME" is "Bullet point 2" then
if cell "VALUE__C" is not "" then
--set cell "VALUE__C" to end of bullet_Value as string
copy cell "VALUE__C" as string to end of bullet_Value
end if --bull 2
end if -- bullits 2
end tell
end repeat
end tell
end tell
I’m to understand that after version 4 FileMaker adds options for layouts to display like a spreadsheet, and then later(?) versions also allow several documents to be contained within a single database. This makes it easier, tho less intuitive, to work with FileMaker’s AppleScript objects in a one-to-many relationship (one database contains several documents which contain several records which contain several fields). The only time the layout objects come into play is when the script needs to work with different layout formats (with number and/or text styles, for example), or if the UI and/or user is part of the action. Otherwise, it can all happen to the data without needing to bring FileMaker to the front or showing layouts on the screen.