edit the child table when using Mjoin

Khalid Teli

Hi,

Is it possible to edit the joined tables (joined using Mjoin)? So that if the key in the main table is changed , the change reflects in the foreign key of linked child table

I have two tables : Main table: Agreemnet table and joined table: terms_type. one agreement can have multiple terms , so the relation is 1 to many, as depicted in the picture:

I have joined the using Mjoin and the code on serverside looks like this:

->join(
        Mjoin::inst( 'crg_terms_type' )
            ->link( 'agreement.agreement_id', 'crg_terms_type.agreement_id_fk' )
            ->fields(
                Field::inst( 'agreement_id_fk' )
                    ->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'crg_terms_type' )
                        ->value( 'agreement_id_fk' )
                        ->label( 'type' )
                    ),
                Field::inst( 'type' )
            )
    )

Here is the data returned from the server:

{
    "data": [{
        "DT_RowId": "row_123",
        "agreement": {
            "agreement_id": 123,
            "company": "",
            "supplier": "BC Foods",
            "brand_type": "",
            "ah_company": "D",
            "ah_supplier": "J",
            "start_date": "2020-01-01",
            "end_date": "2020-12-31",
            "income": 0,
            "comments": "",
            "status": "PENDING",
            "print": "",
            "supplier_ref": "BC2020",
            "terms": ""
        },
        "crg_terms_type": [{
            "agreement_id_fk": 123,
            "type": "ORD"
        }]
    }, {
        "DT_RowId": "row_456",
        "agreement": {
            "agreement_id": 456,
            "company": "",
            "supplier": "AB Foods",
            "brand_type": "",
            "ah_company": " P",
            "ah_supplier": "J",
            "start_date": "2020-01-01",
            "end_date": "2020-12-31",
            "income": 0,
            "comments": "",
            "status": "PENDING",
            "print": "",
            "supplier_ref": "AB2020",
            "terms": ""
        },
        "crg_terms_type": [{
            "agreement_id_fk": 456,
            "type": "ORD"
        }, {
            "agreement_id_fk": 456,
            "type": "Target-1"
        }, {
            "agreement_id_fk": 456,
            "type": "Target-2"
        }, {
            "agreement_id_fk": 456,
            "type": "Target-3"
        }, {
            "agreement_id_fk": 456,
            "type": "Target-4"
        }, {
            "agreement_id_fk": 456,
            "type": "Target-5"
        }]
    }, {
        "DT_RowId": "row_789",
        "agreement": {
            "agreement_id": 789,
            "company": "",
            "supplier": "BC Foods",
            "brand_type": "",
            "ah_company": "P",
            "ah_supplier": "J",
            "start_date": "2021-01-01",
            "end_date": "2021-12-31",
            "income": 0,
            "comments": "",
            "status": "PENDING",
            "print": "",
            "supplier_ref": "BC2021",
            "terms": ""
        },
        "crg_terms_type": [{
            "agreement_id_fk": 789,
            "type": "ORD"
        }, {
            "agreement_id_fk": 789,
            "type": "Target-1"
        }, {
            "agreement_id_fk": 789,
            "type": "Target-2"
        }]
    }],
    "options": {
        "crg_terms_type[].agreement_id_fk": [{
            "label": "ORD",
            "value": 789
        }, {
            "label": "ORD",
            "value": 456
        }, {
            "label": "ORD",
            "value": 123
        }, {
            "label": "Target-1",
            "value": 456
        }, {
            "label": "Target-1",
            "value": 789
        }, {
            "label": "Target-2",
            "value": 456
        }, {
            "label": "Target-2",
            "value": 789
        }, {
            "label": "Target-3",
            "value": 456
        }, {
            "label": "Target-4",
            "value": 456
        }, {
            "label": "Target-5",
            "value": 456
        }]
    },
    "files": [],
    "searchPanes": {
        "options": []
    }
}

What I am trying to achieve here is that, if I edit the data in main table, fro exampe if I change the agreement_id in main table , the foreign key ** agreement_id_fk** should automatically change

