Layout tip – Opening a Popover with a Script

I really like popovers – they’re the most useful interface feature to have been introduced to Filemaker for ages, and being able to add an OnObjectEnter script trigger adds to their usefulness, enabling us to do some stuff each time the popover opens.

But OnObjectEnter is a “Post” trigger (i.e. it runs after the event has happened), and sometimes it’s nicer to do the processing before the popover opens.  Initialising fields is a good example, because we don’t want the existing (“old”) field contents displayed, however briefly, before the fields are cleared.

One way around this is to have:

  1. An ordinary (non-popover) button, which is the button visible to the user, and which the user clicks.
  2. A popover button, hidden from the user.  (To make it hidden, I use the method suggested by ‘bfroo’ in the comments – ‘hide object when 1’ – so that it’s always hidden in Browse mode, but visible in Layout mode.)
  3. An object name for the popover itself.

I tend to put the popover button next to the other button, but different in colour to make it obvious that it’s not a part of the user’s view of the interface, like this:

popover_buttons

(i.e. the ‘Analyse’ and ‘Outcomes’ buttons are the clickable buttons, and the little yellow ones next to them are the popover buttons.)

When the user clicks on the (non-popover) button, a script is performed.  This script:

  1. Performs any pre-processing required,
  2. Goes to the object name of the popover, which has the effect of opening the popover.
  3. Refreshes the window.

E.g.

goToObject_popover

The disadvantage is that it’s obviously a bit more work to set it up in the first place, but the added flexibility that this technique provides, makes it well worthwhile.

Advertisements

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.


Using eSQL to filter a “QuickFind” portal

(Note – this is an update of an older post about filtering portals via relationships.)

The Requirement: A quick, user-friendly “spotlight” style search option, to filter a portal of people’s names.

Demo file: QuickFindDemo.zip – downloadable from the “Box” over on the right.

The Interface

Here’s how the finished interface looks:

qf1

Using the FM’s nice “River” theme, we have a QuickFind box in which the user enters a string, and, as they type, the list expands or contracts to show matching records.  (Note that the “Search” button in the screenshot is not part of this technique – that opens a popover in which the user can specify a number of other criteria – show only people over a certain age, who have attended this month, etc.)

The input field is a global field within the table to be searched.  In this example, it’s called PPL::FILTERINPUT.

When this field is empty, it looks like this:

qf2

The magnifying glass icon and the “QuickFind” text are hidden, via the very useful “hide object when” feature, the condition here being “not IsEmpty (PPL::FILTERINPUT)”.

The Relationship

The portal showing the list of people’s names, plus other information as required, is based on a self-join.  I’ve called the table occurrence PPL_PPL~filter with the self-join relationship defined as:

qf3

In the PPL table, “id” is the primary key.  So the portal will show anybody whose id appears in the global field PPL::FILTERIDS.  (We can ignore the “isDead” thing, which just excludes dead people from the list.)

So, how does PPL::FILTERIDS get populated?

Populating FILTERIDS

The population of the match field involves two scripts, “Initiate filter” and “Filter portal”.  The second one, which does the searching, ONLY runs after a given pause between the user’s keystrokes.  This is important, because you don’t want the searching script to run every time the user presses a key – it would be too slow.  The duration of the delay that you allow depends mainly on the typing speed of the user – I’ve settled on 0.35 seconds.

So, the first script, “Initiate Filter”, runs EVERY time the user presses a key – it’s an OnObjectModify triggered script attached to PPL::FILTERINPUT.  This is what it does:

qf4The script is passed two parameters, one is the name of the field that we want to populate (i.e. PPL::FILTERIDS), and the other is the object name (not the field name) of the input field – I’ve called mine “PeopleNameFilter”.

These two parameters are stored as global variables, for use in the subsequent script. (Note that I use the GSP custom function to “get” these parameter values – it’s available here on Brian Dunning’s site).

We then use “Install OnTimer Script” to schedule the searching script every 0.35 seconds – this has the effect of allowing the user that amount of time to press another key – if s/he modifies the field again within 0.35 seconds,  the script runs again, and the new OnTimer Script replaces the previous one.

When the user has finished typing (i.e. 0.35 seconds has elapsed), the “Filter Portal” script runs.  It looks like this:

qf6

If the input field is empty, we just initialise the multi-key field.  This will have the effect of showing no records in the portal.  We then use “Install OnTimer Script” again, with a zero interval, to cancel the repetition of the current script.

If we have an input string, we use a fairly straightforward ExecuteSQL statement to find the matching ids.  Here’s the eSQL statement:

ExecuteSQL(“SELECT id FROM PPL WHERE searchableData LIKE ? ” ; “” ; “” ; “%” & PPL::FILTERINPUT & “%” )

PPL::searchableData, the field searched by the ExecuteSQL statement, is a concatenated text field, including whatever pieces of data you want to search on.  (I’ve experimented with making this a calculated field, but have found it much more efficient to have it as a plain text field, updated by a script whenever relevant data changes.)  In this case, the field is updated as:

lower(nameFirst & “ “ & nameLast & “ “ & address1)

So, at the end of all this, we have PPL::FILTERIDS populated with the primary keys of all records where matching text has been found (anywhere within the searchableData field – it doesn’t have to be at the beginning), and the portal showing those records.  If there are NO matching records, we say so, then cancel the OnTimer script (by installing another (blank) one), and go back to where we came from.

Note that the use of parameters is optional, but makes it possible to use it elsewhere, should we need other similar QuickFinds.

