ExecuteSQL – Taming the Beast – Part 2

In Part 1, I wrote about  my first experiences with the ExecuteSQL function in Filemaker, looking at returning a basic list of field contents, passing arguments, and the use of simple joins.  The function is proving useful, and I’m delving further into it, with help from others.

Here are are my latest discoveries.  (It’s all rather random, i.e. I’m finding out more as and when I need it, so this may not appear to be a logical order in which to describe things.)

Putting the query in a variable

It seems to be common practice to put the query statement and its result into calculation variables.  (I’ve yet to see the full benefits of this, but enough experts do it to make me think it’s good practice!)

So, instead of:

ExecuteSQL ( “SELECT nameFirst FROM PPL WHERE nameLast = ‘Smith’ ” ; “” ; “” )

We get:

Let ([
$query =
“SELECT nameFirst, nameLast
FROM PPL
WHERE nameLast = ‘Smith'”;
$result = ExecuteSQL( $query ; “” ; “”)
];
$result)

Field and row separators

By default, if the function is returning more than one field (e.g. nameFirst and nameLast in the above example), these will be separated by a comma.  You can put any other separator as the second parameter of the function, e.g.

esql1

 

 

 

 

 

 

 

 

 

(See the effect of this in the “Result” pane above.)

And if you want to separate rows with something other than the default carriage return, just put that in as the third parameter, e.g.

esql2

 

 

 

 

 

 

 

 

 

“ORDER BY”

It won’t come as a great surprise that the ORDER BY clause in SQL enables you to sort the output.  The order can be ASCending or DESCending, but if you want the result in ascending order, you don’t need to specify it, as it’s the default.

esql3

 

 

 

 

 

 

 

 

 

“WHERE…IN”

This one’s a little bit fiddly, and I needed the generous help of members of the Filemaker TechNet to get it working.

I needed to limit the scope of the query.  I had already isolated a list of keys, via a combination of Filemaker find and eSQL query,  I had hoped originally to base the next eSQL query on the table occurrence in which these keys were listed.  Unfortunately, though, eSQL will always use the BASE TABLE of any table occurrence – it doesn’t recognise the filtering via relationships which are so useful elsewhere in Filemaker.

So I’ve got a list of keys of the records, in a global field, PPL::FILTERIDS.  I want to query within this filtered subset – “within the group of records that I’ve already found, tell me how many are women, how many have disabilities, etc.”.

To start with, I’ve just done a query to give me the people’s names, like this:

esql4

 

 

 

 

 

 

So we’re saying give me the names of each person whose id appears IN the list of IDs.  But we can’t just use the field itself, because that is a return-delimited list.  The IN clause needs a comma-separated list enclosed in parens, and, furthermore, each value in the list needs to be in (single) quotes (if they are alphanumeric text values).  So the setting of the ~idList includes replacing each CR with ‘,’.  Then, in the query variable itself we add an opening paren plus, and a closing plus paren.  Fiddly, but quite portable once you’ve got it working the first time (I hope!).

Note that it’s a bit less fiddly with numeric strings, in that they don’t need the single quotes around each value.

With this as a basis, we can carry on with more complex querying.  More about that in a later post.

Advertisements

ExecuteSQL – Taming the Beast – Part 1

I’ve been getting to grips with the ExecuteSQL function.  To say that I’ve been enjoying it would be partly true – but it can be a little tricky, not without its frustrations, and disappointing in its performance.  BUT, I’m beginning to see just how powerful and useful it could be.  There are a couple of excellent resources that I’ve found very useful:  one is Filemaker Hacks – Kevin Frank’s articles about ExecuteSQL on his blog are excellent. Kevin writes so clearly, and presents the material so well, that even when he’s repeating stuff that I already know (which isn’t often!), it’s a delight to read his articles.  Of particular relevance here are:

Also, Beverley Voth’s “The Missing Filemaker 12 ExcecuteSQL Reference” – there’s a link to this on FilemakerHacks too – here.

Here are some examples of expressions using the function:

Basic List of Field Contents

ExecuteSQL ( “SELECT nameFirst FROM PPL WHERE nameLast = ‘Smith’ ” ; “” ; “” )

