Calculations and formulas

Home

4D v16

Video script

In this video, we're going to learn to use variables and do some simple programming.

In addition to saving "raw" information, our database must also provide us with:

both quantitative information (such as volumes, prices, and totals)
and on-the-fly-calculations as needed.

Certain data must be kept and so they are saved in a database field; this is the case, for instance, of the total (including tax) for an estimate or the date of an intervention.

Other data that is less permanent may only be needed during the work-session (and is lost at the end of the session when we exit 4D).

In this second case, we'll use "variable" type objects. One common example is the number of records in the list following a query.

When we display all the interventions, how can we find out how many we have or how many records were found after performing a query on the object ="Training"?

To begin, we're going to indicate, in a variable, the number of records found in the INTERVENTIONS table after a query.

The question to keep in mind while programming is: "When should the calculation be triggered?".

In our case, we think that it should be when the user clicks on one of the following buttons:

All
Query
Selections

In all other cases (like sorting, printing, reports, or labels), the number of records doesn't vary so there's no reason to recalculate it.

We're going to place this variable in the navigation form on page 0 since we ultimately want to use this variable to display the number of records regardless of the table concerned.

So, once we're on page 0:

We click on the variable object type
Then we draw it under the buttons
We display the Property List
We name the variable vNumRecords
And we make it non-enterable.

This will be a variable:

of the Number type
for which we want a number format that can go up to 1 million
For the moment, we don't need to worry about any of the other properties.

Next we're going to set up the calculation method in the "All" button as follows.

After the ALL RECORDS command has executed, we're going to request that vNumRecords be equal to the number of Records in selection for the Interventions table.

Clearly, this number of records will be calculated when we are working on the list of interventions. And we'll do the same calculation, which will be optimized later on, when we are working on the Technicians table.

We can also copy this programming for the Query button since, after our queries on the Technicians or Interventions tables, we'll need to recalculate this number.

For the moment, we're leaving the methods in the objects for educational purposes. Later on, we'll call another method by passing parameters.

Now we can test Navigation and check that:

When we request "All", the number of interventions does in fact match the number that we imported
That the same goes for the technicians.
And that after a query, for instance on the interventions, we check that we have 2346 interventions concerning the hardware
Or that we have 13 interventions that took place on October 10, 2010.