[Article] A Multiple Find Technique

By Michael Rochard
http://www.michaelrochard.com/

In the following article, we are going to describe a technique for performing multiple finds from a single data entry field. This technique requires the user to do nothing more than type several words or parts of words in a field and have any records that match those entries found as soon as the field is exited. For the purposes of this article, we are going to search on customerids and will be using a customer detail form and a customer list.

In this technique, we are going to use a global field on a custom controller to make multiple selections and perform a find on all of the selections. OK, I can already hear the question! What is a custom controller and how is it different from a regular controller?

Forms, in Servoy, are, by default, set to use a controller, which is very similar to the FileMaker status area with a rolodex type object, on the left side of the screen, which allows the user to navigate from one record to another.. While functional, it is pretty ugly and I suspect that most developers will choose not to use it. Fortunately, Servoy gives us the option to use a custom controller instead of the default one. A custom controller is nothing more than a form that can contain buttons, fields and other objects and which will appear on the left side of any form that is set to use it. Think of a controller as a remote control; only limited by your imagination.

Before we add our global field to a new form, which will be used as a custom controller, we must first define the field. To do so, we have to be in Designer mode so click on the little icon to the right of the New Form icon.

Go into Dataproviders by clicking on the Dataproviders icon

click on the Globals tab and then on the New button. Name the global dataprovider Search_Field, choose the type, which for the sake of this example, we will make Text and then click on OK to exit Dataproviders.

