How to get Grouped Row Totals from Nested Grouped Row Totals?

EastCoast2024

Hello,

I'm currently working with a table that contains two grouped groups that I was able to make work using the solution provided to me in this thread.

The table's functionality is almost completed but the issue I'm facing is when there's more than one tableRow within each invoice_group, the partner_group's totals are being read from the tableRows instead of the invoice_group grouped row (see the screenshot above). The issue is causing the Total Cost, Total Price, Gross Profit, and Commission to be inaccurately calculated whenever the invoice_group has more than one transaction associated to the invoice.

I'll provide a simple test case for your convenience and let me know if you need anything else.

Thanks!

kthorngren

I didn't dig in to make sure everything is correct but I think the problem might be that you need to reset last before the second loop, for example:

      // Reset `last` for the next loop
      last  = '';
      
      api.column(4, { page: 'current' }).data().each(function (group, i) {

I updated the test case and the totals look different:
https://jsfiddle.net/m8dzgwx1/

Does this fix the issue?

Kevin

EastCoast2024

Hello,

Thank you for your assistance but your suggestion, unfortunately, didn't fix the issue.

Basically, I need the partner_group grouped row totals to be calculated from the cells highlighted in yellow whilst the table is currently calculating the cells highlighted in green. So, when there's more than one row to an invoice_group grouped row, then it'll calculate the totals twice (or how many times a transaction is tied to that invoice). See INV0000156 and how the partner_group grouped row calculates it as $100 and not $50 ($340 + $50 = $390).

Let me know if you need any more information and I'll happily be of service.

Thanks!

kthorngren

Sounds like it will take a bit of digging on our part to understand our code. I don't have time at the moment. In the meantime try using the browser's debugger and step through the drawCallback code to see if you can find the error. If I get a chance soon I will take a deeper look at your test case.

Kevin

kthorngren

I think I understand the requirement but might not understand your code. I think the problem is starting here:

totalCostSum = calculateColumnSum(groupStartIndex, groupEndIndex, totalCostColumnIndex);

Without digging into the code I believe groupStartIndex and groupEndIndex contain index ranges for all the rows in the group. If I understand correctly you don't want to sum all the rows in this group but only want to sum one total cost for each invoice number.

For this I would look at passing a forth boolean parameter to the calculateColumnSum() function to determine if all rows or summed or only on invoice number change. Then do something like you are doing with if (last !== group) { in that function. This should allow for waht you have now with summing all rows or adding to the sum when the invoice number changes.

I may be off base with this. If so maybe you can provide more clarity about your code so we know where to dive in and look. Better may be to simplify your test case to eliminate code we don't need to look at.

Kevin

EastCoast2024

Hey,

Sorry for the delay.

Yes. I want to sum the partner_group totals based on the the totals for each invoice number. My table is structured like so:

I actually got the mentioned code block from an online resource but I believe that it does exactly what you're trying to suggest with the fourth boolean parameter in the calculateColumnSum() function. It basically loops through all the tableRows for every partner and sums the totals for each.

I tried implementing your suggestion by adding a boolean parameter to the calculateColumnSum() function and it (sorta) works. It sums the first row of each invoice_group but the only issue in that case, would be with the Payment Amount column (since it's not a constant value for each tableRow) which needs to calculate all columns in the grouped row.

I fixed that by removing the sumOnInvoiceChange parameter from the function call for the Payment Amount field and it works like before. So what's in the test case I linked above should be an accurate showcase of how it should look and function.

Sidenote: Can you explain to me what adding if (last !== group) {} would do in the function? And whether my exclusion of it would affect my current setup I have going on?

Lastly, I had some fears that implementing this fix would affect my getCommissionAmount() function that's calculated before drawing the table.

function getCommissionAmount(combinedMap, combinedResults, totalCost, totalPrice) {
    Object.keys(combinedMap).forEach((invoiceIntId) => {
      var result = combinedMap[invoiceIntId];
      if (result.partnerCommission) {
        result.payments.forEach((payment) => {
          if (payment.isCreditMemo) {
            if (payment.paymentAmount >= result.totalPrice) {
              if (result.partnerIntId == 6543 || result.partnerIntId == 1234) {
                result.commissionAmount = 0;
              } else {
                result.commissionAmount += (result.totalCost * result.partnerCommission) * -1;
              }
            } else {
              result.commissionAmount = (result.grossProfit * result.partnerCommission) - (payment.paymentAmount * result.partnerCommission);
            }
          } else if (result.partnerIntId === 78945) {
            result.commissionAmount = result.totalPrice * result.partnerCommission;
          } else {
            result.commissionAmount = result.commissionAmount;
          }
        });
      }
      combinedResults.push(result);
    });
  }

But, I don't think so since the commission is constant within the table and therefore, should not cause any trouble.

Do you have any additional notes I should look at that may improve this?

Thanks!

kthorngren

Sidenote: Can you explain to me what adding if (last !== group) {} would do in the function?

I think you implemented the function as I suggested. I was just using if (last !== group) as an example of the if statement needed. You used if (sumOnInvoiceChange) { which looks like the correct result.

but the only issue in that case, would be with the Payment Amount column (since it's not a constant value for each tableRow) which needs to calculate all columns in the grouped row.

In that case pass false for that parameter so it sums all the rows in the group. Seems like you did that as it looks correct.

Do you have any additional notes I should look at that may improve this?

No, if its working then I don't have further input

Kevin

EastCoast2024

Sounds good. Thank you so much for your help!