Getting values from FileMaker using whose clauses.

This seems to be a common problem and I found no clear solution: getting values of fields in a FileMaker database using whose clauses.
Unfortunately there are a number of pitfalls:

records whose cellvalue of cell "MyField" is "somevalue"

Although this works without needing a specific layout or the database being the frontmost:
is very slow;
is case sensitive, at least in some versions;
raises an error if not found (-1728, “Object not found.”)

According to AppleScript’s manual “The value of a filter reference form is a list of the objects that pass the test. If no objects pass the test, thelist is an empty list: {}.”

Well, at least cellvalue returns a list if only one is found.

A much faster (hundreds of times faster) solution is:

records whose cell "MyField" is "somevalue"

But…
Fails with an error (-1728, “Event not handled.”) if:
The database is not the frontmost;
The current layout does not contain the queried field(s)
The query fails.

Yes, same error for any of these situations!

If it finds only one occurence it does not return a list of one element.

So, here comes a suggestion, respecting the expected result and all:

		tell myDocument
			show layout "A Layout with my fields"
		end tell
		tell myTable
				if (count of records) = 0 then return {}
			-- believe me!
			try
				set x to {}
				
				set x to exists (records whose ¬
					cell "theID" begins with theId)
				-- error -1708, "Event not handled."
				
				set x to {} & ( ID of records whose ¬
					cell "theID" begins with theId)
				-- error -1728, "Event not handled."
				
				set x to {} & (cell "Another field" of records whose ¬
					cell "theID" begins with theId)
				-- error -1728, "Object not found."
				
			on error number -1728 -- or -1708
				-- do nothing
			end try
		end tell

Oh! And forget about first record, some record. You will always get them all!

Any flaws or pointers to more elegant solutions are very welcome.

Hello.

You forgot to mention the untidy screen due to the flickering windows. I experienced the same a couple of years ago. I drew the conclusion, that solutions involving Filemaker Pro, really needed to be centered around FileMaker Pro. I just wonder if the runtime engine that ships with FileMaker Pro Advanced has the same deficiencies. I dread the fact that I didn’t buy FileMaker Pro Advanced instead, but before I eventually do, I am going to check if it is as dependent on showing forms on screen as its little brother.

Hopefully someone one day will come up with a solution, from which we can intergrate seamlessly with a relational database manager from AppleScript :wink:

– I usually do something like this. It’s fast.



tell application "FileMaker Pro Advanced"
	tell window "test" -- database filename is "test.fmp12"
		go to layout "CASES"
		if (count of requests) > 0 then
			delete every request
		end if
		set newRequest to create new request
		go to newRequest
		set cell "case_type" of newRequest to "work comp" -- finds all "Workman's Compensation" records
		-- field "case_type" must be on current layout ("CASES")
		find
	end tell
	tell document "test" -- document = found set from above find
		if (count of records) is 0 then
			display dialog "No records found!"
		else
		-- do something with records
			set id_list to get data field "id" -- getting "field" gets all cellvalues in found set as a list
			-- field "id" must also be on current layout
			return id_list			
		end if
	end tell
end tell


Hello.

Thanks. Hopefully, that works in regular FileMaker Pro as well. I’ll check it out.

Applescripting regular FileMaker Pro should be exactly the same as Advanced.

And I am pretty sure, that when I come into trying this, -finally, then I’ll come back to you with a quote say something like: “In order to be able to do that, you’ll need the FileMaker Pro Advanced version”. :slight_smile:

Hello.

When I try to “Go to layout” then I get this as a result back with FileMaker Pro: “FileMaker Pro got an error: Object not found.” :slight_smile:

I’m sorry this took a while.

If you used the correct window name and layout name, then one of the fields you are referencing is probably not on that layout.

Hello.

