Video script

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.