Query by formula, applying a formula

In addition to the manipulations explained in the video, you should also note that:

By applying a formula, you can pass a field in all caps or lower case, concatenate character strings or divide them (if this processing was not planned before the import or if your forms do not specify the automatic conversions and controls).

All the 4D language commands are at our disposal in the Formula editor (a limited version is available to end users when they access the standard editor).

Using formulas lets you perform the equivalent of a high level search/replace. Since you use 4D language commands, the more familiar you are with them, the better you can handle the finer points of the operation.

Let's look at some examples:

• In the file that you imported into a Users table, you forgot to capitalize first names and the last names are mixed (some capitalized, others not)
• Moreover, phone numbers are not all formatted the same way (some have spaces, some have dashes, others don't have either, sometimes the leading zero is not imported because it comes from a spreadsheet that wasn't properly formatted during the export, and so on).

We need to reprocess data that is incorrect or improperly formatted.

4D works with the concept of a current selection; in other words, a list of records whose numbers are kept in memory and that serve as the basis for all processing (except in rare cases).

Tip: When you apply a formula, first test it on a few records to check whether it is valid and matches your expectations.

Example of sorting by formula:
The clearest example is usually sorting a dictionary which, by definition, is already sorted by alphabetical order and therefore does not need to be sorted using a formula.

However, what about a Scrabble dictionary? First we need to:

• sort names by their length, in number of characters (1-character words, then 2-, then 3- and so on)
• then, within these groups, sort by alphabetical order.

In this case, you need to use a sort by formula for the first sort.

In addition:
You can also apply a method (containing several lines of code) to the selection.

Actually, if you need to apply several formulas to the same selection, you can:

• Write the formulas to apply within a method
• Then indicate the name of this method in the line of formula to apply:

Formulas can also be used in columns of the Quick report editor whose functioning was discussed in previous chapters. This gives you a virtually unlimited number of combinations for producing your reports, exports and processing.

Today, we're going to learn how to perform advanced queries and to modify data en masse and sort them according to a calculation formula.
This lets you refine queries, sorts, quick reports and so on, but also lets you modify the values of a selection of records by "applying a formula".

Let's look at an example with the interventions.

First, we want to extract all the interventions that took place in the month of December regardless of the year.

Since we don't have a "Month" column, a conventional search is not possible because we have to extract the month from the date itself.

In the "Records" menu:

• Choose "Query"
• then "Query by Formula..."

The editor has 3 columns:

• tables and fields
• operators
• 4D commands

To form the query formula:

• in the "Date and Time" theme, double-click on the month
• we type an open parenthesis
• double-click on "Intervention dates"
• close the parenthesis
• and add "=12"

So we're going to search for all the interventions whose month is equal to 12.
Validate and we get 905 records.

We can sort them by chronological order (2009, 2010, and so on) but we've been asked to sort them by day (all the 1s together, then the 2s, and so on).

In the "Order By" menu:

• Remove the previous sort criteria
• At the bottom right, click on "Add Formula"

As in the previous editor:

• in the "Date and Time" theme, double-click on Day of
• open parenthesis (
• double-click on "Intervention date"
• close the parenthesis )
• validate the formula (we return to the Order by editor)
• click on the "Order by" button

And we get all the 1s together, then the 2s, and so on, but they are mixed with 2010, 2009, 2011, etc.

So we're going to add a "normal" sort criteria so that they'll also be sorted by ascending year:

• In the "Records" menu choose => "Order By"
• Double-click to the left on Intervention date
• Double-click to the left on Intervention time (to have a real chronological sort)
• Then click on the "Order by" button

Then we obtain all the interventions for the month of December, sorted by date and time, year by year.

Now we're going to apply a formula; in other words, change the value of a field for the records displayed. Warning: This operation is cannot be undone.

In order, we must:

• Select the records (create a selection matching the criteria).
• Then apply one (or more) formula(s).

In the import of interventions that we made, the duration of the training sessions was not included (7:00:00).

So we're going to:

• Create the "Duration" field in Interventions table of the structure
• Modify the "Output" form to add the Duration field
• Place the Duration field next to the Technician initials
• Duplicate the field and the text
• Modify the title and the corresponding field
• Save the form (we can see the change in the background on the list of interventions)

• We're going to search for interventions that are training sessions
• Then in the same menu, choose "Apply Formula"

As in the previous editor:

• We double-click on "Duration" in the list of fields
• Type ":=" since it is an assignment
• Indicate the duration between question marks like this: ?07:00:00?
• Click to validate

Durations are now assigned to all the training sessions.