[Data Grid] How to group rows and columns?

Forge Component
(32)
Published on 4 Dec (2 days ago) by OutSystems R&D
32 votes
Published on 4 Dec (2 days ago) by OutSystems R&D

Is there a technic to achieve this layout 

             Store 1   Store 2   Store 3

Item 1    100             -             5

Item 2         8            77          -

Item 3       43             -            3


If we have JSON structure like this:
{
    "itemName": "Item 1",
    "Item id": 83495,
    "Store Id": 11869,
    "Store Name": "Store 1",
    "quantity": 100
}, ....



Mykola (Nick) Tkachenko wrote:

Is there a technic to achieve this layout 

             Store 1   Store 2   Store 3

Item 1    100             -             5

Item 2         8            77          -

Item 3       43             -            3


If we have JSON structure like this:
{
    "itemName": "Item 1",
    "Item id": 83495,
    "Store Id": 11869,
    "Store Name": "Store 1",
    "quantity": 100
}, ....

There are a few way to do this within in the grid:

I have used the Conditional Styling's itemFormat to change the bound cell value based on other bound cell values in the given row but it should work cross row as well.  The itemFormat will get you half way there, but does not cover the any secondary instance rows of a particular ItemName value. The missing half might need require that you abandon the itemFormat entirely, but the code example below can be tweaked in the appropriate wijmo event.

Create 5 columns (Item #, Store 1-3, Store Name)

  column 0 binding: 'ItemName', heading: ' '  // space in single quotes

  column 1 binding: 'quantity', heading: 'Store 1'

  column 2 binding: 'quantity', heading: 'Store 2'

  column 3 binding: 'quantity', heading: 'Store 3'

  column 4 binding: 'Store Name', visible: false,

Set grid's itemFormat

grid.itemFormat = myItemFormatter;

Code for myItemFormatter

function myItemFormatter(panel, r, c, cell) {

    if (r > 0) {
        let nr;
        let rc0 = panel.getCellValue(r, 0);

        if (rc0 === 'Item 1') {
            nr = 1;
        }

        else if (rc0 === 'Item 2') {
            nr = 2;
        }

        else if (rc0 === 'Item 3') {
            nr = 3;
        }

        if (c >= 1 && c <= 3) {
            if (nr !== r) {

                if (panel.getCellValue(r, 4, false) === panel.getCellValue(0, c, false)){
                    panel.setCellValue(nr, c, panel.getCellValue(r, c, false));
                }
                else {
                    cell.innerHTML = '-';
                }
                // Todo - need code (some place) to remove rows greater than 4 
            }
            else if (panel.getCellValue(r, 4, false) !== panel.getCellValue(0, c, false){
                cell.innerHTML = '-';
            }
        }
    }
}


             Store 1   Store 2   Store 3

Item 1    100             -            5

Item 2         8            77          -

Item 3       43             -           3

Item 1         -              -           5

Item 2         -            77          -

Item 3         -              -           3

I believe all these values can be set using the AdvancedFormat properties of the Grid/Column without issue.  I have some issues with some properties (autoMerge) being eaten by OutSystems wrapper around the wijmo grid however.