As ever, please comment if you can suggest a better way of doing this.  None of it is really of my own invention, and is documented here to remind myself of how it works, but if it’s of use to you, so much the better!


Mutually exclusive Booleans

The requirement:  A set of mutually exclusive fields, to be stored as Booleans (i.e. 1 or 0, on or off).

Note – There may be several reasons why these must be separate Boolean fields, rather than one non-Boolean (which would obviously be easier to implement) – totalling and reporting, for example, can be  much more straightforward with Booleans.

In a simple example, we have 3 fields, isRed, isGreen, and isBlue. If any one of them is set to 1, the other two must be set to zero.

This is how I do this, following advice and tips from Technet members and others.

First of all, each field is shown as a checkbox set, using values from a value list called “true”. This VL actually only has one value in it, i.e. “1”. The field is sized on the layout to show only a single checkbox. Like this:

rgb_layout

Then, each field is defined as a number field, with two auto-enter settings:

  • Data – 0 or 1 (i.e. this determines the default setting),
  • Calculated value – as shown below:

rgb1

So, basically, this calc says:

  • If the field currently being updated is isRed, set isRed to whatever the user has selected, i.e. 1 or not. But, because we want to store a zero to indicate the “off” state, use the GetAsBoolean function to achieve that.
  • If the field currently being updated is isGreen, and if isGreen is “on”, set isRed to “off”.
  • If the field currently being updated is isBlue, and if isBlue is “on”, set isRed to “off”.

Each of the other two fields have a similar auto-enter calc applied to them, like this:

rgb2

rgb3
Note that the confusingly-labelled setting “Do not replace existing value of field (if any)” is set to OFF.
There’s another post on this blog advocating the use of conditionally-formatted objects to indicate on/off states of Booleans, but I’m now beginning to prefer this approach.  As ever, if you know of a better way to skin the cat, do let me know!


How to go to a specific portal row

The requirement:   In a portal, you need to be able to highlight a given record, and ensure that the portal row is visible.  (For example, a portal contains 20 records, but displays only 3.  You know which record you’re interested in, and you have the id of the record stashed in a global field, but the record may be out of sight when you display the portal.)

I devised a functional, but clunky, way of achieving this, then asked for advice on the Filemaker Technet.  I got 2 really good suggestions.  The first, from David Jondreau, suggested this:

portal_row_highlight1

So, the portal shows sessions (SSN) within a programme (PGM), and the id of the session that we’re interested in is held in the global field PGM::CURRENTSESSIONID.  The loop just goes through the portal rows until it hits that record.  “Go to portal row” ensures that it’s highlighted and visible.

The second suggestion, from Mike Beargie, uses a custom function called “ListIndex” (available from Brian Dunning’s site).  It’s a really concise one-liner:

portal_row_highlight2

The ListIndex CF just finds the position of the global id within the list of related SSN rec ids, and goes to that portal row, all in one script step.  Really neat, but, as Mike pointed out, it relies on the portal not being filtered.


Filtered Value Lists, with “All” Option

Demo file: filtered_VL_with_all.zip – – downloadable from the “Box” over on the right.

Filtered value lists are great, and very useful when it comes to improving the user interface.  In this food-based example, we have 2 tables, “Courses” and “Items”.  Food Items belong to a Course (e.g. Soup is a Starter, Steak is a Main course, Trifle is a Desert, etc.).

rel_vl1rel_vl2So when the user selects a Course, the Items menu (pop-up) displays all Items belonging to that Course.

This is achieved by a global field, COURSEID, with a relationship from this field to the course_id field in the Items table.  We then have a value list defined as follows, i.e. including only related values in Items from the context of the Globals table.  Standard stuff, well documented elsewhere, and it works fine, as long as the user has selected a Course.

rel_vl4

But we often have to deal with exceptions.  What if, for example, we have Items which don’t belong to a course (such as coffee or canapes)?  Or we want to allow the user to skip the Course selection and go straight to the Items list, where s/he expects to see a list of ALL items available?

Well, there are probably many ways to achieve this, but I’ve found it quite difficult to make it really user-friendly.  For instance, one book I read recently suggested having separate pop-up menu for “All Items”, using a value list based on a cartesian join, which would obviously work, but strikes me as potentially confusing to the users, sending them to 2 different places to make the same selection.

In my solution, the principle is the same, i.e. if the user doesn’t select a Course, we want to offer ALL Items, but let’s keep it all in the same pop-up.  To do so, base the relationship from Globals to Items NOT on the COURSEID global, but on a second global field, COURSESELECTOR.  This is an auto-enter calc, defined as:

If ( IsEmpty (COURSEID); ValueListItems ( Get ( FileName ) ; “AllCourses” ) ; COURSEID )

(making sure that “Do not replace existing value of field” is UNchecked).

What the auto-enter is saying is “if no Course has been selected, put all Course ids in the relationship field, as a return-separated list. But if a Course has been selected, just put that id in the field”.  So we’re using the global field either as a multi-key, or as a simple key, and when it’s a multi-key, the result will be that all Items appear in the “Items” value list.

rel_vl3There’s one important proviso though, concerning Items that don’t belong to a Course.  As it stands, these won’t appear in the “Items” value list (because that list includes only those Items which have a “parent” Course record.  So we must create a dummy Course record, with a blank name (or even named “Misc”, “Other items”, etc.), and all Items which don’t have a natural foreign key value must be given this one.  This will force the Items to appear.

As ever, there may be better way to skin this cat – if so, please let us know in a Comment.

 


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