Monday, December 11, 2017

#1 2015-02-07 08:20:25 am

mouramartins
Member
From:: Algarve
Registered: 2010-11-21
Posts: 156

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:

Applescript:

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:

Applescript:

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:

Applescript:

       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.

Last edited by mouramartins (2015-02-07 08:58:23 am)


Filed under: FileMaker, slow, whose, problems

Offline

 

#2 2015-03-03 07:59:19 am

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: Getting values from FileMaker using whose clauses.

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

Last edited by McUsrII (2015-03-03 08:00:59 am)

Offline

 

#3 2015-03-05 05:31:33 pm

kerflooey
Member
Registered: 2011-07-07
Posts: 138

Re: Getting values from FileMaker using whose clauses.

-- I usually do something like this. It's fast.

Applescript:



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

Offline

 

#4 2015-03-05 06:01:22 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: Getting values from FileMaker using whose clauses.

Hello.

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

Offline

 

#5 2015-03-06 12:04:36 pm

kerflooey
Member
Registered: 2011-07-07
Posts: 138

Re: Getting values from FileMaker using whose clauses.

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

Offline

 

#6 2015-03-08 09:33:02 am

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: Getting values from FileMaker using whose clauses.

kerflooey wrote:

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". smile

Last edited by McUsrII (2015-03-08 09:33:44 am)

Offline

 

#7 2015-03-19 06:57:06 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: Getting values from FileMaker using whose clauses.

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." smile

I'm sorry this took a while.

Offline

 

#8 2015-03-20 11:37:54 am

kerflooey
Member
Registered: 2011-07-07
Posts: 138

Re: Getting values from FileMaker using whose clauses.

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

Offline

 

#9 2015-03-20 01:30:24 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: Getting values from FileMaker using whose clauses.

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! smile

Offline

 

#10 2015-03-20 02:09:31 pm

kerflooey
Member
Registered: 2011-07-07
Posts: 138

Re: Getting values from FileMaker using whose clauses.

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

Offline

 

#11 2015-03-20 02:32:02 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: Getting values from FileMaker using whose clauses.

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. smile

Offline

 

#12 2015-03-20 06:48:24 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: Getting values from FileMaker using whose clauses.

Hello kerflooey.

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!

Applescript:

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

Last edited by McUsrII (2015-03-20 06:49:32 pm)

Offline

 

#13 2015-03-22 04:04:01 pm

kerflooey
Member
Registered: 2011-07-07
Posts: 138

Re: Getting values from FileMaker using whose clauses.

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.

Offline

 

#14 2015-03-22 04:23:58 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: Getting values from FileMaker using whose clauses.

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". smile

Last edited by McUsrII (2015-03-22 04:28:45 pm)

Offline

 

#15 2015-03-22 07:37:40 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: Getting values from FileMaker using whose clauses.

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. 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

Last edited by McUsrII (2015-03-22 07:49:44 pm)


Filed under: FileMakerPro

Offline

 

#16 2015-07-20 08:56:04 am

mouramartins
Member
From:: Algarve
Registered: 2010-11-21
Posts: 156

Re: Getting values from FileMaker using whose clauses.

McUsrII wrote:

You forgot to mention the untidy screen due to the flickering windows. )


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.

kerflooey wrote:

-- I usually do something like this. It's fast.


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.

Offline

 

#17 2015-07-20 10:44:06 am

mouramartins
Member
From:: Algarve
Registered: 2010-11-21
Posts: 156

Re: Getting values from FileMaker using whose clauses.

Applescript:

-- 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

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)