How to add a row for percentage calculate of column and calculate data depending on value

majamuel

Hi there,

i have to build a datatable where User can calculate the values depending on a percentage selector.

Data comes from a database and is rendered once the page is loaded.
From this on, if the user changes the selector usage of one column, the whole column should recalculate its values by the selected percentage...
if the user changes it again, the value should calculate again on base of the value from database..
I hope you understand what i mean.... ;-))))

rf1234

just recalculate everything on "draw" or whatever event is suitable for you.
https://datatables.net/reference/event/draw

To loop through all of the table rows I would use "rows().every()".
https://datatables.net/reference/api/rows().every()

I do something similar with an accumulator that is recalculated every time new data is loaded or the user changes the sort order etc. Works fine.

majamuel

Hi,
Thanks for your answers, but I don‘t know how to use them. ;-)
I want that every column with data has a selector in thread where you can choose a percentage... on changing this selector, the column under this selector should recalculate it’s data from the original data from database with the selected percentage... I try to make a fiddle to explain my needs ...
Thanks for all helps

majamuel

Happy Easter everybody...

i´ve made a fiddle to show my needs..
https://jsfiddle.net/y7eLdwgu/7/

You see a header with 3 Locations.
The first column shows different products...
The second column is a sum column for products.

The second Row is for choosing a percentage need for the location and the product of this row...

What i need... ;-)

• On Changing the Selector -> the values in the whole Column should get calculated on the origin Value of the Column. If User changes it again, the calculation should base everytime on the origin Value of the cell..

• the sum Column should recalculate everytime a select has changed...

• the order arrows should only assign in the first row

I have tried many things... but with no success... :-( I´m not very familar with jquery and javascript... so i hope, i can learn it by any help ....

Thanks a lot ... and stay healthy....

colin

You would need a click event for the select element, then you would use column().data() and change the values for each cell.

Colin

rf1234

I tried to make it work. But I am not used to working with those fiddles and with DOM sourced data tables either. The logic I implemented in this should work, but it doesn't. In previous versions I had this in the individual field renderers and it worked like a charm. It just doesn't want to work in rows().every(). I have the same logic implemented somewhere in my application and it works. Maybe someone can take a look please. The "draw" event doesn't get triggered either after the table has been initialized. The jQuery change event handler works but then still nothing happens in the "draw" event handler. Mmmmhhh...

https://jsfiddle.net/c4s7zb2x/19/

    table.on('draw', function() {
        table.rows().every( function (rowIdx, tableLoop, rowLoop) {
          var data = this.data();
            data.Sum = ( $('#locA').val()/100 * data.LocationA_dbval ) +
                         ( $('#locB').val()/100 * data.LocationB_dbval ) +
                         ( $('#locC').val()/100 * data.LocationC_dbval );
          data.LocationA = $('#locA').val()/100 * data.LocationA_dbval;
          data.LocationB = $('#locB').val()/100 * data.LocationB_dbval;
          data.LocationC = $('#locC').val()/100 * data.LocationC_dbval;
          this.invalidate();
      } );
    });
    $('.locSel').change(function() {
            table.draw();         
    } );

rf1234

Just used the debugger to figure this out. rows().every() seems to work fine. I set the percentage for location A to 50% and as you can see from the screen shot the value of location A is correctly calculated and is written to the respective row. So the problem seems to be with something else caused by the fiddle or whatever.

No idea why "draw" isn't triggered after table initialization and why "draw" doesn't return anything after being triggered from the "change" event handler ...

kthorngren

When using DOM based table the table initialization is synchronous and the draw event initialization takes place after the initial table draw. In this case you will need to initialize the event first. See this example:
http://live.datatables.net/qemibose/1/edit

I wouldn't use the draw event for this as it will run each time the table is searched and sorted. I would place the code in the change event and only run it when there are changes. Plus it will need to be executed in initComplete. Also I prefer to use row().data() over row().invalidate(). After the loop use draw() to update the table for sorting and searching. Updated example:
https://jsfiddle.net/ajtrmLyx/1/

Kevin

rf1234

Many thanks, Kevin! You know this really, really well!

This was my first DOM sourced data table and I bet it will be my last! It felt like starting from scratch again ...

@majamuel So you have a working example now. Since you are intending to use this with a database and not DOM sourced you can also use my version of the fiddle because it should probably work when it is ajax sourced. It seems that "this.invalidate()" doesn't work at all with a DOM sourced table. Also the events on "init" etc. don't really work with a synchronous DOM sourced table. I wasn't aware of this at all.

When you implement this with an ajax data source I would recommend you select the fields twice (as aliases for example) so that you keep the fields with the original db values available without having to ajax.reload() the table.

I did that in the DOM sourced example as well since I have all the field values in the hidden fields with the suffix "_dbval". When the table is ajax sourced you don't need the hidden fields in the HTML because you will have them available in the respective row anyway.

majamuel

Hi Kevin and rf1234....

many, many thanks for the working fiddle... I try to understand and learn from it...
I get the data once from database... They are fix and don´t change in a session so that there is no need to get them by ajax.... I use the cakePhP Framework for the whole page... I get the data and put in the view....and can then use it dom sourced..
So your fiddle really, really helps me...
I hope that i´m soon be able to help others, to use this great Tool....
Have a nice day together...

rf1234

Gern geschehen! Und wir brauchen immer Leute, die helfen wollen! Schöne Rest-Ostern noch.

Roland

majamuel

Und ich breche mir einen mit meinem Schulenglisch ab... ;-))))

