I. Overview
This article introduces how to automate data entry in a form to accurately fill in information about the business trip expenses. The following steps are provided for your reference:
1) Auto Match: When choosing an employee name, his/her other information will be auto matched and filled in corresponding boxes.
2) Option linkage: To fill in the business destination, pick the province first, and then based on the province, the drop-down box of the city option will display a list of cities located in the province.
3) Data entry validation: When inputting expense details, validate whether the expenses are consumed during the business trip.
4) Auto Calculate: If a new expense detail is added, the total expense will be automatically recalculated.
II. Design a data entry form
1. Design some basic effects
1) You can make a drawing using Excel, Axure or other tools.
2) The data entry form is divided in three parts, as shown in the figure below:
Employee information in the upper part
Business information in the middle part
Expense detail in the lower part. You can add or delete rows in this part.
2. Change row height and column width
1) To change the row height, right-click the column number of the selected column. Set the row height of row 1-11 to 10MM.
2) To change the column width, right-click the column number of the selected column. Set the column width of columns B, C and D to 30MM. Set the column width of columns A, F and G to 10MM.
3. Design form structure
1) Employee
Merge the cell range B2:E2 and type the header: Business Trip Expense.
Type the following field names respectively in B3, D3, B4 and D4: Name, Department, Position and Amount of reimbursement.
2) Business trip
Merge the cell range B5:E5 and type the header: Destination
Type the following items respectively in B5, D6, B7 and D7: Province, City, From and To.
3) Expense detail
Merge the cell range B8: E8 type the header: Expense Detail.
Type the following field names in the cell range B10: E10: Date, Time, Purpose and Expense.
Merge B11 and C11and type the field name: Total.
Merge D11 and E11.
4. Add widgets and watermarks
Add the following widgets and watermarks to corresponding cells
Cell | Type of widget | Watermark |
C3 | Drop-down Box | Select your name |
C6 | Drop-down Box | Select province |
E6 | Drop-down Box | Select city |
C7 | Date | Start time |
E7 | Date | End time |
B10 | Date | Select date |
C10 | Date | Select time |
D10 | Drop-down Box | Select item |
E10 | Number | Expense |
5. Set the time format
The default format of the Date widget in C10 is the date format. You can also change it to the time format by choosing [Time] and making the time display in HH:mm format.
6. Preview
Select [Data Entry Preview] to view the effect in the browser window.
7. Add Insert Row and Delete Row buttons
1) Add a Button widget in F10, which is to be functioned as an Insert Row button. Configure its settings as follows:
Button type: Insert Row
Button name: None
Specify cell: B10
2) Add a Button widget in G10, which is to be functioned as a Delete Row button. Configure its settings as follows:
Button type: Delete Row
Button name: None
Specify cell: B10
3) Set the Expansion Direction of B10 as [Vertical].
4) Preview. Click + to add rows and click × to delete rows.
8. Add a SUM formula
1) Insert the following formula in D11: SUM(E10[!0]).
2) Preview. When inserting a new row, typing numbers or deleting a row, the SUM formula will help you to update the data automatically.
III. Define widget options
1. Define the Name options
1) Set a data dictionary for the drop-down box of C3:
Type: Database Table
Database: FRDemo
Select Database: EmployeeInformation
Actual Value: ID
Display Value: Full name
2) Preview. Click the drop-down box to select a name. It is the employee ID rather than the name is displayed in the widget, because the actual value of the widget is employee ID.
3) Select [Data Dictionary] to set the display for C3
Type: Database Table
Database: FRDemo
Select Database: EmployeeInformation
Actual Value: ID
Display Value: Full name
4) Preview it again. Click the drop-down box to select a name, and you can see the name is displayed in the widget (but the actual value is still the employee ID).
2. Auto match of employee information
1) Type formulas respectively in E3, C4 and E4:
Type the following formula in E3: SQL("FRDemo", "SELECT Department FROM EmployeeInformation WHERE ID = '" + C3 + "'", 1, 1)
Type the following formula in C4: SQL("FRDemo", "SELECT Position FROM EmployeeInformation WHERE ID = '" + C3 + "'", 1, 1)
Type the following formula in E4: SWITCH(C4, "General staff", 100, "Senior officer", 300, "Department manager", 1000, "General Manager", 5000)
2) Preview. Select the employee name and FineReport will auto match his/her other information.
3. Define the Province options
1) Create a new dataset [ds1] and type the following SQL statement:
SELECT DISTINCT CASE pid WHEN "" THEN "Other" ELSE pid END AS Province,Provinces AS City FROM Map1
2) Set a data dictionary for the drop-down box of C6:
Type: Data Query
Dataset: ds1
Actual Value and Display Value: Province
4. Linkage of data entry options: Method 1
Set a data dictionary for the drop-down box of E6:
Type: Formula
Actual Value: click the formula box and input the following formula: ds1.select(City, Province = C6)
Display Value: leave the formula box blank and the display value will be equal to the actual value.
5. Linkage of data entry option: Method 2
1) Create a new dataset [ds2] and type the following SQL statement:
SELECT DISTINCT CityFROM (SELECT DISTINCT
CASE pid WHEN "" THEN "Other" ELSE pid END AS Province,
Provinces AS City
FROM Map1) AS AWHERE A.Province = '${C6}'
2) Set a data dictionary for the drop-down box of E6:
Type: Data Query
Dataset: ds2
Actual Value and Display Value: City
3) Preview
6. Customize the drop-down list options
1) Set a data dictionary for the drop-down box of D10. Set the type as [Custom] and type some options manually.
2) Preview.
IV. Set data entry attributes
1. Configure settings for the data entry form
1) Click [Template] >[Data Entry Attribute] to go into the Data Entry Attributes setting pane. Insert [Built-in SQL 1] and [Built-in SQL 2] respectively for the two tables in which you will enter data
2) Insert [Built-in SQL 1]:
Dataset: FRDemo
Table: Business_Trip_Record
Add fields and bind them to cells, as show in the figure below
Check the Key box of EMPID and Start_time
3) Insert [Built-in SQL 2]:
Database: FRDemo
Table: Expense_Detail
Add fields and bind them to cells, as show in the figure below
Check the Key box of EMPID, Date, Time and Purpose
2. Set data validation rules
1) Click Data Validation to set two data validation rules
2) Add Built-in Verify 1:
Validation Formula: (B10 >= C7) && (B10 <= E7)
Validation Error Message: "The date should between the start date and the end date !"
3) Add Built-in Verify 2:
Validation Formula: E7 >= C7
Validation Error Message: "End date should be after the start date ！"
V. Make the report more visually appealing
1. Make the report look better
1) Text color
Titles and field names: 0f3f70
2) Cell background
Titles: d5e6f7
Field names: ebf2f7
3) Border
Color: d5e6f7
4) Others
Align texts to the center
Make titles and field names bold and increase the font size.
2. Set interlaced background color
Set a conditional formatting for B10:
Attribute: Background
Click [Edit] to set f7f9fa as the background color
Applicable to the [Current Row]
Type the formula: row()%2 == 1
Click [+Add] to finish the setting
3. Final effect
Previous：Row-based Data Entry
