build apps by user

Database & Formula

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.
The formula is based on Microsoft Access SQL (Structured Query Language) formula which is a set of English like commands and is very intuitive and self-explanatory. You can learn about SQL in this site or Microsoft’s site with this, this and this pages.

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.
create a selection list of customers
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.
data is extracted from customer 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
add formula to display list of customer for selection.
add formula to display list of customer for selection.
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).
table and field name of customer worksheet
You can also open the actual database table in MS Access:
table and field name of customer worksheet
Selection list displayed for entering “Customer” in Invoice worksheet:
display customer list for selection 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
sort the customer with order by formula
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'
pull in customer data with a formula
cell setting of contact person
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."
pull in customer data with a formula
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
calculate amount from price and qty
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.
calculate amount from price and qty
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.
calculate total amount in the invoice worksheet
calculate amount from product amount automatically

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:
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:
Insert into b (DocNo, A, B) Select 'a.A', 'a.A', 'a.F' from tblDummy where val("value")=0
create an entry in customer worksheet if it is a new customer
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.
create an new invoice entry in the worksheet
create an entry in customer worksheet if it is a new customer

Build Fast and Change Forever

BAU DB is designed for quick and yet powerful database solutions, so that you can always change and grow your solutions to match with your business easily.