Customized Data Entry

  • Last update:  2020-12-10
  • 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.

    1.GIF

     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.

     2.GIF

     3) Data entry validation: When inputting expense details, validate whether the expenses are consumed during the business trip.

    3.GIF

    4) Auto Calculate: If a new expense detail is added, the total expense will be automatically recalculated.

    4.GIF

    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.

    5.png


    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.

    6.png

    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.

    7.png


     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.

    8.png


    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

    9.png


    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.

    10.png


    6. Preview

    Select [Data Entry Preview] to view the effect in the browser window.

    11.png


    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

    12.png

     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

    13.png

    3) Set the Expansion Direction of B10 as [Vertical].

    14.png

    4) Preview. Click + to add rows and click × to delete rows.

    15.GIF


    8. Add a SUM formula

    1) Insert the following formula in D11: SUM(E10[!0]).

    16.png

    2) Preview. When inserting a new row, typing numbers or deleting a row, the SUM formula will help you to update the data automatically.

    17.GIF

    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

    18.png

    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.

    19.GIF

    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

    20.png

    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).

    21.GIF


     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)

    22.png

    2) Preview. Select the employee name and FineReport will auto match his/her other information.

    23.GIF


    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

    24.png

    2) Set a data dictionary for the drop-down box of C6:

    • Type: Data Query

    • Dataset: ds1

    • Actual Value and Display Value: Province

    25.png


     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.

    26.png


    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}'

    27.png

    2) Set a data dictionary for the drop-down box of E6:

    • Type: Data Query

    • Dataset: ds2

    •  Actual Value and Display Value: City

    28.png

    3) Preview

    29.GIF


    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.

    30.png

    2) Preview.

    31.GIF

    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

    32.png

    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

    33.png


    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 !"

    34.png

    35.png

    3) Add Built-in Verify 2:

    • Validation Formula: E7 >= C7

    • Validation Error Message: "End date should be after the start date !"

    36.png

    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.

    37.png


    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

    38.png


    3. Final effect

    39.GIF


    Download the template

    Free Form.cpt


    Attachment List


    Theme: Data Entry
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy