Log in
Hot & Trending on FMT

FM 12 ExecuteSQL, part 2

FM 12 ExecuteSQL, part 2 - Kevin Frank FM 12 ExecuteSQL, part 2 - Kevin Frank

One of the fun things about a new FileMaker release is figuring out how new features work, including subtle behavioral nuances. Today’s demo file, FM12 ExecuteSQL Help Example from CW, is based on Corn Walker’s re-working of one of the demos from part 1, and helps demonstrate how ExecuteSQL fits in with the FileMaker security model. 


As you may recall from part 1, the demo consists of two tables, Employees and Salaries. From an architectural standpoint, the “Salary” field could easily live in the Employees table, but for security reasons, it has been placed in its own table.

There are two accounts: 1) “Admin” linked to the [Full Access] privilege set, and 2) “User” linked to the Restricted privilege set…

…and the Restricted privilege set has been denied access to the “Salary” field in the Salaries table.

So what you see in this demo depends on whether you are logged in as “Admin”…

…or as “User”.

When logged on as “User”, you might expect the Salary_SQL calc in the Employees table to fail but the one in the Salaries table to work, which is how normal calculation fields behave, but instead ExecuteSQL behaves as if it’s always going across a relationship and therefore observes access restrictions in both tables. (Contrast this with the behavior of Salary_Calc in the Salaries table, and thank you Corn for pointing this out.)

Also, if you’re following along in the demo, make sure to click the “Display Dialog to Show Salaries” button when logged in as “Admin” and also when logged in as “User”.

I’ll have more to say about ExecuteSQL in upcoming postings. Until then, happy FileMaking.

Kevin Frank

FMT Staff Note: IF you would like any of Kevin's tip or techniques incorprated into a solution or project contact him at his website below.

Hello and welcome. I’m Kevin Frank, and I’ve been using FileMaker Pro since the late ’80s… professionally since 1995. The expression “hack” has both positive and negative connotations. Here it is defined as “a tip, trick or technique that helps you solve a problem,” and the best hacks are the ones that can be re-used and modified to meet a variety of challenges. Or, put another way, as I read recently in Street Fighting Mathematics, “A tool is a trick I use twice.”

Kevin Frank and Associates provides custom FileMaker database solutions for business, government, education and non-profit clients. With over 20 years experience, we are certified FileMaker Developers with a solid reputation for high quality results. We invite you to call us today for a no-charge, no-obligation phone consultation.

To find many more in depth articles from Kevin and some must have tools and services visit his site at the link below:

Website: www.filemakerhacks.com