Master-Sub Data Entry Report

1. Problem description

As shown in the figure below, the business logic of the Master-sub table is realized through a single report. The above is the order information, and the data comes from the order table; the following is the order details, and the data comes from the order details, and is associated through the order ID.

When modifying the order information and detailed data above, the data will be backfilled into the corresponding data table.

The data comes from multiple data sources, when data entry, the data is backfilled into multiple tables. We call it multi-source data entry table.

2. Implementation ideas

First, the sub table is associated with the master table by setting filter conditions, and then the data is backfilled into different data tables by defining multiple built-in SQL.

1. Data Preparation

Create a new workbook, add data set DS1, and the SQL statement is: select * from orders.

Add another data set DS2, and the SQL statement is: select * from ordersdetail.

2. Report design

1) Table style design

Set the table sample as shown in the figure below, and drag the corresponding data column into the corresponding cell:

Set the left parent cell of cells B6, B8, E4, E6 and E8 to cell A3, and the rest of the cells are default, that is, the data is expanded according to the orderID.

The types of controls added to each cell are shown in the following table:

3. Parameter setting

Click Template > Template Parameters to add a report parameter named orderID. the default value is 10001.

The corresponding widget is generated in the parameter pane, and the widgetl type is drop-down box.

Then, in the control settings > Data Dictionary of the property panel, set the data table of the drop-down box as order, and the actual value and display value are: order ID.

4. Filter condition setting

Set the filter condition for A3, and the filter is that theorder ID is equal to the parameter $OrderID.

At the same time, set the filter condition for A12, and the filter result is that the OrderID is equal to cell A3.

5. Data entry attribute setting

Click Template > Data Entry Attributes in the menu bar, and add two built-in SQL submissions to the report as shown in the figure below .

Built-in SQL1:

Built-in SQL2:

6. Effect preview

1) PC terminal

Click Data Entry Preview to query the data of order No. 10008. 

Modify sub table, modify quantity and price, and click submit to prompt success. Refresh the browser and select 10008. You can see that the data has changed, as shown in the following figure:

2) Mobile terminal

Both App and HTML5 are supported, and the effect is shown in the following figure:

Completed template, please refer to %fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\DataEntry\DataEntryApplication\MasterSub.cpt

Click download template: 

MasterSub.cpt

1. Execution order of multiple built-in SQLs

If more than one built-in SQL is defined in a data entry template, its execution order will be executed according to the defined order. If an error occurs when executing one of the built-in SQL, the execution of the following built-in SQL will be stopped and the executed built-in SQL will be rolled back. For details, please check Data Entry Attributes.