Bis demnächst mal...

Marc

majamuel

Ah... i understand the way...
it´s easier than i expected...
But now i have the next problem...
If the site is build, the number of products and locations is variable...
Can i make the whole thing a little more flexible?

majamuel

I think i have to do it on another way..
if a selector is changed, i have to know in which column the selector is...
this column should be recalculated...
for the calculation there should be the next hidden column with the db-Values...
At last the sum column should get recalculated...

Sounds easy... :-| i´ll try it... ;-)

rf1234

Yes, should be easy. Another product is just another row so that is dynamic anyway if your table is ajax sourced. If you have an ajax source and you use this to fill the DOM (and hence keep the Data Table as DOM sourced) you could dynamically generate more th's, tr's and td's using jQuery.
If the ajax source may also vary regarding the number of locations (your columns) you could also generate the HTML dynamically using jQuery.

"if a selector is changed, i have to know in which column the selector is...
this column should be recalculated..."

of course you could that. For the sake of simplicity I simply recalculated everything regardless of what column's select field was changed. This won't cause a performance issue I guess. Rather generating many DOM rows and columns using jQuery may cause performance issues.

You are new to jQuery, right? I think I read it somewhere. So what you need to do to is loop through your ajax data and generate additional DOM table rows and columns. More rows are easy; to add new columns dynamically requires a little more work:
If you generate additional columns you would also need to append them to the Data Table dynamically.
Or you would manipulate the Data Table definition even BEFORE the Data Table gets initialized. Using a promise or a function call on "success" of the ajax server call.

the jQuery commands you are likely to need are $('selector').append() and .prepend

Just found this again: https://datatables.net/forums/discussion/comment/156503/#Comment_156503

You can't dynamically add columns after Data Table initialization. So you would need to do it before initialization which is feasible if you fill the DOM table first, then generate the code for the additional Data Table columns BEFORE eventually initializing the data table.

Sounds like an interesting use case!

majamuel

I´ve did it.... I think, it could be done with less code, but at leas it works... ;-)
Solution...
i gave every selector an id which is equal to the column number,
after every Value-column is a hidden Column with the dbvalues...

On change... i recalculated the column and the sum column

    $('.perc_sel').change(function() {
        var value = this.value;   
        var valueColumn = $(this).attr('id');
        var dataColumn = parseInt(valueColumn) +1

        table2.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
            var data = this.data();
            var wert = data[dataColumn].replace('.', '').replace(',','.')
            data[valueColumn] = wert * value / 100 ;
            data[valueColumn] = new Intl.NumberFormat('de-DE',{'minimumFractionDigits':2}).format(data[valueColumn])
            this.row(rowIdx).data(data);
            table2.draw();
        } );

        table2.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
            var data = this.data();
            var sum = 0
             for (i = 3; i < data.length; i+=2) {
                sum = sum + parseFloat(data[i].replace('.', '').replace(',','.'))
             }  

            data[2] = new Intl.NumberFormat('de-DE',{'minimumFractionDigits':2}).format(sum)
            this.row(rowIdx).data(data);
            table2.draw();

        } );


    } );

`

kthorngren

A couple suggestions; 1 place all the code in one loop to make more efficient and 2 place table2.draw() outside the loop so it it executed once. Something like this:

$('.perc_sel').change(function() {
    var value = this.value;  
    var valueColumn = $(this).attr('id');
    var dataColumn = parseInt(valueColumn) +1
 
    table2.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
        var data = this.data();
        var wert = data[dataColumn].replace('.', '').replace(',','.')
        data[valueColumn] = wert * value / 100 ;
        data[valueColumn] = new Intl.NumberFormat('de-DE',{'minimumFractionDigits':2}).format(data[valueColumn])

        var sum = 0
         for (i = 3; i < data.length; i+=2) {
            sum = sum + parseFloat(data[i].replace('.', '').replace(',','.'))
         } 
 
        data[2] = new Intl.NumberFormat('de-DE',{'minimumFractionDigits':2}).format(sum)

        this.row(rowIdx).data(data);
       
    } );
 
   table2.draw();
 
} );

Kevin

majamuel

@Kevin... thanks... that works of course too... i really have to look, that i don´t repeat myself.... ;-))

@Roland.... the use-case is really interesting in these times...
It´s for an overview of medical articles for the fire-department...Till now it was an Excel-Sheet... But now more people have to know where and how much some handgloves und desinfection materials are.... so we need to create an webfront....

Last thing is, that i have now to save the selected percentages in an Cookie, that they are preselected if the site is entered...

rf1234

That you save them in a cookie means they are user specific. Why don't you save all of this in a MySQL database and use Editor to do all the CRUD operations? Then it will be device independent and the users can use it on their mobiles and other devices as well.

It would be these tables for example:
- location (id, name, etc.)
- product (id, name, etc.)
- stock (id, product_id, location_id, amount, etc.)
- location_user_percentage (id, location_id, user_id, percentage)

You'll probably have a "user" table anyway so I assumed there is a foreign key for this in user_percentage.