Editor Create and Edit Same Time

vincmeister

Hello,
Is it possible to create new record and edit old record on the same time?

I want to record edit process. When a user edit a record, it will create new record, and the edited record will marked as edited.

I'm using create button with default value triggered from selected row. After a user press the create button, new record will added based on old data. (This already done)
In the same time, the old data will update edited column as yes (Need help on this)

Please advise, thank you

allan

In the same table, or logging the new record to a different table based on the data submitted?

Allan

vincmeister

in the same table Allan

allan

Thanks for the clarification - that isn't something that Editor currently supports I'm afraid. You can use the server-side events to have it create a new record (which I guess you've already done), but there is no option to have Editor read that extra new row and return it.

You could potentially use the Editor->data() method to get the data that would be returned in the JSON and then add the extra row to the data property - I think that Editor should then add the extra row due to its multi-row editing support, but that isn't something I've explicitly tested for.

If that doesn't work you'd need to reload the table's data (ajax.reload()) in a submitComplete event handler.

Allan

vincmeister

Hi Allan,
Thank you for your information. I don't really understand with your suggestion. Is it possible to use something like this?

trTransferDataEditor.on( 'submitSuccess', function () {
                var detail_id = trTransferDataTable.row( { selected: true } ).data().tr_receiving_detail1.id 
                ajax: "function/tes.php?detail_id=" + detail_id
                trTransferDataTable.ajax.reload();
            } );

Get the old id on select, then pass it to tes.php SQL UPDATE
I'm trying those, but no luck. Please advise, thank you

allan

Basically that - yes. Lines 2 and 3 shouldn't be required (indeed, they aren't valid Javascript). Just a call to ajax.reload() will reload the table in its current state.

Allan

vincmeister

Hello Allan,

I'm using line 2 to get the detail_id (primary key) for the old data row, that i want to edit.

I mean, after create new record with the basic editor, I'm trying to use manual SQL Updated using file tes.php and pass the var detail_id to $php detail_id

So i'm thinking to use the submitSuccess event to manual edit the old row using the code above.

My tes.php is:

    <?php
        session_start();
        include ('../include/connect.php');
        $detail_id = $_GET['detail_id'];
        echo $detail_id;
        $sql = 'UPDATE tr_receiving_detail1 SET changes=1 WHERE id=' . $detail_id;
        $conn->query($sql);
        $conn->close();
    ?>

note: changes=1 means the old record set to edit

Unfortunately not working. Please help, thank you

Danny

allan

Can you give me a link to the page so I can take a look at what is going on and help to debug the issue please.

Allan

vincmeister

Please visit this link

on that example, ID #3 is a new record from #1 (see old ID column), which created from transfer button (using the create function on editor)
what i want is after submitSuccess, the changes column on ID #1 will edited to 1 (which means edited)

So i'm trying to update the changes column using code above.
Please assist, thank you

Danny

allan

Hi Danny,

Rather than using another Ajax request to increase the Changes count, I would suggest using a server-side event - postCreate in this case. That will let you run your UPDATE method in the same request that Editor makes to create the new row.

When the ajax.reload() call really should be as simple as:

trTransferDataEditor.on( 'submitSuccess', function () {
      trTransferDataTable.ajax.reload();
} );

Allan

vincmeister

Hi Allan,

Following your suggestion and a little search, I found this link and tring to adapt to my case by adding this code

->on('postCreate',function( $editor, $id, $values, $row ) {
        var_export( $id );
            $editor->db()
                ->query('update', 'tr_receiving_detail1')
                ->set('tr_receiving_detail1.changes',1)
                ->where('tr_receiving_detail1.id','tr_receiving_detail1.old_id')
                ->exec();
        })

but still not working.

Response:

