reinnovate database

Processing of Data (After Saving an Entry)

Let’s have a look at more features about manipulations of data after saving a record entry. These features simplify the process of manipulating data so that you can simply use a line of formula or several formulas combined together to accomplish some tasks.
This example demonstrates how to update stock quantities from Invoice worksheet. In the actual operation, an Invoice is created and sent to customer when goods are delivered. The Invoice worksheet updates stock quantities of the delivered articles when an Invoice entry is created:
stock movement of an Invoice worksheet
The formulas are added in the section “Processing After Document Saved” in Worksheet Setting. In Main Menu, click “Worksheet Setting”. In Worksheet Setting, select the entry “INV” which is the setting of Invoice worksheet. Scroll down to the section – Processing After Document Saved.
manipulation o data after saving record

Executing Formulas in Sequence

Sometimes we want to accomplish a task which needs more than one formula. This is done by adding the order of executing formulas in “Priority”. To update stock quantities from Invoice worksheet, 2 formulas are used and executed in sequence:
calculate stock when saving invoice
This formulas are executed step by step like this:
Line 14 : Check if the articles exist in the “Out” location. If an article does not exist, create an item of the article in “Out” warehouse.
line 15 : Calculate and update article quantities in the “Out” warehouse.
Counter Formula of Line 14:
Select Count(ItemNo) from tblItemStock where ItemNo = 'tblInvDtl.ItemNo' and WareHouse = 'tblInv.OutWare'
It counts if the articles of the Invoice entry exists in the data table of Inventory. If an article does not exist, the formula returns a zero value. Select Count() is a formula function which returns the number of records in the table with the condition as defined in the “where” clause.
Update Formula of Line 14:
Insert Into tblItemStock (ItemNo, WareHouse) Select top 1 ItemNo, OutWare from tblInv,tblInvDtl where tblInv.InvNo=tblInvDtl.InvNo and val("value") = 0 and ItemNo = 'tblInvDtl.ItemNo' and OutWare = 'tblInv.OutWare'
The Update formula will only create an article record in the Inventory table if the article does not exist, by using the conditionval(“value”) = 0. The keyword “value” is the return value from the Counter Formula. Insert Into … Select … from … where … is a convenient formula structure to be used for creating records based on some conditions.
Counter Formula of Line 15:
Select 'tblInvDtl.ItemNo', sum(iif('tblInv.OutWare'=InWare,Qty,-Qty)) from tblInv,tblInvDtl where tblInv.InvNo=tblInvDtl.InvNo and ItemNo='tblInvDtl.ItemNo' and 'tblInv.OutWare' in (InWare,OutWare) and DocType in ("INV","SUP","PRO","RMA","DBN") and StockMvmt<>"No Movement"
This Counter Formula sums up the stock quantity of an article from all of its related document entries, such as Invoice and Supplier Invoices etc. The selection of record entries is done with this condition – DocType in (“INV”,”SUP”,”PRO”,”RMA”,”DBN”). These are the Document Types of Worksheet entries that will affect the stock quantity. For example, an Invoice entry removes articles from the stock while a Supplier Invoice entry adds articles into the stock. Sum() is a formula function which sums up the value of a numeric data field out of a set of selected records.
Update Formula of Line 15:
Update tblItemStock Set Qty = val("value1"), Stockdate = dateValue('tblInv.Invdate') where ItemNo='tblInvDtl.ItemNo' and WareHouse='tblInv.OutWare'
The Update Formula here replaces the calculated stock quantity returned from the Counter Formula with the keyword “value” and updates to its Inventory record.

Formula Looping Through Multiple Cells

When a formula involves a replacing cell value of multiple occurrence cell, the formula will be applied to each row of the multiple cells automatically. This is a convenient feature so that you can apply only one formula to calculate all rows of data on a worksheet.
article is a multiple cell in the Invoice worksheet
Inside the Counter Formula of Line 14, the replacing cell value ‘tblInvDtl.ItemNo’ is a multiple occurrence cell, in this case the Counter Formula and Update Formula will loops through each line of the multiple occurrence cells and apply the formula once for each row of the multiple cells. In this example, the formula will loop through each article in the Invoice entry and calculate the stock quantity for each article one by one.
Select Count(ItemNo) from tblItemStock where ItemNo = 'tblInvDtl.ItemNo' and WareHouse = 'tblInv.OutWare'
the formula loop through each row of the multiple cells