Before we go any further, I suppose we should talk about Dataproviders, more specifically what they are. Dataproviders manage information; columns are one type of Dataprovider; other types are globals, calculations and aggregates. The dataprovider manages the data; the field element displays that data (and may enable you to act on that data, such as in editing or scripting.

Now let's create a new form. Click on the New Form icon on the far left of the tool panel

We will call this form 'MultiFind' and create a new table with the same name.

Click OK, the form will be created and you will find yourself back in the Dataproviders window since Servoy now expects you to add columns/dataproviders to the new table.

However, since we do not need to do so, click OK to exit. This will immediately take you to the list of fields

Select the global field search_field from the list of fields

Click OK.

Now click anywhere on the Multifind form (except for on the field itself) to bring up the Properties window for the form; (if we clicked on the field, it would bring up the properties for the field, which we don't want).

Uncheck Show in Menu

Set Controller to None

The next thing we have to do is to go to the form that contains the data that we want to search on; this form can either be a record or a list view and it doesn't even have to display the field you are going to search on. For the purposes of this article, however, we are going to use a record view since we want to go to a list view after the search has been performed.

On the left side of the screen (in the pale blue so that it stands out) is the custom controller with the search field on it and on the right is the customer detail. The customer detail form that we are going to search on has already been created and has several hundred records as a sampling.

If we go back into Designer mode and click, in an empty area, on the Customers form, the Properties panel will open up and you will see that the controller for this form has been set to the Multifind form.

The next thing is to create the method that is going to perform the search and, since the search itself takes place in the Customers table, that is where the method is going to be created.

The method looks like this:

Which, quite possibly, looks very confusing but is really very simple. In JavaScript, indenting is very important since it makes the script much easier to read than if it was just all aligned to the left side. Like most new things, this takes some getting used to.

The first line is, quite simply: controller.find. Remembering that we are creating the method in the Customers table, this can be inserted into the method by clicking on the 'key' icon to the left of the table name, selecting the first option (Controller) and then double-clicking on 'Find' in the bottom section which will move the code into the editor. Alternatively you can click on 'Find' and Ctrl M to move it or you can click on it and then on the 'Move Code' icon or you can just type controller.find. Multiple ways of doing the same thing and all of them totally correct! Isn't this fun?

Shown below is the Method Editor window. The left hand side is divided into two panes with the top half containing all the different tables and the items that relate to them. Clicking on an item in the this pane will reveal all the options associated with that item and the bottom section is where you move snippets that you want into the main window. (While this takes a little getting used to, once you have done so, you will find it a very easy and powerful tool to build methods).

But what does it all mean? controller.find simply creates a find request.

The next step in the method is to convert the search field (our global field that sits on the custom controller) into an array. Holy heck, what's an array? According to the dictionary, it is 'an arrangement of items of computerized data in tabular form for easy reference." And, if that doesn't make a lot of sense to you, I guarantee that you're not alone but here is my attempt to describe it.

Think of a spreadsheet that has 4 rows of data and 4 columns; if we look at any one row, we are simply looking at a row with 4 columns; conversely if we look at any one column, we are looking at that column with 4 rows of data. If however, we look at the entire spreadsheet we see 4 rows and 4 columns, which makes a total of 16 cells and now we are looking at an array. Mathematically speaking, an array could then multiply the contents of every cell by every cell to come up with a very large number of answers; go on, you do the math; I'm tired!

Another way to describe an array is a list of items; think of a repeating field in FileMaker.

Anyway, moving on, we are going to take the values that we typed into the search field and put those values into an array which, effectively, is going to split them into separate entities. Thus the second line of the method reads:

var searcharray = globals.search_field.split(" ")

which, translated into plain English,

• creates a variable, referred to as var, named searcharray which takes the values from the search field and splits them wherever there is a blank space (indicated by " ").

So if, for example, you had typed AL CE RO in the search field, it would become 3 separate values (AL, CE & RO) in the variable called searcharray. But what would happen if you had typed 8 different words in the search field? It would create 8 separate values or however many you had originally typed; an array has no boundaries or size limitations - it is as big as it needs to be. Although, having said that, there are ways to limit the size of the array but that's another story.

Just to digress for a few seconds, let's say that we wanted to enter the values in our search field with the separator being a carriage return (also referred to as a linefeed) instead of a space. In that case the line becomes:

var searcharray = globals.search_field.split("\n")

The next line sets a variable counter (i) to 0 and then loops through the variable searcharray, incrementing the variable i by 1 as long as i is smaller or equal to the length of the array to the code within the curly braces. Boy, that sounds really confusing, doesn't it!

Let's go back, for a moment, to the definition of an array as a list of items where we want to find some or all of them. If we entered several of those values into a field in find mode, the search would fail, as there would be no records that matched all the values entered. It would be like searching for a pond, a pool and a lake on the same piece of water! Thus, what we want to do is to put those values into an array and have a counter (the variable i) count how many values there are in order that the for loop shown below knows when to exit.

for ( var i = 0 ; i <= searcharray.length ; i++ )

Ok, if you're still with me, now we have to set the value of the field that we are searching on with the values from the array and since, in this example, we are searching on the field customerid, we open up a block statement which, in JavaScript, uses curly braces {} to 'group a list of statements together'.

{
customerid = searcharray[i]
}

The block statement above is very simple; it just sets the field customerid (don't forget that we are in find mode) to the value of the variable searcharray. The one problem with this statement is that it will only find exact matches and we may not have entered sufficient data in the search field to perform a successful search. So let's modify the statement slightly:

{
customerid = "#%" + searcharray[i] + "%"
}

What we have done here is made the search case-insensitive by using # and, by using the % sign at the beginning and end of the statement, we are telling SQL that this search contains the contents of the search field. Thus, "#%" at the beginning of the statement and "%" at the end, tells SQL that the search is case-insensitive and that any word that contains any of the letters from the search field should be found. (The + signs are the SQL equivalent of the & in FileMaker). By the way, there are many times when you will want to search for words that begin with the letters entered and not those that contain them. In this case, all you do is leave out the % sign at the beginning, i.e.,

customerid = "#" + searcharray[i] + "%"

The last part of the method is:

Controller.search()

And although there are no prizes for guessing what this means, it means perform the find which means, in turn, that we're done too. Right? Well, not quite because there is one more thing that we can add to the mix to make this method a little bit more useful than it already is.

The last thing we are going to add is a new block statement that comes after controller.search and what this does is to determine how to display the records that are found. If the search only finds a single record, then we want it to display that record in detail view; however, if it finds multiple records, we want to display the found set as a list so that the user can view all of the records at one time.

The statement is:

if(controller.getMaxRecordIndex() > 1)

{
forms.customers_list.controller.showRecords(foundset)
}

Which does nothing more than say if more than one record is found, go to the form called customers_list and show the foundset.

Multiple Records Found

If this condition is not met, then it will stay on the same form as it started - there is no need to specify this.

A Single Record Found

That's it; a very simple, but effective, technique that you can use and adapt in a myriad of ways.

Have fun out there in the Servoy world.