Data Manipulation
> Moving Data Between Worksheets
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.
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.
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:
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
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”.
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.
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.
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.
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.
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.
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
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.
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
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”.
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.
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.
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.