This will give a list of the first names of people whose last name is Smith.  A couple of things that tripped me up, even at this basic stage:

  • “PPL” is a table occurrence, not a base table name.
  • ‘Smith’ is case sensitive.

To get around the case sensitivity issue, I’ve started changing all text to lower case within the expression, e.g.

ExecuteSQL ( “SELECT nameFirst FROM PPL WHERE LOWER(nameLast) = ‘smith’ ” ; “” ; “” )

Using Arguments

The next step is to use the query term (“smith”) as an argument, rather than including it in the SELECT statement, e.g.

ExecuteSQL ( “SELECT count (*) FROM PPL WHERE LOWER(nameLast) = ? ” ; “” ; “” ; “smith”)

So now we start to see how ExecuteSQL can take the place of FM Finds.  Want to know how many Sue Smiths you’ve got?

ExecuteSQL ( “SELECT count (*) FROM PPL WHERE LOWER(nameLast) = ? and LOWER(nameFirst) = ? ” ; “” ; “” ; “smith” ; “sue”)

And, of course, the arguments can use fields instead of the literal values in the example.

Basic “Joinery”

There’s been a lot of excitement about the fact that ExecuteSQL can be used for querying related data, without the need for that relationship to be defined via Filemaker’s relationship graph.  This can mean simplification of the graph – often quite complex relationships have to be drawn on the graph just for the sake of a simple calculation of related data.

So, moving on to a basic JOIN, i.e. relating 2 tables and getting related data without (necessarily) having a relationship in Filemaker, say we want to know how many Sessions there are for a given Programme (where a Programme can have zero or many Sessions):

ExecuteSQL(“SELECT COUNT (*) FROM SSN AS s JOIN PGM AS p ON s.id_PGM = p.id WHERE p.id = ‘PGM00002866’ ” ; “” ; “” )

Note how we allocate an alias of “s” to use when we refer to the SSN table occurrence later in the expression, and “p” for the PGM table occurrence.

The “AS” in the previous example is optional, and the whole thing can be rearranged to be a bit more readable, e.g.

ExecuteSQL(“SELECT
COUNT (*)
FROM SSN s, PGM p
WHERE s.id_PGM = p.id
AND p.id = ‘PGM00002866’ ” ; “” ; “” )

And now for a 3 level “find”:

ExecuteSQL(“SELECT S.id FROM SSN s, PGM p, PTI t
WHERE s.id_PGM = p.id
AND  p.id_PTI = t.id
AND LOWER(t.title) = s.FILTERINPUT ” ; “” ; “” )

Performance

BUT (and it’s a big, disappointing BUT), ExecuteSQL can be very slow.  In the last example above, looking for matching text in the PTI table, the calc takes about 6-8 seconds to query a set of about 24,000 records.  That may not be a big deal when running a report, or an off-line process, but on an interactive screen (I was trying to use it for a “spotlight search” function), it’s clearly not good enough.  In this situation, it looks like you’re better off “denormalising” the data (i.e. repeating, in the parent table, the field you want to query).


Virtual Lists – “Let’s start at the very beginning…”

“Virtual List” in Filemaker is not really a technique, but a concept.  And it’s one that, although tricky to understand (at least for me!), is actually a very simple one, albeit with scope for complexity that can be rather scary. So I’m creating a series of steps, starting with this, the most simple of implementations, and working upwards in terms of complexity.

The requirement for this first example is simple: we have a table of People, and we want to produce a listing of each person’s first name.  Obviously this first example does not even need a Virtual List – we could simply have a layout showing the values directly from the table – but diving straight in with a more complex example runs the risk of clouding the simplicity of the concept.  (When I was trying to get my head around this concept, the complexity of available examples was a problem.)

The components required for any use of Virtual List are:

  • A global variable, e.g. $$PEOPLELIST, which will contain all the values for the list, in a return-delimited list.  The return-delimiting of the list means that each value in the variable will have a “value number”, and any value can be referenced using the GetValue function, e.g.

