reinnovate database

Moving Data Between Worksheets

An application usually have different worksheets working interactively together, and data needs to flow between these worksheets during different stages of operations or use cases. BAU DB enables data flows among worksheets simply by using formulas.
The formula is based on Microsoft Access SQL formula which is a set of English like commands and is very intuitive and self-explanatory. You can study the usage of SQL in this site or Microsoft’s site with this, this and this pages.

Pulling in Single Value from Another Worksheet

In the Order worksheet, user enters a Customer No. and the program pulls in the rest of the data – Customer Name, Contact Person, Telephone and Address, to the other cells automatically. This example demonstrates how to build this automation by using formulas.
pulling customer data to Order worksheet.
Making a Selection List
The first thing to do is to create a customer list for the convenience of input selection of Customer No. In Cell Setting Screen of “Cust. No.”, select “TextCmb” and apply the following formula:
Multi;Customer Selection;600|1400|3000;Select CustNo as [Cust No],CustName as [Cust Name] from tblCust where CustNo>="Text1" and CustType="CUST" order by CustNo
apply formula to the cell setting screen
The formula here displays 2 columns of data. However, only items in the first column are used for selection, the second column is for reference purpose.
Since the table tblCust is also holding the Supplier records, the field CustType is used here to filter out the Customer records by the condition – CustType=”Cust”.
only the first column is the selection items
Interactive Selection List
The selection list can be displayed interactively in response to what you have been entering. In the formula, “Text1” is the keyword which will be replaced by the letter string you have keyed in the cell. In the below example, since “E” is entered, Customer Nos started with “E” are displayed.
formula for dynamic input
selection list changed in response to the cell value
The Formulas
After “Cust. No.” is selected, the cells “Customer”, “Contact Person”, “Contact Telephone” and “Cust. Address” are to be filled in automatically. It is implemented by selecting “LookupR” in Calculation Type and applying a formula to each of the cells. Remember to press “Save & Render Formula” button after adding the last calculation formula.
set one formula for each of the cells
select customer name and select contact person
select customer telephone and address
Formula used in the cell “Customer” :

Select CustName from tblCust where CustNo='tblInv.CustNo'
Formula used in the cell “Contact Person” :

Select Person from tblCust where CustNo='tblInv.CustNo'
Formula used in the cell “Contact Telephone” :

Select Tel from tblCust where CustNo='tblInv.CustNo'
Formula used in the cell “Cust. Address” :

Select InvAddr from tblCust where CustNo='tblInv.CustNo'
The Condition clause of the 4 formulas after the formula keyword from are the same because the criteria of record selection are equal, only the Select parts are different because each cell retrieve different data fields from the database table.
Table.Field embraced in single quotes is a replacing value. In this example, ‘tblInv.CustNo’ is replaced by the value of the cell “Cust. No.” on the worksheet, i.e. the selected Cust. No., and then the formulas are executed to pull in the customer data into the cells respectively.

Pulling in Multiple Values from Another Worksheet

There are use cases when user enters a value in a cell and the program pulls in automatically multiple rows of data on the other cells. In this example, user enters a Order No. in the Invoice worksheet to pull in multiple rows of articles. This can be easily implemented by using formulas.
transfer data from order worksheet to invoice worksheet
Since multiple articles have to be pulled in from Order worksheet, “BulkLookup” is used here with the below formula. When “Order No.” is entered, multiple rows of articles are pulled in automatically.
BulkLookup

Select ItemNo from tblInvDtl where instr('tblInv.InvNoR',InvNo)>0 order by InvNo, Index
bulklookup imports more than one row of record
Like the example above, ‘tblInv.InvNoR’ is a replacing value and the cell value of “Order No.” on the worksheet, which is the selected Customer Order No. to be imported, is replaced in the formula to pull in the articles records.
Instr() is a formula function to determine if a text string exists in another text string. Therefore, Instr(‘tblInv.InvNoR’, InvNo) > 0 is the condition to select a database record if its value of the field – invoice no., exists in the entered string of Order No.

Pulling in Values from Two Tables

When we use a formula, no matter it is for displaying an item selection list or retrieving a value into a cell, it is natural that the formula used may have to look up data from more than one table, and the most frequent use cases are using 2 tables “related” together. To link up the 2 related tables, you can simply add one more condition in the condition clause, or “Where” clause, of the formula.
In the below example, we want to display outstanding Purchase Orders for input selection in Supplier Invoice. Apart from looking at the Purchase Order Header table tblInv, the formula also checks the Purchase Order Item table tblInvDtl to check if the items are received already.
display outstanding purchase order for input selection
Formula used in “PO No.” :

Multi;Workflow : Purchase Order => Supplier Invoice;450|1600;Select distinct tblInv.InvNo as [PO No] from tblInv, tblInvDtl where tblInv.InvNo = tblInvDtl.InvNo and tblInv.DocType="PO" and tblInvDtl.ItemStatus<>"COMP" order by tblInv.InvNo desc
formula links up 2 related tables.
The formula of PO No. involves two data tables – tblInv and tblInvDtl, which are the tables used in the worksheet and both use the common Key Field – InvNo. So, the formula links the 2 tables up by using the Key Field InvNo in the condition clause “where tblInv.InvNo = tblInvDtl.InvNo”.
related header and detail tables in Purchase Order worksheet
The formula checks if the records are Purchase Order with this condition – tblInv.DocType = “PO”, and checks if the article items are still outstanding with this condition – tblInvDtl.ItemStatus <> “COMP”, and therefore both tables tblInv and tblInvDtl have to be used in the formula.
Since duplicated PO Nos. will be displayed with the 2 tables joined together, the keyword “Select distinct” is used to make sure only unique PO Nos. are displayed.

Grouping Multiple Items Together

After selection of the Customer Order Nos in PO worksheet, we would like to pull in the articles from the Customer Orders and group them together if same articles exist in multiple Customer Orders.
bulk lookup articles and group duplicated items together
same articles are grouped and summed up together
Since multiple rows of records are to be retrieved, “BulkLookup” is used and formulas are set in the cells “Article No.”, “Description” and “Quantity” respectively. Remember to press “Save & Render Formula” button after adding the last calculation formula.
set the bulk lookup formula in the cells of article, description and qty
Formula used in “Article No.” :

Select ItemNo from tblInvDtl where instr('tblInv.InvNoR', InvNo)>0 Group By ItemNo, ItemDesc
Formula used in “Description” :

Select ItemDesc from tblInvDtl where instr('tblInv.InvNoR', InvNo)>0 Group By ItemNo, ItemDesc
Formula used in “Qty” :

Select Sum(Qty) from tblInvDtl where instr('tblInv.InvNoR', InvNo)>0 Group By ItemNo, ItemDesc
For the 3 formulas, the part of the formulas starting from the keyword from are the same. It is because they share the same criteria of retrieving records. The only difference is the data fields that the formulas select.
The Group By ItemNo, ItemDesc clause groups same article items together from the selected Customer Orders. The formula of the cell “Qty” uses Select Sum(Qty) because it returns the summed up value of all Qty field of the same article items, if there are any.
The condition clause where instr(‘tblInv.InvNoR’, InvNo) > 0 is to select only records with Order No that exists in the selected Order Nos. The formula function instr(‘tblInv.INvNoR’, InvNo) returns a value larger than zero when the value of the field InvNo is found inside the replacing cell value ‘tblnv.InvNoR’ – the selected Order Nos.