Implement excel style edit and auto update using ajax.reload() in a drawCallback

monkeyboy

Allen,

I am trying to provide a table which behaves somewhat like excel, in that if you edit a cell, other cells will update based on a formula. I implemented a php function which recalculates and updates all table elements which were impacted by the POST data

I then need to have this RUN as a post edit event. This failed, so I thought I could do an ajax.reload using a drawCallback, but I cannot get that to function either

I seem to be wrestling with 2 issues:

1

        var affordTable;
    $(document).ready(function() {
        var affordTable = $("#affordTable").DataTable( {
                        dom: "Bt",
            "drawCallback": function( settings ) {
                affordTable.ajax.reload();
            },
                       ......

results in: TypeError affordTable is undefined
What am i doing wrong?

In the server side
```
->on( 'postEdit', function ( $editor, $id, $values )
{
updateSubtotals();
})

Which calls php function to calculate and update subtotal columns in a mysql table, causes a
Lock wait timeout exceeded error in mysql (the query executes just fine from phpMyAdmin)

Any idea why this occurs?

kthorngren

For issue 1 I think the var affordTable assignment is not complete when the darwCallback function is built thus the undefined error. I think you will need to use this.api(), as shown in the drawCallback examples, to use ajax.reload().

I have no clue for issue 2

Kevin

allan

Calling ajax.reload() in the draw callback is going to leave you with an infinite loop since ajax.reload() will trigger a redraw, which...!

The key question is, are the calculations performed on the row, or the column (or both)? And are the client-side or server-side? If you are able to link to the page showing what you currently have, that would be really useful.

Allan

monkeyboy

OK - I did not think THAT through! infinite loop - oops

Unfortunately linking is not possible as this is not on a connected server
Perhaps an explanation of what I am attempting will help.

Calculations are done on the server side- triggered by POST-EDIT event
Calculations involve both rows and columns

I need to duplicate a report (which auto-updates from various sources) which was originally maintained in excel. It allows editing on SOME but not all fields
When an editable field is updated, the server performs calculations (post-edit event) updating several other fields in the table - then we need to re-display the entire table.

Example table:
Row# col1 col2 $sum col4 Text Column
Row1 $100 $200 $300 0 editable comment
Row2 $ 50 $ 60 $110 0 some other text
Subttl $150 $260 $410 0
Row4 48.5 55.8 $987 492 probably blank (not footer!)
More rows below.....

So for row 1,2,3 we sum to get the column data (Col 1,2 are editable)

Row 4 can be edited in col2 and sum columns. Col1 and col4 are complex calculations

So - what I really need is 3 techniques:
1) the entire table must update whenever something is edited
2) I need to mark specific cells as READONLY based on the row (eg. row4 $col1)
3) I need to render specific cells differently based on the row they are in

Thanks so much for your awesome support.

allan

1) the entire table must update whenever something is edited

Use submitComplete combined with the ajax.reload() method you proposed.

2) I need to mark specific cells as READONLY based on the row (eg. row4 $col1)

With inline editing, it can be done like in this example.

3) I need to render specific cells differently based on the row they are in

The columns.render method let's you provide a function for the renderer so you can customise it based on whatever logic you need.

Allan

monkeyboy

submitComplete - Brillaint - Thanks.

Regarding #2 and #3 (Sorry to be soooo dimwitted!)
How do I check (within my render and column defs functions) the specific ROW number and Column Number?

If I can determine that, I can either render or add editable class to specific cell locations

Thanks again!

allan

How do I check (within my render and column defs functions) the specific ROW number and Column Number?

The meta parameter that is passed into columns.render contains that information.

Allan

monkeyboy

Thanks - Here are my final functional Javascript code snippets for refreshing the whole table, so that server side updates (triggered by a php save event) will be reflected in the user's display:

        myEditor.on( "submitComplete", function (e) {
                myTable.ajax.reload(null,false);
            });

As you indicated above - subMitComplete avoids the infinite loop caused by ajax.reload

Stripe-ing a specific Row by ID:

    "rowCallback": function( row, data, index ) 
    {
        if ( data.tableName.id == 6 ||
            data.tableName.id == 11 ||) 
        {
            $("td", row).closest("tr").removeClass("even");
            $("td", row).closest("tr").removeClass("odd");
            $("td", row).closest("tr").css("background-color","#71c5e8");
            $("td", row).closest("tr").css("color","black");
            $("td", row).closest("tr").css("font-weight","bold");
            $("td", row).eq(1).css("color","Purple"); // change font color for 2nd column
        }

On the php side - I update calculated data in certain table cells with a postEdit Event call

        ->on( 'postEdit', function ( $editor, $id, $values ) 
            {
                updateCalculatedCells();
            })

And yes - the calculated cells could be handled on the client side, but the complexity, and need to reuse some of that data led me to decide that the cost of additional space in mySQL table was a small price to pay, to just have those calculated fields available to other applications (cleaner and more readable code - yay Brute force!)

Thanks for all the help getting this working

allan

Excellent - good to hear you got it working. Thanks for posting back!

Allan

monkeyboy

Enjoy the bench cookies

allan

Yes! Yes! Yes!

I've been meaning to ping you back about that and my apologies for the delay in doing so. That's really super kind of you - thank you. I haven't used them yet, but there have been several times this year already that I'd been thinking "this would be a lot easier if..."!

What kind of wood working do you do yourself? I'm sort of dabbling in several areas, but mainly focusing on trying to make some toys for my kids. Just started doing some dove tails as well, which is actually more fun that I had thought it would be .

Regards,
Allan

monkeyboy

Boxes, cabinetry, and attempts at many things. Built a custom shaker style kitchen for my mom.
Currently making a "dining" table for 2 for a friends tiny cabin, as well as a platform bed, and some Adirondack chairs.

Wife does turning (good thing because I stink at it)

Dovetails in many varieties are awesome with an INCRA jig. Incra is here in town, and I know the founder/inventor

wish I had more time for it, but working pays the bills - lol

allan

A custom shaker kitchen must have taken you a while! That's much bigger than what I've attempted so far.

I love the INCRA kit - I've got a few bits and bobs of theirs and I seriously appreciate how much thought they put into everything. Kudos to the founder!

Allan

monkeyboy

The kitchen was not hard - as it is one floating panel door after another (and another and another....) It took 6 months of weekends and evenings, as her kitchen is Texas sized!
I got quite good at making floating panel doors! lol

Patience in doing setups is the key (and thinking your process through to make a mass production work flow)

One of these days, I will see if this hold true for inlay work. The table will have some inlay in the top....