Replies

• allan Posts: 63,542Questions: 1Answers: 10,476 Site admin

  December 2017

  If you are trying to edit data of joined tables, you need to also submit the joined table's primary id column data. That will let the server-side libraries know which row in the joined tables needs to be updated.

  Allan
• JuanPi Posts: 4Questions: 0Answers: 0

  December 2017

  Sorry for the late response but yes you were right, thanks for the help
• nicontrols Posts: 32Questions: 16Answers: 1

  November 2018

  Hi! I am suffering the same problem. Where do you submit the joined table primary id?
• allan Posts: 63,542Questions: 1Answers: 10,476 Site admin

  November 2018

  Include it in your Editor form as a hidden field type.

  Allan
• nicontrols Posts: 32Questions: 16Answers: 1

  November 2018

  Thanks Allan.

  I still can't get this to work - maybe I'm missing something obvious.

  On the server side, I have joined 3 tables like so:

          Editor::inst( $db, 'enqitem', 'enqitemid')
          ->fields(
              Field::inst( 'stock.salmasid' ),
              Field::inst( 'stock.partnum' ),
              Field::inst( 'stockitem.salsubid' ),
              Field::inst( 'stockitem.condition1' ),
              Field::inst( 'stockitem.supplier' ),
              Field::inst( 'enqitem.enqitemid' ),
              Field::inst( 'enqitem.cost' ),
              Field::inst( 'enqitem.sale' ),
              Field::inst( 'enqitem.qty' ),
              Field::inst( 'enqitem.linedisc' ),
              Field::inst( 'enqitem.leadtime' )            
          )
          ->leftJoin('stock', 'stock.salmasid', '=', 'enqitem.itemmasid')
          ->leftJoin('stockitem', 'stockitem.salsubid', '=', 'enqitem.itemid')
          ->where('enqitem.enqnr',141316)
          ->debug( true )
          ->process( $_POST )
          ->json();
  

  Then in the editor I have the following:

              let editor = new $.fn.dataTable.Editor( {
                  ajax: {
                      url: "ajax.php",
                      type: "POST"
                  },
                  
                  table: "#dataTableSites",
                  fields: [{
                          label: "ID:",
                          name: "stock.salmasid",
                          type: "hidden"
                      },{
                          label: "ID:",
                          name: "stockitem.salsubid",
                          type: "hidden"
                      },{
                          label: "ID:",
                          name: "enqitem.enqitemid",
                          type: "hidden"
                      },{
                          label: "Part Number:",
                          name: "stock.partnum"
                      },{
                          label: "Condition:",
                          name: "stockitem.condition1",
                          type: "select",
                          options: [
                              { label: "New", value: "New" },
                              { label: "Used", value: "Used" },
                              { label: "Repair", value: "Repair" }
                          ]
                      },{
                          label: "Supplier:",
                          name: "stockitem.supplier",
                          type: "select"
                      },{
                          label: "Cost:",
                          name: "enqitem.cost"
                      },{
                          label: "Sale:",
                          name: "enqitem.sale"
                      },{
                          label: "Qty:",
                          name: "enqitem.qty"
                      },{
                          label: "Disc:",
                          name: "enqitem.linedisc"
                      },{
                          label: "Delivery Time:",
                          name: "enqitem.leadtime"
                      }
                  ]
              } );
  

  The data displays perfectly, however when I try to update a field I get the following error:

  An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'enqitem.enqitemid' in 'field list'

  In the debug I can see the following:

  {"query":"SELECT  `enqitem`.`enqitemid` as 'enqitem.enqitemid' FROM  `stock` WHERE `salmasid` = :where_0....
  

  This is where it fails, as enqitemid doesn't exist in the stock table.

  What have I messed up?
• allan Posts: 63,542Questions: 1Answers: 10,476 Site admin

  November 2018

  Ah no - that isn't you messing up, its me! That's a bug in 1.8.0 I'm afraid. This commit fixes it and that will be released in 1.8.1 (very soon), or you can grab the updated file from the repo.

  Allan
• nicontrols Posts: 32Questions: 16Answers: 1

  November 2018

  Nice one Allan - thats fixed it!

  Your support on here is incredible - appreciate the quick responses.
  Cheers mate.