How Do I Add Formulae For Basic Calculation To My DataGrid?

In this example we will see how DGH can enable a data grid to do basic column calculations.

To do this we will:

  1. Assign a formula definition to a column
  2. Install the appropriate script in our data grid.

Along the way will will test as we go.

Set Up The Data Grid And Prepare DGH.

First, drag a new data grid to a card, open DGH, and then open the Column Builder.

In the Preview area, click and hold the + (1) and select 3 Columns to set up three columns in our data grid.

In the Property list, expand the Formula topic.

Note 1: Topics in the Property List are not available until we have at least one column in the data grid.

Note 2: The actual data grid does not display any columns as we are yet to apply the changes we are making in DGH.

Assign A Formula Definition To A Column

Select the column you wish to contain your formula. (In this example we will use column 3.)

In the expanded Formula topic, double-click in the text entry area for Formula and type the following: [Col1] * [Col2]

The supported syntax requires you place the column names inside square brackets. The supported operators in formulae are: * / + -

Test The Formula Using The Columns Builder Preview

Before applying our changes to our data grid we can test the formula using the Column Builder Preview.

Double-click on a cell in the preview area and enter some numbers in each of columns 1 and 2. The result of the formula ([Col1] * [Col2]) should appear in column 3.

In this case 15 * 4 = 60

Prevent User Editing Of The Result Column

Because column 3 is displaying the result of a calculation we need to ensure it can’t be edited outside the formula.

With column 3 still selected in the Column Builder view, expand the Columns topic and uncheck the Editable checkbox.

Apply The Change To The Final Data Grid

Click onto the Apply button to apply the changes done in the builder to the targeted data grid.

Add Some Data For Testing

Notice that the data grid now has the three columns added in the previous steps. Note also that the data from the Column Builder Preview did not transfer across. This data was local to DGH.

Now would be a good time to enter some data into the data grid so that we can see the formula in action. Using DGH or the LiveCode Inspector, populate the data grid with some quick data.

(In the figure below we have used the Contents topic to add the data via DGH)

Install The Formulae Calculation Script Into The Data Grid Group

The final step to enable our formula is to install the ready to use script Formulae Calculation inside the data grid group.

This script is available inside the DataGrid Scripts topic of DGH's Table Properties view. Collapse the Column Builder view by clicking the Grid button.

Expand the DataGrid Scripts topic and then click the Install Script button for Formulae Calculation to install the script.

After installation the Install Script button is disabled reflecting the installation was a success. This is also indicated by the button's tooltip.

Testing The Formula

All the remains is to test the formula.

Click on the “Browse” tool and then add some numbers to column 2 of the data grid.

You should see the formula result appear in column 3 for the appropriate row once you have completed the column 2 entry.

Save your stack to use in the next section's example Adding a Grand Total Function.

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.