Tried this on the on the client side in editor field:

    "fields": [
   {
                "label": "Agreement ID FK-child table",
                "name": "crg_terms_type[].agreement_id_fk"
            },

but it is not working

Thank you

allan

It is using Editor 2 and a DataTable field input with nested editing. But it isn't possible to edit the content of the child table directly from the parent form - it needs to be in a nested form.

Allan

Khalid Teli

@allan Thank you. First of all congratulations for the release of Editor2. It looks amazing.

With a Datatable field input , is it possible to create duplicate records in both tables (Parent and Child tables) at same time using duplicate buttons?

Thank you

Khalid Teli

Hi @allan

I have implemented the nested editing and it works fine. However there are few issues I am facing:

1) in theconfig partof the code, I am passing the extra variable so only relevant rows from table are selected. I am not sure where should I define the Var selectRowId, it shows the variable is undefined because the datatable is initialised after editor instances.

config: {

           "serverSide": true,
    ajax: {
      'url':  "/Editor2.0/php",
      type: 'POST',
      data: function(d) {

       d.agreement_id = selectRowId;
      console.log(d);
      }
    }
                    buttons: [
                        { extend: 'create', editor: incomeEditor2 },
                        { extend: 'edit',   editor: incomeEditor2 },
                        { extend: 'remove', editor: incomeEditor2 },
                         {extend: 'selectAll'},
                    ],


                    columns: [
                        {
                            title: 'Agreement ID FK',
                            data: 'agreement_id_fk'
                        }
                    ]
                } 

2) Is it possible to use Duplicate button in nested editor? Something like this

config: {

                        "serverSide": true,


    ajax: {
      'url':  "/Editor2.0/Editor-2.0.1/controllers/stattrak/income_fetch.php",
      type: 'POST',
      data: function(d) {

          var selectRowId ='87';
       d.agreement_id = selectRowId;
      console.log(d);
      }
    },




                    buttons: [
                        { extend: 'create', editor: incomeEditor2 },
                        { extend: 'edit',   editor: incomeEditor2 },
                        { extend: 'remove', editor: incomeEditor2 },
                         {extend: 'selectAll'},

                            {
                extend: "selected",
                text: 'Duplicate',
                action: function ( e, dt, node, config ) {
                    // Start in edit mode, and then change to create
                    ignore = true;
                    incomeEditor2
                        .edit( agreementTable.rows( {selected: true} ).indexes(), {
                            title: 'Duplicate record',
                            buttons: 'Create from existing'
                        } )
                        .mode( 'create' );
                            // $("#customForm").show();

                }
            },
                    ],


                    columns: [
                        {
                            title: 'Agreement ID FK',
                            data: 'agreement_id_fk'
                        },
                         {
                            title: 'Type',
                            data: 'type'
                        },
                         {
                            title: 'Percentage',
                            data: 'percentage'
                        },
                         {
                            title: 'Growth',
                            data: 'growth'
                        }
                    ]
                }

if yes, how would I go on to select rows
edit(xxxx.rows( {selected: true} ).indexes(), {

Thank you

Khalid Teli

just an update with the duplicate button, everything works : except

The issue is when I click on the duplicate button in nested form (create from existing), it does create the duplicate records but the editor window disappears after I click on create from existing.

However, it should return back to the main Editor window (main form) , where I should be able to again click on create from existing to duplicate rows in main table

                 {
                extend: "selected",
                text: 'Duplicate',
                action: function ( e, dt, node, config ) {
                    // Start in edit mode, and then change to create
                    incomeEditor2
                        .edit(( {selected: true} ), {
                            title: 'Duplicate record',
                            buttons: 'Create from existing'
                        } )
                        .mode( 'create' );

                }
            },

allan

Could you give me a link to your page so I can help to debug that please?

Thanks,
Allan

Khalid Teli

Hi @allan thank you.

I have sent you the link in pm.
Thank you