Percentage and Amount mutual dependent calculated columns

vicjish

Hi,
I have three columns in my table, TOTAL AMOUNT,DISCOUNT PERCENTAGE,DISCOUNT AMOUNT.
The disc_amount, and disc_percentage are calculated columns, the calculation should be made on run time while editing, so i used column render function to achieve this, my column render function is stated below.

    {
      "targets": [6], // **For Discount Percentage**
      "render": function(data, type, row) {
        debugger;
        var amount1=0;
        if (data != '' && data != row.DISCPERCENT) {
          amount1 = Math.round((data * (row.TOTAL)) / 100);
          row.DISC_AMT = amount1;


        }
 return amount1;
      }
    }


            {
              "targets": [7], // **Discount Amount**
              "render": function(data, type, row) {
                debugger;
                    var amount1=0;
                if (data != '' && data != row.DISC_AMT) {
                  amount1 = Math.round((data / (row.TOTAL)) * 100);

                  row.DISCPERCENT = amount1;
                }
         return amount1;
              }
            }

But its not working as expected, am i missing something?

kthorngren

if (data != '' && data != row.DISCPERCENT) {

Not sure what this comparison is so its hard to say what is happening. Without seeing the row data it will be difficult to offer suggestions.

One problem is the render function always needs to return something. Column 6 is returning amount1 only inside the if statement. You also need to return something, data, maybe, if the statements in theif` are not executed. And it doesn't look like column 7 is returning anything.

If you are still having problems please build a simple test case we can look at to help you debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

vicjish

Kevin, sorry for the incomplete question, as a newbie please excuse, Coming to the point, updated my question, and the issue is how the render function works, If i change the value in disc_percent column all the render functions got executing, this is how its works? if yes then two calculated columns depends each other can't be done through datatable?

kthorngren

Generically I would say that having two columns relying on each other for calculations is not going to work. In Excel this would be Circular Reference and not recommended. Instead of changing the actual data maybe you can take advantage of Orthogonal Data and perform the calculations for the display type.

If i change the value in disc_percent column all the render functions got executing, this is how its works?

That depends on how you are making changes. If you are suing Datatables API's to update the table then when performing a draw() then yes the render function should run. Are you using Datatables Editor to for the updates?

Again it would be easier to help if you could put together a simple example representing your data and Datatable config. You don't need ajax data or anything like that. Just a Datatable showing an example of your data and how you are updating the table.

Kevin

vicjish

HI kevin,
Am using datatable editor trail version with that, I dont know how to put this code in fiddler/code pen. Please help me with that i will put my code in fiddler/code pen.

colin

Hi @vicjish ,

Could you this here as a template, please: http://live.datatables.net/tiwibido/1/edit

Cheers,

Colin

vicjish

Hi,
Please find the below link this is what am try to do
http://live.datatables.net/tiwibido/4/edit

colin

Hi @vicjish ,

That should work fine. I've added two columns into the table, the QTY1 and RATE, since you were using those and they weren't part of the dataset, and this table is DOM sourced. If you're getting you're data from Ajax, you just need to ensure those two fields are part of the returned data.

Hope that helps,

Cheers,

Colin