GetValue ($$PEOPLELIST;823)

  • A Virtual List table, pre-populated with the largest number of (blank) records you will ever need – i.e. the largest number of values that will be held in $$PEOPLELIST.  So, for example, if you’re going to use Virtual List for a report based on People, and you have currently 8,000 People records, you should probably create 10,000 blank record in your VL table.  Each record must have a sequential id, starting from 1.  There must be no gaps, and records must not be deleted, i.e. there will always be 10,000 records, numbered 1 through 10000.  (There is another field in the Value List record, “name”, about which more later.)  Obviously, we use a script to populate the table in the first place, like this:

vl_1

  • A script to create and populate the variable.  This will result, for example, in a return-delimited value in $$PEOPLELIST for each record in the People table.  Note that, if the field you’re concerned with has blank values in any records, a blank space needs to be written to the variable.  (We’ve seen that each value in the variable has a “value number”, and this will, in time, equate to the serial number of the record in the table. Hence the need for there to be no gaps.)

vl_2

So far, so very simple.  All we’ve done is write a variable with a bunch of people’s names – no big deal.  But what’s been going on in the Virtual List table while we’ve been doing this?

Well, the vital component to the whole Virtual List concept is a field in the Virtual List table, which I’m calling “name”.  This is an unstored calc field, defined as:

GetValue ( $$PEOPLELIST ; id )

So, into this field we automatically get the contents of the relevant value in the global variable.  The relevant value is obviously the nth value, where “n” is the id of the record, which in turn is the number of the value in the variable.  So if the 823rd person in the original file has a first name of George, because we’ve set the 823rd value in the variable to that name, we automatically have “George” in the name field in the Virtual List table.  Note that the records in the Virtual List table don’t need to be written or updated – the unstored calc is automatically refreshed when the global variable on which it depends is updated.

We can now use our Virtual List table as the source for, perhaps a report, listing people’s names.

At this stage, this may seem a) obvious, and b) pointless – but stick with it!  In future posts, we’ll be looking at how this can be really useful.  For now, consider the possibilities if, instead of just one global variable, we have several.  We may, for example, want to build a reporting table including related values (from other tables) for each person.  That’s when it starts to get really useful.


The Case function in a calculation

A recent post on the TechNet forum, plus a succession of typically helpful answers, provided a very useful tutorial in how best to use the Case function to calculate a marked up cost, with the markup being based on the value of Cost.

Here’s the original formula.  “Why won’t it work?” was the cry for help:

Case (
Cost < “5”  ;   Cost * “4”   ;
Cost > “5.01” and Cost < “15”  ; Cost * “3.5” ;
Cost > “14.99” and Cost < “25”  ;  Cost * “3”  ;
Cost > “24.99” and Cost < “40”  ;  Cost * “2.75” ;
Cost > “39.99” and Cost < “60”  ;  Cost * “2.5” ;
Cost > “59.99” and Cost < “100” ;  Cost * “2.25” ;
Cost > “99.99” and Cost < “200”  ; Cost * “2” ;
Cost > “199”; Cost * “1.6”)

Well, with the field “Cost” defined as a number, and the calculation result defined as a number, the first step is to remove the quotes, which makes Filemaker see the numeric values as numbers, rather than as text.

So, V2:

Case (
Cost < 5 ;   Cost *4   ;
Cost > 5.01 and Cost < 15  ; Cost * 3.5 ;
Cost > 14.99 and Cost < 25  ;  Cost *3  ;
Cost > 24.99 and Cost < 40  ;  Cost * 2.75 ;
Cost > 39.99 and Cost < 60  ;  Cost * 2.5 ;
Cost > 59.99 and Cost < 100 ;  Cost * 2.25 ;
Cost > 99.99 and Cost < 200  ; Cost * 2 ;
Cost > 199; Cost * 1.6)

This will now work, but it’s making much harder work of the calc than it needs to.  Filemaker only works through the Case statement until it meets a true condition, then it bails out – there’s therefore no need for all of these > and < evaluations.

So, enter V3:

Case  (
Cost  <  5  ; Cost * 4   ;
Cost  < 15 ; Cost * 3.5 ;
Cost  < 25 ; Cost * 3   ;
Cost  < 40 ; Cost * 2.75  ;
Cost  < 60 ; Cost * 2.5 ;
Cost  < 100 ; Cost * 2.25  ;
Cost  < 200 ; Cost * 2   ;
Cost * 1.6)

(Note the final  default value, 1.6, which applies to any value of Cost equal to or greater than 200 – no need for an evaluation there; if we get that far, that’s the value to use.)

The next efficiency is to use calculation variables, for the value of Cost, and the markup to be applied.

So, finally, V4:

Let (  [
$Cost = Cost ;
$Markup  = Case  (
$Cost <  5  ;  4 ;
$Cost < 15  ;  3.5 ;
$Cost < 25  ;  3 ;
$Cost < 40  ;  2.75 ;
$Cost < 60  ;  2.5 ;
$Cost < 100  ; 2.25 ;
$Cost < 200  ; 2 ;1.6)];
$Cost * $Markup)

But then came another interesting point, i.e. whether the Cost field itself is the right place for the calculation to happen.  In the case of a “net price” such as this, which may need to be overridden, the better way is to have a separate Markup field, defined as an auto-enter calc, with the initial value of the markup amount calculated as above.  The (new) Price field is then defined as a simple calc field, i.e.  Price = Markup * Cost.  On the Markup fields, the option “Do not replace existing value of field (if any)” should be ticked, to prevent historical and “override” data being replaced.

For a more comprehensive look at calc and auto-enter fields, see here.  Thanks, as ever, to the Technet contributors who combine to make it such a great resource.


Tips for Improving Performance Over WAN

I recently posted a cry for help on the Filemaker TechNet, and received a load of helpful responses about how to improve a solution’s performance over a WAN (wide area network, i.e. via the internet, rather than local).

The full thread is here (login required), but these are the main points offered by the ever-helpful TechNet people, especially Taylor Sharpe, Joshua Ormond, David Jondreau and Mike Mitchell:

Unstored calculation fields

This seems to be the biggest issue in some circumstances.

  • The bad news about unstored calcs.  If a calc has to reference other records (e.g. aggregate fields, such as sum, min, ave, etc.), then all the records that contribute to the calc have to be downloaded and cached.  Aggregating data or referencing a lot of related data inevitably impairs performance, and this becomes especially noticeable in a WAN setup, and even more so when the unstored calcs are used in a Find or a Sort.  And EVEN more so when unstored calcs are used to aggregate data from other unstored calcs in related records – e.g. Sum ( { insert unstored calc here } ).
  • The good news.  It would be unfair on the humble unstored calc to say simply that “unstored calcs are bad” – obviously they’re a brilliant, and essential, feature of FM.  And if an unstored calculation references a global variable, for example, the evaluation occurs instantly.  And unstored calcs only evaluate when they are accessed (displayed, referenced by another calc, sorted on, etc.).

Where unstored calcs are causing performance issues, Joshua Ormond came up with these alternatives:

  • When you need agrregates of related data (count, sum, etc.), use a static field, which can be indexed.  This field is updated when records are added, deleted, or amended.  Joshua calls this a “transaction model”.
  • Execute SQL can also help. (This really does seem to becoming the FM Developer’s new best friend…)

“Big data”

Where possible, split data into manageable chunks.  Because FM retrieves the whole record, rather than just the bits it needs, it makes sense to limit the size of those records.  I’ve found this to be true in my solution, where I once had huge “People” table, whereas I now have it split between “People basic” and “People auxiliary”, the latter containing various calcs etc.  So if I just need the basic People data, that’s all I get.  (The “basic” table is still too big, so I aim to split it further in due course, using a properly normalised attributes table.)

Too many relationships

Where a relationship exists purely to retrieve an occasional piece of data from the related table, consider using “Execute SQL” (FM12 +) instead.  Certainly in my solution, the relationship graph, whilst tightly organised along anchor-buoy lines, will be hugely simplified when I move to FM12 and can use this feature.

Layout efficiency

  • Use blank layouts on file open and file close.  It seems that the first thing FM does when it opens a file is “Show all records”, so specifying a blank layout based on an empty table will certainly help.  Likewise, ensure that this same blank layout is the last layout shown when the file is hosted and closed, as this becomes the default startup layout.
  • When switching to another layout to perform a find, go to Find Mode FIRST, then switch to the other layout, thus preventing the loading of an initial set of records to display.
  • Switching layouts is slow.  Again “Execute SQL” can be used to retrieve data which otherwise may require a layout switch.

Seeing what’s going on

To see how much network traffic FM is handling, analyse the packet data going via Port 5003.  Tools such as Base Elements enable detailed benchmark testing of script performance etc.

ALSO see this article from FileMaker itself – “Optimizing Network Performance for Shared Databases“.  Some useful stuff.


Data Separation – Managing User Accounts

Having split my solution into “Data” and “Interface”, I had to give thought to regulating access to each file, and maintaining user accounts.  Here’s what I’ve ended up with for now – it’s roughly the method explained a while ago by Matt Petrowsky on his magazine site , which I heartily recommend.  He did a whole series of articles about data separation which I found invaluable.

  • Central to the whole user access thing is the User Preferences table (UPF) in the Data file, which has a record for each user, including details of his her account name, privilege set, as well as personal details (email, photo, etc.), various indicators to show his/her access to parts of the system, etc.
  • The Interface file is wide open.  In File Options, I’ve set “Log in using…” with a shared username and password, i.e. anybody with access to the server on which the file is hosted can open it.  (There is, of course, one other account, i.e. the admin account, which can be used via shift/option+click.)
  • When the Interface file is opened, the Data file is also opened, as it’s listed as an external data source.  Needless to say, this doesn’t have a generic user account, so at this point the user is prompted for a username and password.
  • When the user provides valid credentials and the file is opened, a calc field is set in the UPF table, i.e. UPF_c_Account, defined as simple “Get ( AccountName )”.  (There’s another calc field, UPF_c_PrivSet, which holds the user’s Privilege Set – more about that later.)   Nothing else happens in Data for now – we don’t open a window, so the Startup script doesn’t run yet.
  • Next, back in the Interface, we go to a “startup” layout, which necessitates opening a window, which in turn kicks off the “startup” script, as defined in File Options.  This is what the script does:
  1. If  this is a Full Access user (i.e. the Interface file has been opened with the Admin user account), install appropriate menu set, show toolbars, etc.  Otherwise (i.e. it’s been “auto-opened” with the generic account), install minimal menus, hide toolbars, etc.
  2. Perform the “Verify user details” script, which does this:
  3. Take UPF_c_Account (which contains the current user’s account), and do a find in the “User Preferences” table – i.e. set UPF_AccountName to the value held in UPF_c_Account, the calc field set when the file opens.  If there’s no match (unlikely, given that they must have entered a valid username and password to get this far), display a “No Access” message and close (see screenshot below).  If there IS a match, then set all the required globals, variables, etc., display the welcome screen, then the main menu.

It all works pretty well, although, as ever, I look forward to being told there’s a better way to skin the cat!


The Let Function

I’ve rather avoided the Let function until now – until you use it, it seems to add a layer of complexity, and, as a novice, I’ve always liked keeping things simple.

But now I’m beginning to realise that simplicity is exactly what the Let statement offers.  Not only does it enable you to make calculations more readable, but it greatly increases the efficiency of the calculation.

Here’s a good example.

Say I have a field called BKG::BKG_Cost, which is the cost of a Booking.  The cost of the Booking is the total of the costs of the Resource Bookings within the Booking.  The cost depends on whether the date of the first Session has passed – if the deadline has passed, we increase the cost by 15%.

So, one way of defining the field would be like this:

But if we use a Let statement to define the total booking cost, we end up with this:

The main gain here is that we reference the field PGM to SSN to RBK::RBK_Cost only once, and do the Sum only once, i.e. when defining the calculation variable “BookingTot”.  In the first version, to apply the increase, we do that twice.  Using the calculation variable also makes the whole thing more readable.  And the more variables you use, the more readable it becomes.  Here is is again, with a couple more variables (note the need, when defining more than one variable, to enclose the variable definitions in square brackets):