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

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

monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0

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?

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0

    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.

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0

    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!

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin
    Answer ✓

    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

  • monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0
    edited February 2017

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

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

    Allan

  • monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0

    Enjoy the bench cookies

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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 :smile:.

    Regards,
    Allan

  • monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    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

  • monkeyboymonkeyboy Posts: 60Questions: 19Answers: 0

    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....

This discussion has been closed.