SubGrids(Popgrid) in DC

There may be a need to enter detail lines for every line entered in a grid. So, every row in the detail table may have more than one row in the sub detail table. Consider a request document in which a user requests for a list of products & quantities. Each of these line items are given a supply schedule as below.

Product UOM Quanity
Computer Nos 100
Monitor Nos   50
Keyboards Nos 150
Product Due date Qty
Computer 01/08/2011 40
Computer 10/08/2011 40
Computer 10/09/2011 20
 Product Due date Qty
Monitor 01/08/2011 40
Monitor 10/08/2011 40
Montior 10/09/2011 20

The supply schedule for computers & Monitor is provided in sub grid. Similarly, you could provide for all other products.

sub grid is defined as a Grid DC. Set its pop grid propety on.  Fill the details in the popgrid definition form based on need.

The text entered in the “Heading” text box will appear as the title for the popgrid.

The grid DC to which this grid is a sub grid is the parent DC. In the above example the products DC in which the total quantity is entered is the Parent DC. This DC is also called the parent grid.

Parent fields are the fields that relate the parent grid and the sub grid. In this example the product is the parent field. That is for every unique product in the parent grid there can be many detail rows in the sub grid. There can be more than one parent field. That is for every unique combination of values in the parent fields there can be many detail rows in the sub grid. The values in the parent fields should be unique in the parent grid.

Every parent field should have a corresponding field in the sub grid. This is to be defined manually by you. For example, if the parent field is product in parent grid. The 3rd DC is the sub grid. The field should be named sub3_product. That is the field should be named sub<dc number>_<parent field name>. This field should be a hidden field. Its width and mode of entry should be the same as that of the parent field.

Any or all of the parent fields can be selected in “Show button” box. A button will be displayed beside these fields at run time. The end user can open the sub grid on click of this button.

If “Auto show” is set to true, then the sub grid will popup when any of the parent field values are changed and all the parent field values are bound. That is after user has entered values into all parent fields & any one parent field is changed, the sub grid will automatically popup for end user to make corresponding changes to the sub detail rows.

Consider a case where when user focuses on a field, the sub grid pops up for users to enter detail rows. Further, the column values from the sub grid need to be populated into this field as a delimited string when the sub grid is closed. You can achieve this by selecting a “display in” field & setting the “Summary format“.

In the above example assume there is a column named “Supply schedule” in the parent grid. On entering this column, the sub grid should popup. On close of the sub grid the due date & qty should be displayed as “01/08/2011 40, 10/08/2011 40, 10/09/2011 20”. To achieve this set the display in as “Supply Schedule”, Summary Format as “:DueQty :Qty”. Set ‘,’ in delimiter. For every row in the sub grid, a string is made as per the format and each row string is seperated with the given delimiter.

condition can be set based on the fields in the parent gird or any non grid field that is before the parent grid. The sub grid is applicable only if the this condition evaluates to true. In the above example, assume that there is a field (named “SchRequired”) in the header in which user can say whether, supply schedule is to be given or not. Use this field to provide a condition. In the value drop down you can enter any value or select any field from the drop down.

You may want to pre populate the sub grid with values when the user opens it. In this case provide an SQL statement in “Auto Fill” that will fill the sub grid. The column names in the SQL result should match with the field names in the sub grid DC. You can use any field from the parent grid or any non grid before the parent grid as parameters to the SQL.

If “Firm Bind” is set to true, then the user cannot modify the SQL result. Any change made in the parent grid, will automatically change the contents of the sub grid. This will be done even if the sub grid is not opened by the user.

Allow addition of rows” property is useful only if firm bind is set to true. If this is checked, then user will be allowed to add rows to the SQL result that has been filled by Axpert. So, when the SQL parameter values are changed in the parent grid, only the rows populated into this grid from the SQL will be refreshed and all the user entered rows will be maintained as it is.


SQL result is as below

Product Due date Qty
Computer 10/08/2011 100
Computer 15/08/2011   50
Computer 20/08/2011 150
Product Due date Qty
Computer 10/08/2011 100
Computer 15/08/2011 50
Computer 20/08/2011 150
Computer 10/09/2011 20

<– Data filled into sub grid

The last row is user-created.

Key columns” are the columns that relate an SQL result and the columns in the sub grid. The rows in the sub grid and an SQL result are matched based on key columns. If the key columns match, the changes in the rest of the columns will be updated in corresponding rows from SQL result. This is applicable only when data in the parent grid is modified after the sub grid is filled with data.

Consider Product & Due Date are the key columns. After entering the above data, the data is changed in the form & hence the SQL result changes as below

Product Due date Qty
Computer 10/08/2011 100
Computer 15/08/2011  60
 Product Due date Qty
Computer 10/08/2011 100
Computer 15/08/2011 60
Computer 10/09/2011 20

<- Data updated in the sub grid

The last row remains as it is.

Sub-grids are used to implement complex many to many relation ships between tables in a tstruct.