Creating an auto-updating balance field

In the Online Inventory Management application, the items table has a balance column (see the screenshot below) that keeps track of the current inventory balance for each item. It gets updated automatically whenever a transaction is made. Outgoing transactions decrease the item balance, while incoming transaction increase it.

In this article, we'll explain how you can create a similar field in your own application. We'll make use of 3 table-specific hooks: after_insert, after_update and after_delete. As the names indicate, the code you add to these hooks gets executed upon adding a new record, updating or deleting an existing one, respectively.

The function for updating balances

To make our code easier to manage and avoid repeating code blocks, we'll create a function for updating inventory balances and call that function every time a new transaction record is created, or an existing one edited or deleted. In the generated hooks/transactions.php file, we'll append the following code to the end of the file.

function update_balances($data){
	/*
	  retrieve the ID of the item whose balance is to be updated,
	  and make it safe for use in SQL
	*/
	$item = makeSafe($data['item']);
		
	/*
	  Comma-separated list of all transaction types (each enclosed in single quotes) that add 
	  to inventory -- others are assumed to subtract from inventory
	*/
	$positive_transactions = "'Incoming', 'Transfer In', 'Customer Returns'";
	
	/* get sum of all item transactions and update item balance */
	if($item){
		/*
		  positive (incoming) transactions are added to the balance,
		  others (outgoing) are subtracted from the balance
		*/
		$item_balance = sqlValue("select
			sum(if(transaction_type in ({$positive_transactions}), quantity, -1 * quantity))
			from transactions where item={$item}
		");

		/* update the item balance with the value calculated above */
		sql("update items set balance='{$item_balance}' where id={$item}", $eo);
	}
}

We've tried to add comments to each line of code above to make it easy to understand .. Please note line 12 above. It lists which types of transactions increment the item balance ... So, incoming transactions from suppliers increase the balance, items returned by customers increase the balance, ... etc. You should edit this list to suit your own inventory system. Any other transaction types not in this list are assumed to decrease the balance. So, for example, 'Outgoing', 'Damaged' and 'Expired' transactions would decrease the item balance.

The next step is to call the above function in the events where item balances should be updated. That is, when a new transaction is inserted, or when an existing one is updated or deleted. Here is the code we've added to the transactions_after_insert hook.

function transactions_after_insert($data, $memberInfo, &$args){
	update_balances($data);
	return TRUE;
}

And the code added to the transactions_after_update hook.

function transactions_after_update($data, $memberInfo, &$args){
	update_balances($data);
	return TRUE;
}

The above two hooks are pretty straightforward. We just pass the transaction record, which is already stored for us in $data, to the function we wrote earlier, update_balances(). Now, we need to also call that function after a transaction is deleted. The trick here is that when the hook transactions_after_delete is executed, the record of the transaction no longer exists, and so we don't know the item ID to update its balance. To overcome this, we'll first store the item data in the $GLOBALS array before deleting it. This will preserve the item data so we can still use it after the transaction is deleted.

function transactions_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
	$selectedID = makeSafe($selectedID);
	$res = sql("select * from transactions where id='{$selectedID}'", $eo);
	$GLOBALS['deleted_data'] = db_fetch_assoc($res);
	return TRUE;
}

We've just stored the item data before it gets deleted (using the hook transactions_before_delete) into the super global variable $GLOBALS['deleted_data'] .. We can thus retrieve this data later after the transaction is deleted.

function transactions_after_delete($selectedID, $memberInfo, &$args){
	update_balances($GLOBALS['deleted_data']);
}

That's all! Now, whenever any records are added, edited or deleted in the transactions table, the balance of the concerned item will be automatically recalculated and updated.

Tips and tutorials