'5'{"data":[{"DT_RowId":"row_5","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"5","item_code":"1","quantity":"1000","customer_id":"2","enduser_code":"E5","enduser_name":"UNKNOWN","changes":"0","old_id":"1","tr_in_id":"1"},"master_item":{"item_name":"BAG"},"master_customer":{"customer_name":"LEBRON JAMES"}}]}

Please help, thank you

Danny

allan

'5'

That is making the returned data invalid JSON. Are you able to remove that?

Allan

vincmeister

i think if comes from var_export( $id )
after i remove that line, it's working, but stil not updating the old row. the changes field is not set to 1.

Please help, thank you

allan

Hi Danny,

Thanks for the clarification. So my understanding of what should be happening is that your postCreate function in PHP should be updating the changes column in the database. First question: is it?

Then, there should be a submitSuccess event handler, like the one I suggested above, on the client-side that will cause the table to reload with the new data. Second question: Is that event handler present, and is it executing?

Allan

vincmeister

Hi Allan,

I already put this code as your suggestion

trTransferDataEditor.on( 'submitSuccess', function () {
                trTransferDataTable.ajax.reload();
            } );

I think my problem is coming from MySQL that not updated.
If i checked the MySQL Table, changes column still 0, not updated to 1

Please see this image
Blue circle should be '1'
thank you

allan

Does the browser's developer tools show that the data for the table is being reloaded?

If you access the database directly, does it show if changes has been altered?

Is 'tr_receiving_detail1.id === "tr_receiving_detail1.old_id" what is required? I suspect you actually need:

->where('tr_receiving_detail1.id','tr_receiving_detail1.old_id', '=', false)

See the documentation here.

Allan

vincmeister

As your suggestion, i'm changing my code to this one, but no luck. the changes won't altered.

->where('tr_receiving_detail1.id','tr_receiving_detail1.old_id', '=', false)

I don't really understand with your question

Does the browser's developer tools show that the data for the table is being reloaded?

But I looked into the Developer's Response, after the create button pressed, there are 2 transfer.php

{"data":[{"DT_RowId":"row_9","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"9","item_code":"2","quantity":"500","customer_id":"4","enduser_code":"E7","enduser_name":"HAWKEYE","changes":"0","old_id":"2","tr_in_id":"1"},"master_item":{"item_name":"HAT"},"master_customer":{"customer_name":"STEPHEN CURRY"}}]}

{"data":[{"DT_RowId":"row_1","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"1","item_code":"1","quantity":"1000","customer_id":"1","enduser_code":"E1","enduser_name":"CAPTAIN AMERICA","changes":"0","old_id":"0","tr_in_id":"1"},"master_item":{"item_name":"BAG"},"master_customer":{"customer_name":"KOBE BRYANT"}},{"DT_RowId":"row_2","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"2","item_code":"2","quantity":"500","customer_id":"1","enduser_code":"E1","enduser_name":"CAPTAIN AMERICA","changes":"0","old_id":"0","tr_in_id":"1"},"master_item":{"item_name":"HAT"},"master_customer":{"customer_name":"KOBE BRYANT"}},{"DT_RowId":"row_3","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"3","item_code":"1","quantity":"1000","customer_id":"3","enduser_code":"E2","enduser_name":"BLACK WIDOW","changes":"0","old_id":"0","tr_in_id":"1"},"master_item":{"item_name":"BAG"},"master_customer":{"customer_name":"KEVIN DURANT"}},{"DT_RowId":"row_4","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"4","item_code":"2","quantity":"300","customer_id":"2","enduser_code":"E3","enduser_name":"IRON MAN","changes":"0","old_id":"0","tr_in_id":"1"},"master_item":{"item_name":"HAT"},"master_customer":{"customer_name":"LEBRON JAMES"}},{"DT_RowId":"row_5","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"5","item_code":"1","quantity":"1000","customer_id":"2","enduser_code":"E5","enduser_name":"HULK","changes":"0","old_id":"0","tr_in_id":"1"},"master_item":{"item_name":"BAG"},"master_customer":{"customer_name":"LEBRON JAMES"}},{"DT_RowId":"row_6","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"6","item_code":"1","quantity":"1000","customer_id":"4","enduser_code":"E4","enduser_name":"THOR","changes":"0","old_id":"0","tr_in_id":"1"},"master_item":{"item_name":"BAG"},"master_customer":{"customer_name":"STEPHEN CURRY"}},{"DT_RowId":"row_7","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"7","item_code":"1","quantity":"1000","customer_id":"5","enduser_code":"E5","enduser_name":"HULK","changes":"0","old_id":"0","tr_in_id":"1"},"master_item":{"item_name":"BAG"},"master_customer":{"customer_name":"MICHAEL JORDAN"}},{"DT_RowId":"row_8","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"8","item_code":"2","quantity":"500","customer_id":"2","enduser_code":"E4","enduser_name":"THOR","changes":"0","old_id":"0","tr_in_id":"1"},"master_item":{"item_name":"HAT"},"master_customer":{"customer_name":"LEBRON JAMES"}},{"DT_RowId":"row_9","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"9","item_code":"2","quantity":"500","customer_id":"4","enduser_code":"E7","enduser_name":"HAWKEYE","changes":"0","old_id":"2","tr_in_id":"1"},"master_item":{"item_name":"HAT"},"master_customer":{"customer_name":"STEPHEN CURRY"}}],"options":{"tr_receiving_detail1.item_code":[{"label":"BAG","value":"1"},{"label":"HAT","value":"2"}],"tr_receiving_detail1.customer_id":[{"label":"KEVIN DURANT","value":"3"},{"label":"KOBE BRYANT","value":"1"},{"label":"LEBRON JAMES","value":"2"},{"label":"MICHAEL JORDAN","value":"5"},{"label":"STEPHEN CURRY","value":"4"}]},"files":[]}

I think the table is reloaded, but the changes column still not updated. What i want is
SQL UPDATE `tr_receiving_detail1` SET `changes`=1 WHERE 'tr_receiving_detail1.id' = 'tr_receiving_detail1.old_id

How to check that the postCreate is loaded ?

allan

there are 2 transfer.php

Good - that's what I was after. Thanks. That means the client-side part is correct. The issue is the SQL update.

How to check that the postCreate is loaded

In PHP you would need to add some trace information. A simple echo "Hello"; would do it (it would mean that your JSON return is invalid, but that's okay for a quick test!).

If you open Database/Drivers/Mysql/Query.php you'll find a commented out file_put_contents line. Could you remove the comment so it enables that line and update the output path to suit your server. When trigger the action and view the contents of the file, it will show the SQL commands that Editor used. Can you show me them please?

Allan

vincmeister

here's the result Allan, please take a look, thank you

SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
INSERT INTO  `tr_receiving_detail1`  ( `item_code`, `quantity`, `customer_id`, `enduser_code`, `enduser_name`, `changes`, `old_id`, `tr_in_id` ) VALUES (  :item_code,  :quantity,  :customer_id,  :enduser_code,  :enduser_name,  :changes,  :old_id,  :tr_in_id )
SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 AND `tr_receiving_detail1`.`id` = :where_5 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
UPDATE  `tr_receiving_detail1` SET  `tr_receiving_detail1`.`changes` = :tr_receiving_detail1_1_changes WHERE `tr_receiving_detail1`.`id` = `tr_receiving_detail1`.`old_id` 
SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 

allan

Thank you. It shows:

UPDATE  `tr_receiving_detail1`
SET  `tr_receiving_detail1`.`changes` = :tr_receiving_detail1_1_changes
WHERE `tr_receiving_detail1`.`id` = `tr_receiving_detail1`.`old_id`

which looks correct for what you intend.

If that isn't setting the value to 1 it can only mean that the condition isn't working. It does seem like a slightly odd condition that the old_id would be equal to the id (which I presume is the primary key).

If, in that function you code:

          $res = $editor->db()
                ->select('tr_receiving_detail1', '*')
                ->where('tr_receiving_detail1.id','tr_receiving_detail1.old_id', '=', false)
                ->exec();

          var_dump( $res->fetchAll() );

what do you get?

Allan

vincmeister

Error on line 66

  ->where('tr_receiving_detail1.id','tr_receiving_detail1.old_id', '=', false)

query:

SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
INSERT INTO  `tr_receiving_detail1`  ( `item_code`, `quantity`, `customer_id`, `enduser_code`, `enduser_name`, `changes`, `old_id`, `tr_in_id` ) VALUES (  :item_code,  :quantity,  :customer_id,  :enduser_code,  :enduser_name,  :changes,  :old_id,  :tr_in_id )
SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 AND `tr_receiving_detail1`.`id` = :where_5 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
UPDATE  `tr_receiving_detail1` SET  `tr_receiving_detail1`.`changes` = :tr_receiving_detail1_1_changes WHERE `tr_receiving_detail1`.`id` = `tr_receiving_detail1`.`old_id` 

continue below, text too long

vincmeister

part2

SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
INSERT INTO  `tr_receiving_detail1`  ( `item_code`, `quantity`, `customer_id`, `enduser_code`, `enduser_name`, `changes`, `old_id`, `tr_in_id` ) VALUES (  :item_code,  :quantity,  :customer_id,  :enduser_code,  :enduser_name,  :changes,  :old_id,  :tr_in_id )
SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 AND `tr_receiving_detail1`.`id` = :where_5 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
SELECT  * FROM  `tr_receiving_detail1` 

allan

Error on line 66

What was the error please?

vincmeister

ah sorry forget to put the error
here's

<b>Fatal error</b>: Call to undefined method DataTables\Database\DriverMysqlResult::where() in <b>../function/tr-transfer.php</b> on line <b>66</b><br />

allan

Sorry! Could you try this:

res = $editor->db()
      ->query( 'select', 'tr_receiving_detail1' )
      ->get( '*' )
      ->where('tr_receiving_detail1.id','tr_receiving_detail1.old_id', '=', false)
      ->exec();
 
var_dump( $res->fetchAll() );

Thanks,
Allan

vincmeister

Here's the response

array(0) {
}
{"data":[{"DT_RowId":"row_17","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"17","item_code":"1","quantity":"1000","customer_id":"5","enduser_code":"E4","enduser_name":"HAWKEYE","changes":"0","old_id":"1","tr_in_id":"1"},"master_item":{"item_name":"BAG"},"master_customer":{"customer_name":"MICHAEL JORDAN"}}]}

Here's the editor_sql

SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
INSERT INTO  `tr_receiving_detail1`  ( `item_code`, `quantity`, `customer_id`, `enduser_code`, `enduser_name`, `changes`, `old_id`, `tr_in_id` ) VALUES (  :item_code,  :quantity,  :customer_id,  :enduser_code,  :enduser_name,  :changes,  :old_id,  :tr_in_id )
SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 AND `tr_receiving_detail1`.`id` = :where_5 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
SELECT  * FROM  `tr_receiving_detail1` WHERE `tr_receiving_detail1`.`id` = `tr_receiving_detail1`.`old_id` 

allan

Thanks! If the result set it empty it means that there are no rows that meet the applies condition.

I'm still surprised that the condition is id = old_id. What is setting old_id?

Allan

vincmeister

i'm using old_id to refer the new row to the old row.
for example:
on row # 9, id = 9 , old_id = 2
meaning row # 9 is an edited record from id # 2
id # 2 will no longer used, and must marked as changes=1, and replaced by id # 9

my scenario:
- i'm creating new record, using default value from old_id row (which already done) and
- update the changes field on old_id row, in a moment after submitSuccess (which not done yet)

all in 1 button process
please advise, thank you

allan

Thank you for the explanation - that was exceptionally useful and I believe let's me help to resolve the issue. Try using

->on('postCreate',function( $editor, $id, $values, $row ) {
        var_export( $id );
            $editor->db()
                ->query('update', 'tr_receiving_detail1')
                ->set('tr_receiving_detail1.changes',1)
                ->where('tr_receiving_detail1.id', $_POST['tr_receiving_detail1']['old_id'])
                ->exec();
        })

That uses the value submitted as the old_id as the value to use in the condition.

Allan

vincmeister

Hello Allan,

unfortunately, there are still error

<b>Notice</b>:  Undefined index: tr_receiving_detail1 in on line <b>77</b><br />
{"data":[{"DT_RowId":"row_23","tr_receiving_head":{"tr_in_id":"1"},"tr_receiving_detail1":{"id":"23","item_code":"1","quantity":"1000","customer_id":"4","enduser_code":"E4","enduser_name":"HAWKEYE","changes":"0","old_id":"1","tr_in_id":"1"},"master_item":{"item_name":"BAG"},"master_customer":{"customer_name":"STEPHEN CURRY"}}]}

Line 77:

->where('tr_receiving_detail1.id', $_POST['tr_receiving_detail1']['old_id'])

editor_sql:

SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
INSERT INTO  `tr_receiving_detail1`  ( `item_code`, `quantity`, `customer_id`, `enduser_code`, `enduser_name`, `changes`, `old_id`, `tr_in_id` ) VALUES (  :item_code,  :quantity,  :customer_id,  :enduser_code,  :enduser_name,  :changes,  :old_id,  :tr_in_id )
SELECT  `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_head`.`tr_in_id` as 'tr_receiving_head.tr_in_id', `tr_receiving_detail1`.`id` as 'tr_receiving_detail1.id', `tr_receiving_detail1`.`item_code` as 'tr_receiving_detail1.item_code', `master_item`.`item_name` as 'master_item.item_name', `tr_receiving_detail1`.`quantity` as 'tr_receiving_detail1.quantity', `tr_receiving_detail1`.`customer_id` as 'tr_receiving_detail1.customer_id', `master_customer`.`customer_name` as 'master_customer.customer_name', `tr_receiving_detail1`.`enduser_code` as 'tr_receiving_detail1.enduser_code', `tr_receiving_detail1`.`enduser_name` as 'tr_receiving_detail1.enduser_name', `tr_receiving_detail1`.`changes` as 'tr_receiving_detail1.changes', `tr_receiving_detail1`.`old_id` as 'tr_receiving_detail1.old_id', `tr_receiving_detail1`.`tr_in_id` as 'tr_receiving_detail1.tr_in_id' FROM  `tr_receiving_detail1` LEFT JOIN `tr_receiving_head` ON `tr_receiving_head`.`tr_in_id` = `tr_receiving_detail1`.`tr_in_id`  LEFT JOIN `master_item` ON `master_item`.`item_code` = `tr_receiving_detail1`.`item_code`  LEFT JOIN `master_customer` ON `master_customer`.`id` = `tr_receiving_detail1`.`customer_id` WHERE `tr_receiving_detail1`.`kirim` = :where_0 AND `tr_receiving_detail1`.`changes` = :where_1 AND `tr_receiving_detail1`.`customer_id` <> :where_2 AND `tr_receiving_detail1`.`enduser_code` <> :where_3 AND `tr_receiving_detail1`.`enduser_name` <> :where_4 AND `tr_receiving_detail1`.`id` = :where_5 
SELECT DISTINCT  `item_code` as 'item_code', `item_name` as 'item_name' FROM  `master_item` WHERE (`axapta` LIKE :where_1 )
SELECT DISTINCT  `id` as 'id', `customer_name` as 'customer_name' FROM  `master_customer` 
UPDATE  `tr_receiving_detail1` SET  `tr_receiving_detail1`.`changes` = :tr_receiving_detail1_1_changes WHERE `tr_receiving_detail1`.`id` IS NULL 

i'm trying to echo$_POST['tr_receiving_detail1']['old_id']) , no result

please help, thank you

allan

Sorry, $_POST['tr_receiving_detail1']['old_id'] should have been:

$values['tr_receiving_detail1']['old_id']

Allan