using whose clause in FileMaker Pro 8

Scripting Forums AppleScript | Mac OS X
,
#1

Hi all,
I have a script that export a lot of information (Estimated costs Bid) from FileMaker 8 to InDesign CS2 and it’s working ok, but yesterday I discover a little bug, and I didn’t found a way to resolve it:
The script uses information about the gender of the destined person of the letter to write appropriate salutation in Spanish (Estimada or Estimado), but in many cases the Data Base don’t have this filed filled, so the script ask for the gender, so it can use the adequate word and in the background fill this data in the appropriate table in FM. This is done now, using info of a cell that gives to each person a unique number “ClaveContacto”, so when necessary, go in the background and fill the cell “Sexo”, but assuming that this unique number is also the number of the record, but I never stopped to think that the user can delete records, (Daah!) :frowning: so at this point the “ClaveContacto” will never coincide with the record number and of course I receive an Error, “Object not found”.
My actual script looks like this and this process are doing without interruption of my principal script:


tell application "FileMaker Pro Advanced"
        tell database "DKontrolData"
            tell table "Directorio"
                set cell "a10 Sexo" of record (ClaveContacto as integer) to SexoD
            end tell
        end tell
    end tell

So I try to rewrite my code with something like:

"set cell "a10 Sexo" of (record whose cell Clave Contacto is ClaveContacto) to SexoD

This code doesn’t work at all, and I don’t found any way to do this.
As I understand in FM the only 2 ways to found anything is using “Show” or “Make a new request” but this commands will interrupt my process in InDesign, and this is not desirable, so anybody knows a way to do that?

The other way I tried to solve this issue is by using the Record ID of the appropriate record, so in the data base I put a new filed “RecordID” and defined it’s value using the FileMaker Function “Get (RecordID)”, but FM gives me a very different value from the one that I see in the Explorer of Script Debugger, for example Script Debugger show an ID like “3.3393E+4” and FM gives “754” and if I try to use this last value, I get the same Error "Object Not Found’ but I can’t see any way to get this ID within FileMaker
I know this last question isn’t exactly AS, but is related.

In the Hanaan Rosenthal’s book page 43 is an example that tell:


tell application FileMaker Pro"
  Tell table "guests" of database "party"
       Set kids_list to cell "name" of every record whose cell "age" is less than 12
   End tell
End tell

I try this using exactly the same code “of course using my variables, table & database names“ and I also get same error “Object not found”

Can anyone give a tip about how to set some data in a table using the whose clause or similar?

Thanks in advance

Carlos Ysunza

#2

This might help. Its a snippet from a larger script that I am sure you can adapt for your purposes. This simply lists images in a folder, searches for them in the correct table in filemaker and then renames them. I have found that Filemake can be quite fussy if it is not told where to look for the info ie the layout, the database, the table, hence the go to commands that finally made this work


tell application "FileMaker Pro Advanced"
		go to database clientdata
		go to layout images
		tell table images
			tell (every record whose (cell "filename" = i_mage))
				try
					set clientid to cell "client_id"
				on error
					
					set the_message to i_mage & " is probally not a filename. name images script"
					my createlog(the_message)
					activate
					display dialog i_mage & " is probally not a filename. Please check the folders content" buttons {"Cancel"}
				end try
			end tell
			
		end tell
		go to database clientdata
		go to layout "Form"
		tell table clientdata of database clientdata
			tell (every record whose (cell "client_id" = clientid))
				set n_ame to cell "fullname" as text
			end tell
		end tell
	end tell

Model: 11 macs, all my babies
AppleScript: 2.1.1
Browser: Safari 417.9.2
Operating System: Mac OS X (10.4)

#3

Jacques, thanks a lot that is exactly what I’m looking for “first record whose cell…” now it works ok
!So many hours just to find this little detail, that’s the AppleScript it is :rolleyes:

Thanks weedinner!

#4

Jacques I have bad news…
My happiness was just for a minute…
I first tested the line set cell “a10 Sexo” of (first record whose cell “Clave Contacto” is ClaveContacto) to SexoD with the window were resides the cell “a10 Sexo” opened and it works, so I just say YES this works!, but when I make my test in the real conditions that this script will work, that is, without this window opened, sadly I received an error “Event not handled.” How frustrating

As I explain in my first post this action must be done in the background without interruption of my principal script that is exporting lots of information from several tables to InDesign.

QUESTION: someone knows how to get the value “ID” of a record in FileMaker Pro?

Any ideas
Thanks

#5

As far as I know Filemaker will not get a cell value if the cell is not in the current layout, table, database. If your script calls for cell “a10 Sexo” that is not in your current/table layout then the script will fail. The workaround is to place the cell in the current layout that the script is getting the rest of its info from providing the two tables are related. The scirpt would then read

set cell “anotherTable::a10 Sexo” of (first record whose cell “Clave Contacto” is ClaveContacto) to SexoD

But without knowing how your database is constructed its difficult to advise

Model: 11 macs, all my babies
AppleScript: 2.1.1
Browser: Safari 417.9.2
Operating System: Mac OS X (10.4)