LSNOVER

Great new Stuff for ESS fans in FM13

Discussion created by LSNOVER on Dec 10, 2013
Latest reply on Dec 11, 2013 by gdurniak

Hi Folks:

 

I've been very eager to share this info. One of the "issues" we have had with ESS is the inability to call Stored Procedures in our SQL Databases directly from Filemaker. This can be done with the old "Execute SQL" SCRIPT STEP, not to be confused with the ExecuteSQL function. The only problem was that the Execute SQL script step requires a LOCAL ODBC driver. This is a big pain in the butt, to say the least, and defeats one of the nicest features of ESS, which is centralizing all the ODBC setup stuff to the server.

 

Well, with FM13 and the "Perform Script On Server", our problems are solved! We can now use the Execute SQL Script step ON the server, called from a "Perform Script on Server" script! This is a fantastic capability. No more kludges using utility tables and table triggers to call Store Procs! In addition, if you finesse your Error routines in SQL, you can pass back some pertinent info. directly to Filemaker by way of the Error handler. Let's not forget FMGO and Webdirect, for which it is otherwise IMPOSSIBLE to install a ODBC driver. You can now execute SQL stored procs on FMGo and in WebDirect!

 

NEXT, hidden in FM13, is a new Summary function. This one is a little obtuse because of how it has been implemented. As you know, one "issue" for many of us in the FM community has been finding an "easy" way to get all of the Field IDs for the records IN THE FOUND SET. Should be simple, but it's not. Many kludges abound. This is something many Filemaker developers want to do, regardless of whether they use ESS. But for us ESS fans, it's particularly important, because we have no other way of "communicating" what the found set records are, to the SQL database, when we want to run Server side procedures.

 

So in FM13, you can now define a new type of Summary field. The summary type is "List of", and you specify the Field from the table you want a "list of". Once defined, this field will hold the values for all of the records in the found set, for the field you specify. Wala, you now have a nice relatively fast list of your record IDs for the found set. This can now be passed to a Stored Procedure, or saved in a utility record to provide the SQL database with context for your users found set. This is also quite handy when using Perform Script on Server in Filemaker. Remember, when you use Perform Script on Server, your called Filemaker Script has NO CONTEXT of your current Filemaker Client session (which means Pro, FMGo and WebDirect). You must think of these scripts like a user firing up a fresh copy of your application from scratch, and program what needs to happen from there. When the script finishes running, that "special" user session closes out and is done. The nice things about "List of" summaries.... no need to go off to a special layout, loop through your found set, etc.

 

These are two seemingly "trivial" features in FM13 that open up all kinds of new capabilities and flexibility and really make SQL database calls much more convenient.

 

Enjoy ESS Minions! ;-)

 

Regards,

Lee Snover

Outcomes