Editor - Data not saving and not showing some data

sameeragayan · September 2020

I'm using Datatables Editor and for some reason it is not saving the changes. I can see it's passing the new data but not save a them to table

here is editor_invoice_data_handler.php file

<?php

/*
 * Editor server script for DB table Report_Temp_Invoicing_data
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'PDMS.dbo.Report_Temp_Invoicing_data', 'PDMS.dbo.Report_Temp_Invoicing_data.ID' )
    ->debug( true )
    ->fields(
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Row_ID' ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Description' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Line_Net' )
            ->validator( Validate::notEmpty() )
            ->validator( Validate::numeric() ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.VAT_Rate' )
            ->validator( Validate::notEmpty() )
            ->validator( Validate::numeric() ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Owner' ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Project_Number' ),

        Field::inst( 'Tracker_EE.dbo.Projects.[Client Order Number]' ),
        Field::inst( 'Tracker_EE.dbo.Projects.[Estimate Total Project Value]' )
    )
    ->leftJoin('Tracker_EE.dbo.Projects','PDMS.dbo.Report_Temp_Invoicing_data.Project_Number', '=', 'Tracker_EE.dbo.Projects.[Project Number]')
    ->process( $_POST )
    ->json();

Javascript Document.ready section as follows:

 var editor = new $.fn.dataTable.Editor( {
                ajax: 'dataconnector/editor_invoice_data_handler.php',
                table: '#invoice-data',
                fields: [

                    {
                        "label": "Description:",
                        "name": "PDMS.dbo.Report_Temp_Invoicing_data.Description"
                    },
                    {
                        "label": "Line Net:",
                        "name": "PDMS.dbo.Report_Temp_Invoicing_data.Line_Net"
                    },
                    {
                        "label": "Vat Rate:",
                        "name": "PDMS.dbo.Report_Temp_Invoicing_data.VAT_Rate"
                    }
                ]
            } );

            $('#invoice-data').on( 'click', 'tbody td.editable', function (e) {
                editor.inline( this );
            } );



            var table = $('#invoice-data').DataTable( {
                dom: 'Bfrtip',
                ajax: 'dataconnector/editor_invoice_data_handler.php',
                order: [[ 0, 'asc' ]],
                paging:false,
                columns: [

                    {data: "PDMS.dbo.Report_Temp_Invoicing_data.Row_ID"},
                    {data: "PDMS.dbo.Report_Temp_Invoicing_data.Project_Number"},
                    {data: "PDMS.dbo.Report_Temp_Invoicing_data.Description", className: 'editable'},
                    {data: "Tracker_EE.dbo.Projects.[Estimate Total Project Value]"},
                    {data: "Tracker_EE.dbo.Projects.[Client Order Number]"},
                    {data: "PDMS.dbo.Report_Temp_Invoicing_data.Line_Net" , className: 'editable'},
                    {data: "PDMS.dbo.Report_Temp_Invoicing_data.VAT_Rate", className: 'editable'},
                    {data: null, render:function (data,type,row){
                            return (data.PDMS.dbo.Report_Temp_Invoicing_data.Line_Net*data.PDMS.dbo.Report_Temp_Invoicing_data.VAT_Rate)/100;
                        }},
                    {data: null, render:function(data,type,row){
                            return (data.PDMS.dbo.Report_Temp_Invoicing_data.Line_Net)+(data.PDMS.dbo.Report_Temp_Invoicing_data.Line_Net*data.PDMS.dbo.Report_Temp_Invoicing_data.VAT_Rate)/100;
                        }},
                ],
                select:true,
                buttons: [

                    { extend: 'edit',   editor: editor },
                    { extend: 'remove', editor: editor }
                ]
            } );

Updating was working ok before I Join the table.

Here is what i can see when I click on the update

And also, current table not showing data for two fields (marked in RED). Line highlighted in Green what i was trying to Edit.

Q1 - How to fix the data not saving problem?

Q2 - How to fix the 2 fields where not showing the data?

Any help would appreciate as I spend whole day to figure this out and still no luck

please note: I'm using two databases here to fetch the data. The two fields that are not showing the data has spaces on the field name and struggling to change it as the table has heavily used in other applications. I used generator to create scripts and then modified them to fit with the environment.

allan · September 2020

Could you add ->debug(true) immediately before the ->process(...) call. Then submit an edit and show me the response from the server (which will be in the Inspector's Network panel of your browser)?

I suspect it will be due to the use of PDMS.dbo. as a prefix, but I'm not sure.

Allan

sameeragayan · September 2020

Thanks for the respond. I just added the debug before process

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'PDMS.dbo.Report_Temp_Invoicing_data', 'PDMS.dbo.Report_Temp_Invoicing_data.ID' )
 
    ->fields(
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Row_ID' ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Description' )
            ->validator( Validate::notEmpty() ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Line_Net' )
            ->validator( Validate::notEmpty() )
            ->validator( Validate::numeric() ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.VAT_Rate' )
            ->validator( Validate::notEmpty() )
            ->validator( Validate::numeric() ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Owner' ),
        Field::inst( 'PDMS.dbo.Report_Temp_Invoicing_data.Project_Number' ),

        Field::inst( 'Tracker_EE.dbo.Projects.[Client Order Number]' ),
        Field::inst( 'Tracker_EE.dbo.Projects.[Estimate Total Project Value]' )
    )
    ->leftJoin('Tracker_EE.dbo.Projects','PDMS.dbo.Report_Temp_Invoicing_data.Project_Number', '=', 'Tracker_EE.dbo.Projects.[Project Number]')
    ->debug( true )
    ->process( $_POST )
    ->json();

here is the Network > Response for editor_invoice_data_handler.php file

{data: [{DT_RowId: "row_3", PDMS: {dbo: {,…}},…}], debug: [{,…}]}
data: [{DT_RowId: "row_3", PDMS: {dbo: {,…}},…}]
0: {DT_RowId: "row_3", PDMS: {dbo: {,…}},…}
DT_RowId: "row_3"
PDMS: {dbo: {,…}}
dbo: {,…}
Report_Temp_Invoicing_data: {Row_ID: 5, Description: "P-373170 14 Craigfryn (100348 - Mgt + TR Survey)", Line_Net: 250,…}
Description: "P-373170 14 Craigfryn (100348 - Mgt + TR Survey)"
Line_Net: 250
Owner: "sgayan"
Project_Number: "P-373170"
Row_ID: 5
VAT_Rate: 20
Tracker_EE: {dbo: {Projects: {[Client Order Number]: "A0140438", [Estimate Total Project Value]: 250}}}
dbo: {Projects: {[Client Order Number]: "A0140438", [Estimate Total Project Value]: 250}}
Projects: {[Client Order Number]: "A0140438", [Estimate Total Project Value]: 250}
[Client Order Number]: "A0140438"
[Estimate Total Project Value]: 250
debug: [{,…}]
0: {,…}
bindings: [{name: ":where_0", value: 3, type: null}]
0: {name: ":where_0", value: 3, type: null}
name: ":where_0"
type: null
value: 3
query: "SELECT  [PDMS].[dbo].[Report_Temp_Invoicing_data].[ID] as 'PDMS.dbo.Report_Temp_Invoicing_data.ID', [PDMS].[dbo].[Report_Temp_Invoicing_data].[Row_ID] as 'PDMS.dbo.Report_Temp_Invoicing_data.Row_ID', [PDMS].[dbo].[Report_Temp_Invoicing_data].[Description] as 'PDMS.dbo.Report_Temp_Invoicing_data.Description', [PDMS].[dbo].[Report_Temp_Invoicing_data].[Line_Net] as 'PDMS.dbo.Report_Temp_Invoicing_data.Line_Net', [PDMS].[dbo].[Report_Temp_Invoicing_data].[VAT_Rate] as 'PDMS.dbo.Report_Temp_Invoicing_data.VAT_Rate', [PDMS].[dbo].[Report_Temp_Invoicing_data].[Owner] as 'PDMS.dbo.Report_Temp_Invoicing_data.Owner', [PDMS].[dbo].[Report_Temp_Invoicing_data].[Project_Number] as 'PDMS.dbo.Report_Temp_Invoicing_data.Project_Number', Tracker_EE.dbo.Projects.[Client Order Number] as 'Tracker_EE.dbo.Projects.[Client Order Number]', Tracker_EE.dbo.Projects.[Estimate Total Project Value] as 'Tracker_EE.dbo.Projects.[Estimate Total Project Value]' FROM  [PDMS].[dbo].[Report_Temp_Invoicing_data] LEFT JOIN [Tracker_EE].[dbo].[Projects] ON [PDMS].[dbo].[Report_Temp_Invoicing_data].[Project_Number] = Tracker_EE.dbo.Projects.[Project Number] WHERE [PDMS].[dbo].[Report_Temp_Invoicing_data].[ID] = :where_0 "

here what Json look like

i just changed the same record as previously Line_Net from 250 to 350, should i see update query here and data pass to server? I cannot see and value 350 here.

sameeragayan · September 2020

Also when i run the SQL in the debug query on SQL query browser i get the result i expect

PDMS.dbo.Report_Temp_Invoicing_data.ID 3
PDMS.dbo.Report_Temp_Invoicing_data.Row_ID 5
PDMS.dbo.Report_Temp_Invoicing_data.Description P-373170 14 Craigfryn (100348 - Mgt + TR Survey)
PDMS.dbo.Report_Temp_Invoicing_data.Line_Net 250
PDMS.dbo.Report_Temp_Invoicing_data.VAT_Rate 20
PDMS.dbo.Report_Temp_Invoicing_data.Owner sgayan
PDMS.dbo.Report_Temp_Invoicing_data.Project_Number P-373170
Tracker_EE.dbo.Projects.[Client Order Number] A0140438
Tracker_EE.dbo.Projects.[Estimate Total Project Value] 250

allan · September 2020

Is that the query for the initial data load? It is the query for the edit action that I'm interested in here, since that is where the issue is. Could you show me the raw JSON response from the server when you perform an edit action please?

Allan

sameeragayan · September 2020

This is what i can see when i edit a record.

in this case I'm changing line_net from 255 to 350 and i can see the submit data carry this but there is no update query. instead of it run the select query and return all original value.

In this Example you can see this select query return the data for the 2 columns where they are not display on the table

sameeragayan · September 2020

When I remove the leftjoin data edit is working perfectly but then still i don't get the data for 2 columns (Project value and PO - where data coming from TRACKER_EE database.

I was trying to fix it by adding a function to fetch these Project Value and PO using ajax post but then i get a different message on the screen

here is the function for Project value after removing leftjoin.

                    {data: "Row_ID"},
                    {data: "Project_Number"},
                    {data: "Description", className: 'editable'},
                    {data: null, render:function (data, type, row){
                            let pn = data.Project_Number;
                            $.post('reports_invoice_calculation_functions.php',{find_project_val:1,pn:pn},function(info1) {
                                console.log(info1);
                                return  info1;
                            });


                        }},

this part give me a different error

https://datatables.net/manual/tech-notes/4

Editor - Data not saving and not showing some data

Editor - Data not saving and not showing some data

Answers

Howdy, Stranger!

Categories

DataTables

Editor - Data not saving and not showing some data

Editor - Data not saving and not showing some data

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables