4D Doc Center : Query by formula, applying a formula

4D v13

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.

Video script