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!

Advertisements

25 Comments on “Using eSQL to filter a “QuickFind” portal”

  1. Gale says:

    I am having difficulty finding the download for the sample, and have tried searching for ‘download’ and a number of other things on this page in order to locate it. Can you please be explicit about the ‘box’ on the right? The only box I’ve noticed is the search text box. Thanks. Otherwise, I do find your postings helpful.

    • Dave says:

      Hello. There should be a “widget” on the right of your screen entitled “Demo Files”, immediately below the Search box, showing a list of zipped demo files, including “QuickFindDemo.zip”. If you’re not seeing that, I’m very happy to email you the demo file.
      Let me know,
      Dave.

  2. Henry says:

    What would be an easy/proper way to show all records when nothing has been entered into the filterinput field?

    • Dave says:

      Hello Henry,

      I would avoid showing all records automatically when there’s no input – with a large dataset, this will inevitably slow things down, e.g. when user backspaces over the input field. Instead, I’d probably have a “Show all” button, next to the input field. The button would just do a Set Field of FILTERIDS, with calculated result ExecuteSQL(“SELECT id FROM PPL ” ; “” ; “” ), i.e. just putting all key values into the match field (but only when the user really wants to do so).
      Hope that helps – but please let me know if you see a better way!
      Dave.

      • Henry says:

        Thanks Dave, sounds workable.

        I’m thinking about a solution that would work without adding a query in another location, rather works with the existing queries in the search script, that way you’d only have to enrich the script. I noticed a blank space also works, is that reliable as well? Or only if there is a space in searchable data?

        If so a button which merely sets the input field to a blank space would already do (or some other value the will work with mysql queries or with an adjusted script.

        That way adding a query also to the ‘Show all” button would not be necessary. Copying the “Show all” functionality to multiple locations would then be a 15 second job: copy-paste + adjusting the set field target (to the proper input field) and less locations to change queries when tables/field names change.

        Love to hear what you think would work with the (adjusted) script,

        Henry

  3. Bart says:

    Hello dave,

    I dont see the box field under the search box. On onther pc is say not respnonding box.net. Can you send it to me?

    Bart

  4. Henry says:

    Using this very often now, so thanks! Trying to extend this to multiple input fields, to make it search on two or more criteria. Have you already done this perhaps?

    Thinks out loud: I might try to do this by keeping just one field for the trigger (like it already is) and use the second field only in the excecutesql search query

  5. Chase Holden says:

    What a fantastic article, Dave!
    Unofrunately, neither was I able to download or even see the “box” near the search field to download the file. I have reloaded this page in Safari, Chrome and FIrefox with no success.
    I’d be happy to host the file for you if you could please send it to me!
    Thanks again,
    Chase Holden

    • Dave says:

      Hi Chase. Thanks for the feedback. I’m sorry, but I’m away for the weekend, but will send the demo file on Sunday evening.
      Dave.

  6. José says:

    Hello
    I can’t find te zip file.
    Can you please send to me
    José
    Thanks

  7. José says:

    Hello,
    i Cant find the demo, Can you send it to me?
    thanks

  8. José says:

    Hello,
    I can’t neither find the zip file.

  9. Mike J says:

    Hey there, I cannot find the box either…

  10. Mike J says:

    Thanks a lot for sharing this. Together with an OnTimer script trigger this method allows an efficient way of implementing a portal search.

  11. Mark Wilhelm says:

    can’t find the box either!

    it sounds great! just what i need in a module i am building! could you either show me why my ‘box’ on the right isn’t showing or email me the demo file?

    moochos gracias!

    Mark

  12. xxlerate says:

    Looking for the download as well. Thank you!

  13. Hi, Great article, but can you send me the file as well please as I can’t seem to find the link either..

    Carl

  14. stam66 says:

    Great post and one I may well use – as with many others above however, no widget/box with a download link appears in either Safari or Firefox. Would you be able to send me a download link please?

    • Dave says:

      Hi. Glad it could be useful. Demo file is coming to you via email. (I must find a download thing that actually works!)
      Dave.

  15. Eric Werner says:

    Dave, could you please send the demo file via email? Thank you!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s