Calculations with SQL Results

A SQL Load populates variables that you created in an eFORMz project. These variables are arrays whose number of elements match the number of rows returned by the query. For example, 12 rows returned results in variables of 12 elements. The next time the query runs, it might return 3 rows, and the variables contain 3 elements.

If you perform calculations on these variables and store the results in a new variable, you probably see that the variable contains only a single value, because variables have a single value by default. For example, a query returns the quantity, and weight for items in an order. You multiply them and store the value in the variable Total Weight:

You must create the Total Weight variable correctly for it to have the same number of elements as the variables in the calculations. Complete the following steps to create a variables used with calculations of SQL-populated variables:

1. Create the variables used in the SQL.
2. Create the SQL Load procedure that populates the variables.
3. Copy one of the variables used in the SQL procedure by finding the variable in the Project window, hold the Control (Ctrl) button, and drag it down until you see a black line. Release the mouse button, and the variable is copied.
4. Press F2 to rename the copied variable to a name that you want.

The variable that you copied is now associated with the SQL Load procedure. It will contain the same number of elements as the other variables used in the calculation.