IIF() – Adding Decision Power in Formula

The iif() is a convenient formula function which lets the formula to “think”. The structure of this function is iif(Condition, True_Value, False_Value). If the Condition returns TRUE, the iif() function returns the middle part – the True_Value, otherwise it returns the right part – the False_Value.
In Counter Formula of Line 15, iif(‘tblInv.OutWare’ = InWare, Qty, -Qty) is used to determine whether the transaction quantity in a record entry should be positive or negative:
Select 'tblInvDtl.ItemNo', sum(iif('tblInv.OutWare'=InWare,Qty,-Qty)) from tblInv,tblInvDtl where tblInv.InvNo=tblInvDtl.InvNo and ItemNo='tblInvDtl.ItemNo' and 'tblInv.OutWare' in (InWare,OutWare) and DocType in ("INV","SUP","PRO","RMA","DBN") and StockMvmt<>"No Movement"
‘tblInv.OutWare’ is the replacing value which refers to the “Out” location of the Invoice entry on the worksheet. The data field InWare is the “In” location of the other entries in the database table. The iif(‘tblInv.OutWare’ = InWare, Qty, -Qty) function here determines whether “::Warehouse” – the value of warehouse, i.e. the replacing value of ‘tblInv.OutWare’, is used in the “In” location or the “Out” location of the other entries.
the replacing value of tblInv.outware in Invoice worksheet
If the “::Warehouse” is used in “In” location, the items are moved in stock and quantity is positive, while if it is used in “Out” location, items are moved out stock and quantity is negative. The formula uses the Sum() function to sum up the In and Out quantities of an article and therefore calculates the final stock quantity of that article.

Multiple Return Values of a Counter Formula

It’s convenient to use Counter Formula to calculate or retrieve some values and pass the values to Update Formula for updating data. In fact, a Counter Formula can return more than one calculation result at a time.
a counter formula can return multiple results
In Counter Formula of Line 15, iif(‘tblInv.OutWare’ = InWare, Qty, -Qty) is the second return value while the first return value is ‘tblInvDtl.ItemNo’:
Select 'tblInvDtl.ItemNo', sum(iif('tblInv.OutWare'=InWare,Qty,-Qty)) from tblInv,tblInvDtl where tblInv.InvNo=tblInvDtl.InvNo and ItemNo='tblInvDtl.ItemNo' and 'tblInv.OutWare' in (InWare,OutWare) and DocType in ("INV","SUP","PRO","RMA","DBN") and StockMvmt<>"No Movement"
In the Update Formula, “value” is the keyword for the first return value, and “value1” for the 2nd return value, and so on … Since in this Update Formula, we want to use the 2nd return value of summing the stock quantity, the keyword “value1” is used in the formula. A maximum of 99 return values can be used which should be more than enough.
Update tblItemStock Set Qty = val("value1"), Stockdate = dateValue('tblInv.Invdate') where ItemNo='tblInvDtl.ItemNo' and WareHouse='tblInv.OutWare'

Removing Records

Apart from creating and updating records, in the process of operations, you may like to remove some records that have become no use or should not exist any more. As the continuation of the above examples, after the stock quantities are updated, we want to remove some stock records which have zero quantity :
Delete * from tblItemStock where Qty=0 and WareHouse<>"::Warehouse"
We do not want to remove the stock item in the actual warehouse, i.e. “::Warehouse”, only the zero stock items in customer warehouses and supplier warehouses need to be removed. Therefore, the condition WareHouse<>“::Warehouse” is used to select the necessary records to be deleted.
this formula delete records with zero stock quantity.
The above examples demonstrated how to use multiple formulas and execute them in sequence in order to perform certain tasks, and the capability of formula to loop through multiple cells and make some decisions by using iif() function which adds power and flexibility when manipulating the data records.