I. Overview
1) Row-based data entry is applied to enter and modify data with consistent structure. For instance, the check-in and check-out procedure of products can be managed in one row-based form.
2) With a row-based form, you can realize the following effects:
a) Insert and delete rows
b) Batch delete
c) Single-row submission
d) Auto calculation by formula
e) Data validation
| You will learn |
|---|
|
II. Design row-based forms
1. Page setup
Select [Template] >[Page Setup]. Set the [Page Orientation] as [Landscape].
2. Design the form
1) Create a DB query dataset and enter the following SQL statement.
SELECT * FROM ProductsLIMIT 15
2) Drag data columns into B3~H3 and input headers in B2~H2.
3) Select B3~H3, click [Widget Setting] in the right pane, and select [Text Widget].
3. Beautify the form style
1) B2~H3: Set as center alignment
2) Set the color of header text as #455075, in bold
3) Set the color of text in data column as #7f879f
4) Add inner and outer borders to B2~H3, and set the color of border line as #f2f4f8
5) Set the background color of B2~H2 as #eef1f8
4. Data entry preview
1) Click on the triangle icon and select [Data Entry Preview].
2) The upper toolbar has buttons such as [Submit] and [Data Validation]. You can click any cell in the body text to edit it.
5. Data Entry Attributes
1) Click [Template] >[Data Entry Attribute] >[Submit]
2) Click [+] and select [Built-in SQL]. Set the [Submit Type] as [Smart Submit] by default. Choose the Database: [FRDemo] and Table: [Product].
6. Smart add fields
1) Click [Smart Add Fields], check all the fields in table [Product] by default, and click [OK].
2) Check [ProductID] and set it as the key, so that submitted data will be matched with existing data in the database by ProductID.
7. Smart add cells
1) Click [Smart Add Cells].
2) Click B3-H3 one by one and match these cells with the fields in [Product]. In this way, when entering data into these cells, these data will be automatically entered in corresponding fields. Click [OK] in the [Smart Add Cells] pane. Then click [OK] at the bottom of the [Data Entry Attributes] setup pane.
8. Web attributes
1) Click [Template] >[Web Attribute], select [Data Entry Settings], and choose [Individually set for the template].
2) Double click the [Delete Record] button to make it appear in the toolbar. Double click on the icon of the button in the toolbar, and an [Edit] pane will pop up. Click the upward/downward arrow on the left pane to change the button position.
9. Data entry preview
1) Double click the cell to modify the contents of the cell.
2) Click the cell and then click [Add Record] or [Delete Record] in the toolbar.
3) Click [Submit] and data will be entered into the database.
Download the template
III. How to insert and delete rows
1. Set the Insert Row button
1) Click I3 and set the widget in the right-hand Widget Setting pane as follows: [Select Widget: Button], [Button Type: Insert Row], [Button Name: Insert].
2) [Specify Cell: B3], representing that when you click the Insert button, a new row will be inserted, starting from B3. [Number of Rows: 1], representing that every time when you click the button, an empty row will be inserted below.
2. Set the Delete Row button
1) Click J3 and set the widget in the right-hand Widget Setting pane as follows: [Select Widget: Button], [Button Type: Delete Row], [Button Name: Delete].
2) [Specify Cell: B3], representing that when you click the Insert button, a new row will be inserted, starting from B3. [Number of Rows: 1], representing that every time when you click the button, this row will be deleted.
3. Data entry preview
Click [Insert] and [Delete] buttons to preview the effect.
4. Set the Checkbox button
1) Click A3 and set the widget in the right-hand Widget Setting pane as follows: [Select Widget: Checkbox]
2) Set A3 in the right-hand Cell Attributes pane as follows: [Expansion Direction: Vertically], [Left Parent Cell: Custom—B3]
5. Set the Batch Delete button
1) Click B5 and set the widget in the right-hand Widget Setting pane as follows: [Select Widget: Button], [Button Name: Batch Delete].
2) Click [Event] and click [+] to add a [Click Event] in the right pane.
3) Enter the following JavaScript codes in the Event Definition text box:
var $span = $('.fr-checkbox-checkon'); //Get all selected checkboxesvar array = [];// This array will store rows that are selected
var $tds = $("td").has($span); //Get cells that contain the selected checkboxes (i.e. the selected cells)
for (var i = 0, len = $tds.length; i < len; i++) { //Traverse the selected cells
var id = $($tds[i]).attr("id"); //Get the row numbers of selected cells
if (id) {
darray.push(id); //Push the row number to darray
}
}
contentPane.deleteReportRC(null, darray); // The second parameter is the row numberscontentPane.writeReport();
6. Data entry preview
Check the checkboxes in the left column corresponding to the rows you want to delete, and then click [Batch Delete] to delete them.
Download the template
IV. Other settings of data entry
1. Set the background of the row being edited
1) Select [Web Attributes] and [Data Entry Settings].
2) Check [Set background of the row being edited] and choose a color.
3) Data entry preview. Click a cell and the row of this cell will be highlighted.
2. Set the Drop-down Box widget
1) Click E3 and set the widget in the right-hand Widget Setting pane as follows: [Select Widget: Drop-down Box].
2) Click […] at the end of [Data Dictionary] and a [Data Dictionary] setup pane will pop up. Settings are as follows: [Database: FRDemo], [Select Database: Category], [Actual Value: CategoryID], [Display Value: Category_name].
3) Data entry preview. Select all the data in the column [Category ID], and category names will be shown in the drop-down box. After selecting the Category Name, the corresponding Category ID will be displayed.
3. Set the Display Value of the Drop-down Box widget
1) Click E3, select [Cell Attributes] >[Display] in the right pane, and select [Data Dictionary] in the drop-down lists.
2) Settings are as follows: [Database: FRDemo], [Select Database: Category], [Actual Value: CategoryID], [Display Value: Category_name]
3) Data entry preview. Select all the data in the column [Category ID], and category names will be shown in the drop-down box. After selecting the Category Name in the drop-down box, the Category Name is still displayed in the cell, while after clicking [Submit], corresponding Category ID will be submitted.
4. Set the Digital widget
Click H3 and set the widget in the right-hand Widget Setting pane as follows: [Select Widget: Digital Widget].
5. Set the Submit Button for single row submission
1) Click K3 and set the widget in the right-hand Widget Setting pane as follows: [Select Widget: Button], [Button Name: Submit].
2) Add a [Click Event] and settings are as follows: [Commit to Database], [Database: FRDemo], [Table: Product].
3) Click [Smart Add Fields] and all the fields in the table [Product] will be automatically recognized. Set ProductID as the key. Click [Smart Add Cells] and follow the above steps to match cells with fields.
4) Set Callback Function. Click [Set Callback Function] and enter the following JS codes:
if (fr_submitinfo.success) {
FR.Msg.toast('Successfully submitcurrent row to the database.');
setTimeout("location.reload();", 3000 )
} else {
FR.Msg.toast('Fail to submit currentrow to the database.');
}
6. Data entry preview
Click [Submit] and the current single row can be individually submitted to the database.
Download the template
V. Auto calculation and insert row policy
1. Insert a new column of Unit Profit
1) Right click column I and select [Insert Column] to insert a column on the left.
2) Enter Unit Profit as the header in I2 and insert the formula G3-H3 in I3.
2. Use the format painter to copy format
1) Click H2, click on the icon of format painter in the toolbar, and then click I2. In this way, I2 will have the same format with H2.
2) Similarly, click H3, click on the icon of format painter in the toolbar, and then click I3, thus I3 will have the same format with H3.
3. Data entry preview
1) After modifying the Cost Price and Unit Price in a selected row, the Unit Profit will be automatically calculated using the formula.
2) After modifying the Cost Price and Unit Price in the newly inserted row, the Unit Profit cannot be automatically calculated using the formula.
4. Set the Insert Row Policy
1) Click I3, then click [Cell Attributes] >[Other] and set [Insert Row Policy] as [Original Value] in the right pane.
2) Select G3~H3, click [Cell Attributes] >[Other] and set [Insert Row Policy] as [Default] in the right pane. Click the icon below, select [Double-Precision], and input 0 in the box.
5. Data entry preview
1) Insert a new row. The Cost Price and the Unit Price are 0 by default, and accordingly, the Unit Profit is automatically calculated as 0.
2) Modify the Cost Price and the Unit Price in the newly inserted row, and the Unit Profit will be automatically calculated.
Download the template
VI. Data entry validation
1. Set data validation for widgets
Click B3, uncheck [Allow Null] and enter [Enter the product ID!] in [Error Tip] in the right pane.
2. Set data validation
1) Select [Template] >[Data Entry Attribute], a [Data Entry Attribute] setup pane will pop up, and select [Data Validation].
2) Click [+] and select [Built-in Verify] on the left side of the pane. Click [+] and add Validation Formula on the right side of the pane.
3) Enter the Validation Formula: I3 >= 0, and the Validation Error Information: "Unit profit cannot be negative"
3. Data entry preview
1) Insert a row and click any cell in [Product ID] column without entering any data. Then directly click other cells, and an error message will pop up.
2) Modify the values in cells of [Cost Price] and [Unit Price] to get a negative value in the cell of [Unit Profit]. Click [Submit] and an error message will pop up.
Download the template
VII. Import data from Excel
1. Cancel the display settings of E3
1) Choose [Cell Attributes] > [Display] and click the drop-down box to choose [Cancel Display Settings].
2) In the preview, the column of Category ID display ID numbers (rather than category names).
2. Download an Excel file
1) In the preview, click [Export] > [Excel] > [Full Page Export] in the upper toolbar.
2) After the Excel file is exported, open it. Here, you can input the data to to be imported in the Excel file.
Note
The data in the exported Excel file are display values. We've canceled the display settings of E3 in the previous step, and the reason is that, in this way, the data in the column Category ID are ID numbers and this can inform the person who fills the data to enter ID numbers. Otherwise, in the exported Excel file, the data in the column of Category ID are category names and this may mislead people who fill the data.
Using the function of Full Page Export, the formula in the column of Unit Profit is disabled in the Excel file, and the exported values are the computation results of the formula. If you use formula to calculate the unit profit in the Excel, do not import this Excel directly since the formula will be invalid after being imported. You need to copy the data to another Excel file and choose to paste the [values].
3. Add a button for importing
1) In [WEB Attributes], double-click [Import Excel Data].
2) In the preview, a button named Import [Excel] is added to the upper toolbar.
4. Clear cell data
1) Select B3~I3, right-click and chosse [Clear] > [Content].
2) Add a Text Field widget for I3.
3) Preview. Now you will get a pure form for importing data.
5. Import data
1) Prepare the data to be imported. Open the exported Excel file. Preserve the first 2 rows and delete other rows.
2) Click button Import [Excel] and choose the Excel file to finish the importing.
