Editor - Data not saving and not showing some data

Editor - Data not saving and not showing some data

sameeragayansameeragayan Posts: 5Questions: 1Answers: 0

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

Answers

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

    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

  • sameeragayansameeragayan Posts: 5Questions: 1Answers: 0

    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.

  • sameeragayansameeragayan Posts: 5Questions: 1Answers: 0

    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

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

    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

  • sameeragayansameeragayan Posts: 5Questions: 1Answers: 0

    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

  • sameeragayansameeragayan Posts: 5Questions: 1Answers: 0

    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

This discussion has been closed.