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

Advertisements

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.


“Tidiest Drop-Downs” – Part 2, Finishing Touches

In the previous post, I documented how I plan to use a combination of drop-downs and pop-ups to get the best of both worlds, in terms of user-friendliness.  I’ll call these “tidiest drop-downs”.

But there’s one problem with the technique, which is this: because the key field is the one that the user actually enters data into, it’s possible for the user also to enter invalid data.  The intention is to limit the selection to items that are in the value list, but if the user double-clicks in the field and starts typing, invalid data may be left in the field.  (Of course the user may, by chance, enter a valid key value – just as a chimpanzee, left to its own devices, may eventually type the Complete Works of Shakespeare.  But it’s unlikely, to say the least.  And I’m not for a minute implying that my users are in any way like chimps…)  So anyway, we need to use a triggered script to make sure that this doesn’t happen.

On the key field, I’ll add an “OnObjectModify” script.  This will run either when the user selects a key value from the drop-down list, or when s/he types in the field.  The pseudocode for the script is:

Check whether the field holds a value that exists in the value list.
If so, do nothing.
If not, display an error message and offer the drop-down list again.

A couple of pre-requisites for this script;

  1. It has to be as portable as possible.  (I don’t want to have to write a new iteration of the script for every drop-down in the system.)
  2. Once the script has detected invalid data in the field, I want to throw the user back into the field, complete with drop-down list displayed (without the user having to click into the field again).

So this is how it turned out in FileMaker:

valid_dd_scriptNote that the script uses two custom functions, both of which can be found on Brian Dunning’s site.

  1. “GSP” – which gets the specified parameter from the parameters passed to the script.
  2. “FilterList” – which checks for the presence of a value in a given list.  (It does a lot more than this if you want it to, but that’s what it does here.)

Note also that you need a “dummy” field to go to, either during or after validation.  I have the dummy field (a global) parked just off the displayed area of each layout in the system – it often comes in handy.

So, the first parameter is a comma-separated list of all the (key) values in the value list that is attached to the field. (They have to be comma-separated, rather than cr-separated, so that the list will be treated as one parameter.)

The second parameter is the OBJECT NAME (not field name) of the field being validated.  We need this in order to get back to the field if validation fails.  So, for the script to work, you must give the field an object name.

So the script checks whether the contents of the active field exists in the list of values.  If it does, fine – just go to the dummy field, and end validation.

If the value doesn’t exist, then something’s obviously wrong, so show the custom dialog, initialise the field, then go back to the field in question.  Note the need to go to the dummy field first – if you don’t (i.e. if you go straight to the field being validated), the drop-down list will not be displayed – you haven’t actually left that field yet, hence the need for the little round-trip.

Note also the need to use “Go to object” rather than “Go to field”.  Although we can pass the field name as a parameter to the script, we can’t use that information once we get here, because FileMaker haven’t yet come up with a “Go To Field By Name” script step.

And that’s it.  A nicely portable validation script to help keep the solution free of invalid data.


“Tidiest Drop-Downs” – Part 1, The Best Compromise?

There’s no perfect solution when it comes to enabling users to select an item from a value list.   Usually it comes down to a choice between a drop-down list,or a pop-up menu.  These are similar, but crucially different.  These are the pros and cons of each, as I see it.

POP-UP MENU

Pros:

  • Easy to implement,
  • Easy to use – IF there are only a few items in the value list,
  • If the Value List is made up of 2 fields, it’s easy to show the resulting text value (field 2) but store the key value (field 1).

Cons:

  • No type-ahead functionality, at least on Windows – so, to go from A to Z, user has to scroll,
  • The menu will fill the available space – this can look really ugly.

DROP-DOWN LIST

Pros:

  • Look tidy, as only some values are displayed (about 15?),
  • User can type one or more characters to go the required part of the list.

Cons:

  • If the Value List is made up of 2 fields, it’s not possible to say “show the text value, but store the key value” (as you can with the pop-up).

