Using lookup fields in calculations

When customizing the before_insert, after_insert, before_update or after_update hooks to make a calculation, you might encounter a case where one or more fields in the formula you're calculating is a lookup field (foreign key). In this case, the value of $data['fieldname'] (where fieldname is the name of the concerned lookup field) is probably NOT the value you'd like to use for your calculation.

To explain that, let's have a brief look at how lookup fields work. A lookup field is a way of referencing a value from one table in another table. For example, we might be storing product unit price in the 'products' table and want to use that same unit price in the 'order_items' table without having to manually re-type the price ... this is important not just to save a few keystrokes during data entry, but to also ensure referential integrity ... If you throw the same product price into every table in your database, it will be a nightmare to update the price later and make sure all tables see the updated price.

To avoid that mess, we use lookup fields. A unit price lookup field in the 'order_items' table doesn't store the actual price value but rather a reference value that points to the location of the unit price in the 'products' table. The best possible reference to use is the primary key of the product. Let's have an example with numbers to see this in action.

Products table

That was yummy☺ Each entry in the above table has a primary key 'ID' value, which doesn't tell much about the item itself but is used as a reference to it. So, if we talk about product #18, we know we are referring to Lindt EXCELLENCE Mint priced at $3.25. Primary key fields are usually (but not necessarily) named 'ID'.

Let's now have a look at some data from the 'order_items' table.

Order Items table

Similar to the 'products' table, the 'ID' column above is the primary key field of the 'order_items' table, a way of uniquely identifying each row. 'OrderID' is a lookup field to the orders table (not shown here as it's irrelevant to our discussion). 'Product' and 'Unit Price' are both lookup fields to the products table. To understand this with an example, order item #2024 is an order for product #15, which is Lindt HELLO Crunchy Nougat and its price is of course that of product #15 which is $2.05. And the quantity of Lindt HELLO Crunchy Nougat ordered in this record is 1.

When your AppGini application displays the 'order_items' table, it doesn't display reference values like the above. It automatically joins both tables and displays more human-readable results like the ones below

Order Items table joined with Products table

If we later make any modifications to any product in the 'products' table, like changing its name or unit price, the changes are automatically reflected in the 'order_items' table without having to perform any manual data entry.

What remains now is to write code for calculating the subtotal column of the 'order_items' table. We want this calculation to be applied whenever we add a new order item and also whenever we make changes to any existing order item. Therefore, we should perform the calculation in both the before_insert and before_update hook functions.

The initial code I see many AppGini users write usually looks something like this:

$data['Subtotal'] = $data['UnitPrice'] * $data['Quantity'];

The problem with the above code is that $data['UnitPrice'] stores the primary key of the parent product (the value of the 'ID' field from the parent record in 'products'). For example, if we're calculating the subtotal of order item #2025, the above code would display a subtotal of 18 x 3 = $54. This is of course not correct, as the unit price for Lindt EXCELLENCE Mint is $3.25 and we have a quantity of 3 units. Therefore, the correct subtotal should be $3.25 x 3 = $9.75.

What's wrong with the above code is that we didn't take into consideration the fact that 'UnitPrice' field in 'order_items' is actually a lookup field. The stored value is not the unit price but rather the primary key value of the parent product. Accordingly, we should retrieve the actual unit price from the products table using this code:

$UnitPrice = sqlValue(
	"SELECT UnitPrice FROM products where ID='{$data['UnitPrice']}'"
);

The above code retrieves the unit price from the products table given the primary key value stored in the child 'order_items' table, $data['UnitPrice'], and stores the actual unit price in $UnitPrice. We can now perform the calculation as follows:

$data['Subtotal'] = $UnitPrice * $data['Quantity'];

Putting it all together, whenever we are performing calculations that involve lookup fields, we should first retrieve the actual values from the parent table and use those retrieved values in the calculation formula. It's very easy to write once we understand how it works. To sum up, here is our subtotal code:

$UnitPrice = sqlValue(
	"SELECT UnitPrice FROM products where ID='{$data['UnitPrice']}'"
);
$data['Subtotal'] = $UnitPrice * $data['Quantity'];

One final note ... some tables contain non-numeric primary key values. For example, if the above 'products' table stores primary keys as 'LHCN01', 'LEM01' ... etc rather than '18', '19' and so on, then we should escape those primary keys first to avoid query errors, or worse, an SQL injection attack:

/* Escape non-numeric lookup values before using them in SQL queries */
$SafeUnitPriceLookup = makeSafe($data['UnitPrice'], false);

/*
	Now it's safe to use $SafeUnitPriceLookup to
	retrieve our unit price
*/
$UnitPrice = sqlValue(
	"SELECT UnitPrice FROM products where ID='{$SafeUnitPriceLookup}'"
);

/* And here is our calculation */
$data['Subtotal'] = $UnitPrice * $data['Quantity'];