Nested Object Data on Grouped Row

EastCoast2024

Hello,

I currently have two nested grouped rows that display a sales rep and their commission (Ex: Troy Bolton - 50% Commission) and the invoice numbers associated to that rep (Ex: INV00855).

I need critical information to be displayed within both grouped rows. I've already managed to populate the first grouped row with the Total Cost, Total Price, Gross Profit and Commission totals seen in the table's headers (which was working before I added the second grouped row), but, I need to show whether an invoice has one or multiple payment records tied to it (as seen with INV00096).

I'd like to create somewhat of a hierarchy of data, starting with the payment records and ending with the partner's totals. Essentially, I'd like to calculate the payment's Amount against the invoice's Gross Profit which would then be calculated to determine the invoice's Commission which would then be summed with all the invoices tied to that rep for a Commission grand total.

I'm using a drawCallback function I found online that I lightly changed to fit my needs and managed to create a new sub-grouped row for the invoice number. The issue I'm facing is in deconstructing the object that stores all the data necessary in my table for my second grouped row and whenever I'm calling it using api.column(n).data() on the table cell of the grouped row it's outputting [object Object]. See below:

"drawCallback": function (settings) {
    var api = this.api(); 
    var rows = api.rows({ page: 'current' }).nodes(); 
    var last = null; 
    var storedIndexArray = []; 
    const totalCostColumnIndex = 4; 
    const totalPriceColumnIndex = 5; 
    const totalGrossColumnIndex = 6;
    const amountPaidColumnIndex = 8;
    const totalCommissionColumnIndex = 9;

    function calculateColumnSum(startIndex, endIndex, columnIndex) {
        var sum = 0;
        for (var i = startIndex; i < endIndex; i++) {
            var value = $(rows).eq(i).find('td').eq(columnIndex).text();
            sum += parseFloat(value.replace('$', '').replace(',', '')) || 0;
        }
        return sum;
    }

    const currencyFormatter = new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: 'USD',
    });

    api.column(0, { page: 'current' }).data().each(function (group, i) {
        if (last !== group) {
            storedIndexArray.push(i);
            $(rows).eq(i).before(
                '<tr style="cursor: pointer;" class="group">' +
                    '<td colspan="4">' + group + '</td>' +
                    '<td><span class="group-total-cost"></span></td>' +
                    '<td><span class="group-total-price"></span></td>' +
                    '<td><span class="group-total-gross"></span></td>' +
                    '<td colspan="3"></td>' +
                    '<td><span class="group-amount-paid"></span></td>' +
                    '<td><span style="color:#036afb" class="group-total-commission"></span></td>' +
                    '<td></td>' +
                '</tr>'
            );
            last = group; 
        }
    });

    // The second grouped row I need to work
    api.column(4, { page: 'current' }).data().each(function (group, i) { 
        if (last !== group) {
            storedIndexArray.push(i)
            $(rows).eq(i).before(
                '<tr style="cursor: pointer;" class="group">' +
                    '<td>' + api.column(1).data()[2][2] + '</td>' + 
                    '<td colspan="2"></td>' + 
                    '<td><span style=visibility: visible;">' + group + '</span></td>' + 
                    '<td>' + api.column(5).data() + '</td>' +
                    '<td>' + api.column(6).data() + '</td>' +
                    '<td>' + api.column(7).data() + '</td>' +
                    '<td colspan="3"></td>' +
                    '<td>' + api.column(11).data() + '</td>' +
                    '<td>' + api.column(12).data() + '</td>' +
                    '<td>' + api.column(13).data() + '</td>' +
                '</tr>'
            );
            last = group;
        }
    });

    storedIndexArray.push(
        api.column(0, { page: 'current' }).data().length 
    );
    storedIndexArray.push(
        api.column(4, { page: 'current' }).data().length
    );

    for (var i = 0; i < storedIndexArray.length - 1; i++) {
        var groupStartIndex = storedIndexArray[i];
        var groupEndIndex = storedIndexArray[i + 1];
        var totalCostSum = totalPriceSum = totalGrossSum = amountPaidSum = totalCommissionSum = 0;

        totalCostSum = calculateColumnSum(groupStartIndex, groupEndIndex, totalCostColumnIndex);
        totalPriceSum = calculateColumnSum(groupStartIndex, groupEndIndex, totalPriceColumnIndex);
        totalGrossSum = calculateColumnSum(groupStartIndex, groupEndIndex, totalGrossColumnIndex);
        amountPaidSum = calculateColumnSum(groupStartIndex, groupEndIndex, amountPaidColumnIndex);
        totalCommissionSum = calculateColumnSum(groupStartIndex, groupEndIndex, totalCommissionColumnIndex);

        var totalCostElement = $(".group-total-cost")[i];
        var totalPriceElement = $(".group-total-price")[i];
        var totalGrossElement = $(".group-total-gross")[i];
        var amountPaidElement = $(".group-amount-paid")[i];
        var totalCommissionElement = $(".group-total-commission")[i];

        $(totalCostElement).text(currencyFormatter.format(totalCostSum));
        $(totalPriceElement).text(currencyFormatter.format(totalPriceSum));
        $(totalGrossElement).text(currencyFormatter.format(totalGrossSum));
        $(amountPaidElement).text(currencyFormatter.format(amountPaidSum));
        $(totalCommissionElement).text(currencyFormatter.format(totalCommissionSum));
    }
},

Any help is appreciated and I hope I'm doing this right. Thanks!

kthorngren

it's outputting [object Object]

Using api.column(5).data() will return an array of data which is causing the [object Object]. See the column().data() docs for details. I would look at using rows().every() with a row-selector as a function to match the sub group invoice number and first level group of the sub-group. The latter might not be necessary depending on whether the some invoice number is found multiple groups.. In the function use row().data(), ie var data = this.data();, to populate an array containing all the rows. Once the array is built you can perform your calculations for the tr being returned.

Maybe the RowGroup extension will work better for you. It supports multi-level grouping which I think you are describing above. See the row aggregates example. You can use rowGroup.startRender for the aggregates if you want.

If you need help with this please provide a simple test case with an example of your solution. This will allow us to provide more specific suggestions.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

EastCoast2024

Hello,

I'll look into implementing rows().every() into my drawCallback function but in the meantime, I'll provide you with a simple test case so that you can play around with it and, if possible, give me any more needed suggestions in improving my code.

Let me know if the test case is fine or if you need anything else from me.

Thanks!

kthorngren

I added rows().every() with an example of summing the Total Cost column.
https://jsfiddle.net/xckuL9gb/

I added this code:

           var totals = {
                cost: 0,
           }
                
           api.rows(function ( idx, data, node ) {
                    return data[4] === group ? true : false;
                    }).every( function ( rowIdx, tableLoop, rowLoop ) {
                        var data = this.data();
              
              totals.cost += Number(data[5]);
                        } );

Add the other columns to the totals object and save them in the loop.

The output looks like this:

'<td>' + totals.cost + '</td>' +

Kevin

EastCoast2024

I haven't been able to fully implement your solution to my program but I'll let you know how that goes as soon as I'm able.

Thank you for the help.