Until now, I’ve gone for the tried and tested technique of using a drop-down menu to display the available values, with another field, holding the key value, UNDERNEATH the displayed field showing the text value.  (I documented the technique here.)  By setting the hidden key field to allow data entry, and setting the text field NOT to allow data entry, you get the best of both worlds … to a point.  One shortcoming of this technique is that, if you want the user to see an arrowhead as an indication that this is a drop-down, you have to make the text field on top a bit less wide than the key field beneath in order to show Filemaker’s native arrowhead.  In the wonderful world of FM12’s layout themes, this all gets a bit a bit messy, and has always been fiddly.

I’ve now realised there’s a better option, and this is the approach that I plan to use in future (i.e. until something better turns up!):

  1. Define the key field “underneath”, based, as before, on the 2-field value list.  As before, this is a DROP-DOWN menu, allowing field entry (in Browse and/or Find modes, according to the circumstances), but don’t enable the “arrow to show and hide list”.
  2. Add the same field on top of the key field.  This one, however, is a POP-UP list, with NO field entry.  The 2 stacked fields should be exactly the same size.

The result is that the text value is displayed, thanks to the behaviour of the pop-up menu, whilst the type-ahead selection behaviour of the drop-down list underneath makes things more user-friendly.  The added bonus is that the arrowhead of the “on top” pop-up menu appears to belong to the drop-down menu, completing the “best of both worlds” solution.

Note, however, there’s still a drawback with this, and it’s a big one – the user is able to double-click in the key field, potentially typing an invalid key value in the “underneath field” – more about this in the next post.


Copy All Records – A Real Bonus

Sometimes, discovering a simple technique in Filemaker makes me wonder how I’ve lived without it until now.  The “Copy All Records” script step is one that I’ve never used… until now.  Now that I know what it does, it’ll quickly become a favourite.

In a Found Set, Copy All Records does just that, i.e. copies the contents of all records in the Found Set into the clipboard – or at least the contents of all fields on the currently displayed layout.  So if, before doing Copy All Records, you go to a layout on which you have only the primary key field displayed, doing Copy All Records copies just the keys of the records in the current Found Set.  And these keys are nicely formatted as a return-separated list.

This list can then be pasted into, say, a global field, which you can then use as a multi-key in a self-join relationship.   You may, for example, want to use this later on to retrive the Found Set that you were working on earlier, by using a GTRR based on the global multi-key.

Very tidy and efficient.


Sorting in List View

In the previous article, I documented a tidy and portable way to sort portals by clicking on column labels.  In the interests of consistency (the user doesn’t know or care whether s/he’s looking at a portal or a list), it makes sense to have the same functionality on List View layouts – i.e.

  • click on the column label to do the sort,
  • reverse the sort direction if user clicks ion the same column twice,
  • and have a little arrow-head to indicate which column is sorted, and in which direction.

To do this in FOCUS, we have a couple of scripts, performed by a controlling script, i.e. “Sort a list”.  So when user clicks on a column heading, the button performs “Sort a list”, with the field name passed as a parameter.

Set list sort direction” basically takes the parameter onwards, and compares it with how the last list sort was done.  If that was on the same field, we reverse the direction, using a Boolean global variable.  Note the use of the “Abs” function to set the direction to “1” after the toggling may have set it to “-1”.

Perform list sort” does the actual sort, based on the context and direction we’ve set.  Note that there’s a pair of “sort” script steps for each clickable column, one ascending and one descending. Note also that this script deals with all clickable columns on all relevant list layouts.

On the layout itself, we just need conditionally formatted arrowheads to show on which field the list is sorted, and in which direction.


Ditching Finds and Using GTRR Instead

In the past, when selecting items from a list in order to do, say, a mail merge or some other sort of bulk processing, I’ve used a clunky combination of “select” indicators on each record, followed by a Find finding all recs with the selection indicator set), then a loop through the found set.

Having realised the error of my ways, I now use the technique described in “Selecting Items from a List“, which basically puts the primary key of each selected item in a global variable (say “$$SelectedPeople”), using a neat custom function to toggle between selection and de-selection.

Once the global variable is set, getting the records for bulk processing is embarrassingly simple – just bung the contents of the variable into a global “match” field on the table you’re interested in, and do a Go To Related Record.

E.g.

Within the GTRR script step, having checked that there are people selected, you can specify:

  • the layout to be used (in this case a letter template for mailmerging),
  • whether to use a new window,
  • which records to show – in this case, of course, only related records.

And that’s it.  One script step to replace a clumsy sequence.