Database & Formula
> Automate Data with Formula Part 1 > Part 2
Automate Data with Formula – Part 1
One of the fun part using BAU DB is that you could always tell the program to do something in a single line of formula. You will find that it’s just easy to communicate with it.
Create a Selection List to Assist Data Entry
When entering data, very often you would like your program to provide an item list to choose from in order to save time and there is no need to figure out what to enter into the text box. It is also a mean to ensure people are entering the same data to your application. It can be done simply by applying a single formula.
In this example, a list of customer name from another worksheet is to be displayed in the Invoice worksheet. Let’s create a new worksheet “Sheet2”, which holds the customer records, and add cell captions “Customer No.”, “Customer” and “Contact Person” on the worksheet. Then, you can add some customer entries in the worksheet.
To add a selection list in the “Customer” cell of Invoice worksheet (Sheet1), what needs to do is to click out the cell setting screen of the “Customer” cell and select “TextCmb” in Input Box and add the below line in Lookup Formula:
Multi;;500|2000;Select b.A as Customer from b
In the formula, Multi is the keyword for displaying a list of data. In the format string 500|2000, 500 is the row height of the selection list, and 2000 is the width of the column.
In the formula, b.A is the combined table and field name in the actual database which store the data of the cell “Customer” in Customer worksheet (or Sheet2).
You can also open the actual database table in MS Access:
Selection list displayed for entering “Customer” in Invoice worksheet:
After applying the above formula, you find that the customers are not displayed in alphabetical order, and you might like to sort it by adding a “order by” clause to the formula:
Multi;;500|2000;Select b.A as Customer from b order by b.A
This demonstration shows that you can always adjust formula easily and rapidly to match with your works.
Lookup Data From Another Worksheet
It’s common for an application to have at least a few worksheets working together. It’s just convenient to pull in some data from another worksheet without entering or copying it manually.
In the Invoice worksheet, you would like to pull in “Contact Person” from Customer worksheet when the value of “Customer” is selected and entered. In the Cell Setting Screen of the cell “Contact Person” in Invoice worksheet, select “LookupR” in Calculation Type, add the below line of formula and press “Save & Render Formula”:
Select b.B from b where b.A = 'a.A'
Table and field name enclosed in single quotes means that the corresponding cell value on the worksheet is to be replaced into the formula before its execution. In the formula, ‘a.A’ is replaced by the value of the selected customer, ABC Inc., on Invoice worksheet. Contact Person is then extracted from Customer worksheet that has the same customer name with this formula:
Select b.B from b where b.A = "ABC Inc."
You could create a worksheet for product item and do the same to pop out the item price when a product item is selected.
Do Some Calculations on the Worksheet
Let’s see how to use formula to do calculation between some cells. After entering product price and quantity, you would like to calculate the amount automatically.
In Invoice worksheet, create cell captions of “Quantity” and “Amount” beside the cell “Price”. In Cell Setting Screen of “Amount”, select “LookupR” and apply this formula:
Select val('a1.B') * val('a1.C') from tblDummy
In the formula, “tblDummy” is a default database table in BAU DB. When the formula does not involve any specific data tables, just use this dummy table “tblDummy”. ‘a1.B’ and ‘a1.C’ are replaced by the values of “Price” and “Quantity” on the worksheet respectively. “Amount” is calculated after “Price”and “Quantity” are entered.
You could also sum up the total amount of the invoice. In the cell setting screen of “Total Amount”, select “Sum” in Calculation Type and apply the table and field name of the cell “Amount”, a1.D, in Calculation Formula.
Data Manipulations After Saving An Entry
Apart from making query, formulas can also be used to update record. Let’s see how we can do some processing and manipulation of data by simply using formulas.
Let’s make an automation in the Invoice worksheet, which is to add an entry to Customer worksheet if the customer entered in Invoice worksheet is a new one, so that you have no need to enter customer data twice in both Customer and Invoice worksheet.
Counter Formula
Go to Worksheet Setting, select “Sheet1”, i.e. Invoice worksheet, and roll down to the section “Processing After Document Saved” in the Entry Form. Add the line as shown in the picture below which consists of 2 formulas. The first one is Counter Formula which makes a query to check if the customer entered exists in Customer worksheet:
Go to Worksheet Setting, select “Sheet1”, i.e. Invoice worksheet, and roll down to the section “Processing After Document Saved” in the Entry Form. Add the line as shown in the picture below which consists of 2 formulas. The first one is Counter Formula which makes a query to check if the customer entered exists in Customer worksheet:
Select Count(*) from b where b.A='a.A'
In the formula, Count(*) is a formula function which returns the number of records that satisfy the conditions defined in the “where” clause.
Update Formula
The second one is Update Formula which integrates the value calculated from Counter Formula with the keyword “value” which creates an entry in Customer worksheet if the customer is not found:
The second one is Update Formula which integrates the value calculated from Counter Formula with the keyword “value” which creates an entry in Customer worksheet if the customer is not found:
Insert into b (DocNo, A, B) Select 'a.A', 'a.A', 'a.F' from tblDummy where val("value")=0
The formula pattern Insert into … Select … from … where … is to add some records into a table with data supply from the “Select” clause. The “Select” clause returns records only when the conditions is satisfied in Where” clause. The condition in the formula is val(“value”) = 0 means the customer is not found in Customer worksheet.
Execution of Formulas
Back to Invoice worksheet, create an entry with a new customer and save the record. Once the record is saved, the formulas are processed and a new entry in Customer worksheet is created automatically. To simplify the demonstration, “Customer No.” is the same as “Customer” in the formula.
Back to Invoice worksheet, create an entry with a new customer and save the record. Once the record is saved, the formulas are processed and a new entry in Customer worksheet is created automatically. To simplify the demonstration, “Customer No.” is the same as “Customer” in the formula.
