FM 12 ExecuteSQL, part 2

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.