Automate Data with Formula – Part 2
Input Validation – Ensure Input Data are Wanted before Saving
Different people may use your program. Let the program do some checking of input data and give warning if unwanted data is entered. Let’s say, you would like to ensure “Quantity” entered must be greater than 100. This time we go to Main Menu and select “Worksheet Setting”.
Select “Sheet1” in List View and go down to “Validation Before Document Saved” in the Entry Form. Add the validation checking as shown in the below picture and use this line of formula as the validation rule in Required Condition:
Select val('a1.C') >= 100 from tblDummy
In the formula, ‘a1.C’ represent the values of the cells “Quantity” on the worksheet. The formula function val(…) turns a text value into a numeric value to suit for calculation in the formula. This formula always return a True or False value.
Adding the above line of validation is all needs to be done. Go back to Invoice worksheet and enter “Quantity” with value less than 100. On saving the record, you get a error message. Since the Error Type is “Error”, you cannot proceed saving the record unless the “Quantity” is amended. If the error type is “Warning”, you can ignore the message and proceed saving the entry.
Quick Summary of List View
In many use cases, you do not need to create a formal report printed with long list of records and well presented summary information. You just want to know the answer of the totals or sub-totals of some combined record data.
Quick Summary is used for this purpose which summarize data of the records that are listed in the current List View. When combined with the use of Filter Box, this feature allows you to dynamically select the range of records to be displayed in List View and then calculate and display the summary information on Info Pane.
To make a quick summary, go to Worksheet Setting and roll down to Browse Pane’s Cell Property. Add the below 2 lines to Display and Display Formula respectively. You can choose to add in any row:
Display:
Summary;Invoice Summary;500|1600|1800
Display Formula:
"Total", Sum(a.C) as [Total Amt] Group By a.A as Customer
In Display, Summary is the keyword telling the program to perform quick summary. In the format string 500|1600|1800, 500 is the row height of the grid in info pane, 1600 is the width of the first column and 1800 is the width of the second column.
In Display Formula, “Total” is the caption for the grand total. The formula function Sum(a.C) calculate the sum of the data field a.C, which is the value of Amount. The formula Group By a.A displays sub-total of Amount of each customer.
Click Out The Summary
Save this worksheet setting and back to the Invoice Worksheet, click Related Info in the top right corner of the worksheet and you will find that the menu of “Invoice Summary” is created. Click the menu “Invoice Summary” to display the summary in Info Pane. Total amount of all the Invoice entries and sub-total amounts grouped by each customer are displayed in the summary.
Save this worksheet setting and back to the Invoice Worksheet, click Related Info in the top right corner of the worksheet and you will find that the menu of “Invoice Summary” is created. Click the menu “Invoice Summary” to display the summary in Info Pane. Total amount of all the Invoice entries and sub-total amounts grouped by each customer are displayed in the summary.
Related Information from Entry Form
It is a convenient feature that lets you easily apply a formula and display some information related to any data on the worksheet. For example, when you are entering the price of an article, you can set a formula to display the price history of that article in Info Pane.
To apply the formula, click out the Cell Setting Screen of the “Price” cell. The information displayed is for reference only and there is no need to pick out any data from the displayed list, “Text” is used as the Input Box here.
Formula :
Multi;Previous price of 'a1.A';500|1000|1000;Select a1.B as Price, a1.DocNo as Invoice from a1 where a1.A='a1.A' order by a1.DocNo desc
The caption of the display is Previous price of ‘a1.A’, and ‘a1.A’ is to be replaced by the value of the cell “Item” – “product abc”
Only the price history of the current item is to be displayed and therefore the condition clause is where a1.A=’a1.A’, and same as above ‘a1.A’ is to be replaced by the value of the cell “Item” – “product abc”
In the grid formatting string 500|1000|1000, 500 is the row height, the first 1000 is width of the first column and the second 1000 is the width of the second column.
Related Information from List View
Related information can also be triggered from List View. Let’s say you want to display the other contact persons of the customer in an Invoice entry. You can apply a formula so as to create an item in the menu of “Related Info”.
As shown in the below diagram, when an Invoice entry is selected from List View, click the menu, in the top-right corner, Related Info => Other Contacts to display the other contact persons of the client “abc co. ltd.” in Info Pane.
This is the setting of the formula. Go to Worksheet Setting, select the entry “SHEET1”, which is the setting of the current worksheet, and loop down to “Browse Pane’s Cell Properties”. You can add the formula in any line of the current View. The formula is separated into two sections which are stored in the cells “Display” and “Display Formula”.
Display :
Multi;Other Contacts;500|1000
Display Formula :
Select b1.A from b1 where b1.DocNo = 'a.A'
In Display cell, Multi is the keyword for displaying a multiple item list. Other Contacts is the caption in the menu of Related Info. In the formatting string 500|1000, 500 is the row height and 1000 is the cell width of the first displayed column, which is also the only column in this example.
In Display Formula, the select clause Select b1.A from b1 retrieve records from b1 which is the table holding the customer’s contact persons.
In the condition clause b1.DocNo = ‘a.A’, ‘a.A’ is the replacing value with data retrieved from List View in the corresponding column, in this example it is “abc co. ltd.”.
The above demonstrations show that it is so simple and yet powerful of using formulas to automate your works in a database application. The formulas are very flexible and have been applied to different real life applications already. You can twist them to adapt to your works easily and rapidly.