Ok, It failed, before I got as far as to the field names, so, the fieldnames, I am not sure was correct, (it did fail at the line with the “go to layout”. I have now had a look in the AppleScript dictionary of FileMakerPro, and I will come back to you, once I have written an example that should be 110% correct with regards to field, layout and database names.

I really want this to not fail! :slight_smile:

OK. If you need a demonstration file I can create one for you.

Hello.

I am actually around to creating a new database at the moment, so it isn’t really necessary.

I’ll just see to that I don’t use the same names for forms and tables, and the likes, so that there can’t be any room for disambiguition. I’ll be back with the results, and I’ll also post the script when I have finished it. :slight_smile:

Hello kerflooey.

:slight_smile: Thank you very much for beeing insistant!

Well, short story, why bother to design a database if this don’t work up front, so I grabbed the Tutorial file, choose a layout based on a table(!), I modified your example, and it works like a charm, with FileMaker Pro being hidden all the time!

tell application "FileMaker Pro"
	tell window "Sample" -- database filename is "sample.fmp10"
		go to layout "Club List"
		
		if (count of requests) > 0 then
			delete every request
		end if
		
		set newRequest to create new request
		go to newRequest
		
		set cell "Membership Type" of newRequest to "Continuing" -- finds all "Workman's Compensation" records
		-- field "case_type" must be on current layout ("CASES")
		find
	end tell
	
	tell document "Sample" -- document = found set from above find
		if (count of records) is 0 then
			display dialog "No records found!"
		else
			-- do something with records
			set {fname, lname} to get data {field "First Name", field "Last Name"} -- getting "field" gets all cellvalues in found set as a list
			set nameList to {}
			repeat with i from 1 to (count fname)
				copy {item i of fname, item i of lname} to end of nameList
			end repeat
# Should have deleted the find request somewhere here but. ;)
			return nameList
		end if
	end tell
	
end tell

Congrats, well done. Note that you don’t actually have to go to the layout before running the script on it; ie, you can simply tell the layout.

You can use the FM script step “Perform Applescript” to run an Applescript, from either the text in a field or within the script step itself. When doing that, you don’t want to use the “Tell Filemaker” command as you would when running an Applescript in the outside Script Editor.

The Applescript in an FM field can be the result of a calculation. So, for example, you could put placeholders in a global Applescript template field and automatically Substitute() FM field values for the placeholders in a (non-global) Calculation field, and run the calc field in the Perform Applescript script step.

Thanks for the tell layout, and not “go to”, not that it seems like a big difference at the moment, but I’ll check out what the differences are. Dealing with this, I have reread an article at MacTeck, that may sure be handy for others: MacTech | Introduction to scripting FileMaker Pro.

To be honest, the reason for my problems, is that I wanted to be able to use the database, manually, or have it open, without having to close it down, when the script run, (Then that layout, that contained calculated fields, had to be in front, or at least visible in order to succeed.

Now I want to check out, if I can “cheat”, by either openeing the database with another account, for merely reading, or having a copy of the layout for viewing, but without any calculated fields, for accessing from AppleScript, to have an easier but more unintrusive solution.

I do use some AppleScript from The Filemaker side of things, what I am still amiss at is using FileMaker from the AppleScript side. But you have helped me “rewind a bit”. Thing is, is that I stopped using growl when I upgraded to Mavericks, so I only remembered part of the reason for what troubled me concerning FileMaker Pro and AppleScript. I had totally forgotten that the reason was that I made it that way, so I didn’t have to close the database down while AppleScript was running.

Now, I hopefully have some other tricks in my bag that may work, should the situation recurr, that I have alternatives for the annoying “flickering windows”. :slight_smile:

Hello kerflooey.

I wonder if you can produce a simple example of creating a new record in AppleScript, and populate it with values.

Just a simple record into a simple file, that has one index, no relations or anything. Just so I have an example that should work, for the simplest of cases.

Thanks. :slight_smile:

Edit

I found this page, where I think all the examples will work for me, so I have an example. Thanks any way.

Metaclarity “ Filemaker, Web Design and Development, Applescript, Training and more. » Blog Archive » Filemaker Pro AppleScript “ Part 1

If the Applescript is a FileMaker script step it can be preceded with a “Freeze Window” script step. But that is one of the reasons to use the suggestions: using kerflooey’s method adds even more flickering by going to find mode and back, besides destroying the current found set, perhaps making the database user scream in despair.

What I forgot to mention is that this a solution mainly for when one wants to talk to the database and not the document. It does not interfere with the current found set while searching the entire set of records. I added to the confusion by writing “tell myDocument”

Otherwise when scripting FileMaker one is always better off following the way a interactive user does it: create requests and use them by talking to the document. Accessing the database is a pure AppleScript thing and is full of weird behaviour. Note how skillfully the script avoids the FileMaker trap of returning the full database data when the the careless writer expects a simple reference. Anyway the idea is to point to some quirks and solutions to the problem of getting values from FileMaker using whose clauses. Kerflooey correctly makes the point that there are other ways.

I should also note that “hundreds of times faster” is kind of meaningless when it takes a few milliseconds. The indications above apply to tables with thousands of records. Otherwise “cellvalue of .” is (almost) perfectly OK.

So a little editing is in order, to make things more clear. Thanks for the comments.

-- returns a list (possibly empty) of the ID of the found records 
-- or the contents of "My field" in the found records.
-- x is either true or does not exist


set mysearch to "I hope you find what you are looking for"

tell application "FileMaker Pro Advanced"
	
	tell window "My database"
		set oldlayout to current layout
		show layout "A Layout with my fields"
	end tell
	
	tell database "My database"
		tell table "My table"
			if (count of records) = 0 then return {}
			-- If there are no records in the table, the following queries will NOT error
			
			set theResult to {}
			
			try
				
				set x to exists (records whose ¬
					cell "My field" contains mysearch)
				-- error -1708, "Event not handled."
				
				
				-- or
				set theResult to (ID of records whose ¬
					cell "My field" contains mysearch) as list
				-- error -1728, "Event not handled."
				
				-- remember that the AppleScript "ID of record" is different from the ID you get 
				-- with Get( RecordID ) calculation function!
				
				-- or
				set theResult to (cell "Another field" of records whose ¬
					cell "My field" contains mysearch) as list
				-- error -1728, "Object not found."
				
			on error number -1728 -- or -1708
				-- set theResult to {}
			end try
		end tell
	end tell
	
	tell window "My database"
		show layout oldlayout
	end tell
	
end tell

return theResult