Video script

As of v14, the query editor has been simplified so that you can perform queries with greater ease and power, whether they are simple queries, queries by formula, or compound queries.

Let’s take a look at its features and improvements with respect to previous versions. The window is still divided into 3 parts:

•    An area where you choose a field that you want to search
•    A comparison operator
•    And the value area.

Let’s search for all the interventions concerning software. You just need to type the first few letters of the table concerned and then indicate the field, the search criteria and for what value you want to search. If you want to do a more complete query, for instance for all the “software” interventions performed by a certain technician, the combination is done by adding a new search criterion which is added by default with the AND operator.

Now we’re going to query the technicians to search for those where the name matches “Lannes”. When we launch the query, we find all the records that were performed by the technician named “Lannes”.

If we want to find all interventions that started between 11:00 a.m. and 1:00 p.m., we’re going to erase the criteria and then query on [Interventions] the intervention time included between 11:00:00 and 13:00:00. If we sort the interventions by time, we get from 11:00 a.m. to about 1:00 p.m.

Now let’s say we want to get all interventions that were performed in the month of June. In this case, we’ll need to do a query by formula. If we hold down the ALT key and click on the "+" button, this adds a query by formula line. Then we can delete the 1st line and just do a query by formula.

We’re going to perform a query on the intervention date where the month must equal “6”.

Here we have different operators for strings, numbers, dates, and so on, and here are the functions available that can be used, for instance, on dates and times for requesting the month. When we perform the query, we get all the months that are “June”.

You can now combine certain elements in the query editor and more particularly, you can retrieve a recent query (your last ten queries are kept). So if we want to get all interventions performed in the month of June between 11:00 a.m. and 1:00 p.m., we can retrieve this selection and then we can request a Search in selection, and when we do this query, we’re searching in the interventions performed in June whose time is between 11:00 a.m. and 1:00 p.m. By performing the same sort on the intervention time as we did earlier, we now have interventions that were performed in June between 11:00 a.m. and around 1:00 p.m.

Now as you can see when we display the query, it is also possible to restrict or combine it by requesting that this selection be added or removed from the next query. So we have 149 records matching our previous criteria and now we can choose to either add the corresponding selection to these criteria, or to remove them from it. If we want to remove every intervention that involves “software” performed by “Lannes” from this selection, we see that we go from 149 to 146 interventions.

You can also load or save formulas so that you can use them again later on. Resetting lets you erase all the criteria and return to the defaults.

When you are working on the current table, you can use this button here to select the field where you want to perform the query.

Naturally query or comparison criteria are adapted according to the type of field concerned. So if we are on a text field, we’ll have “contains word(s)” and if we perform a query on a date, we’ll have “is tomorrow” so you can see that the choices correspond to the type of field.

As we planned earlier, it is possible to combine a query by formula with a regular query. So we’ll have this query by formula for the month of June using, for instance, everything that concerns the network. The subject is “network” and therefore, it's now possible to do a combined query: “normal query combined with a query by formula,” as well as adding to or removing from the selection.