preCreate/preEdit - extract string from field

peterbrowne

I am wanting to use preCreate and preEdit to automatically create an insert into database column based on two fields in the Editor form.

The created insert needs to be in the format week - unit where unit is eight characters.

This constructed entry is to be inserted into column week.week_full_name

This is constructed from fields:

Field::inst( 'week.week' ),

and

Field::inst( 'unit_group.unit_group' )

The latter field I just want to use the last eight characters of the record (selected by Select list). Not sure how to extract that part of the string from that record - that is I only want the last eight characters of Field::inst( 'unit_group.unit_group' )

The code so far:

            ->on( 'preCreate', function ( $editor, &$values ) {
        $editor
            ->field( 'week.week_full_name' )
            ->setValue( $values['week']['week'] . ' - ' . $values['unit_group']['unit_group'] );
    } )
        ->on('preEdit', function ($editor, $id, $values) {
  $editor->field('week.week_full_name')->setValue(
    $values['week']['week'] . ' ' . $values['unit_group']['unit_group']);
} )

peterbrowne

Full code, and I'm getting system error on create:

Notice: Undefined index: unit_group in /var/www/html/curriculum_mapper/programs/cm_mjd_plmed/program_data/week_data.php on line 38 {"data":[{"DT_RowId":"row_125","week":{"week":"testing","week_full_name":"testing - ","modified":"2022-05-05 13:50:27","modified_by":"00082563","unit_group_fk":"10"},"unit_group":{"unit_group":"Learning Events - PATH3309"}}]}

include( "../../../datatables/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;

Editor::inst( $db_cm_mjd_plmed, 'week', 'week_pk' )->fields(
        Field::inst( 'week.week' ),
        Field::inst( 'week.week_full_name' ),
        Field::inst( 'week.modified' ),
        Field::inst( 'week.modified_by' )->setValue( $user),
        Field::inst( 'week.unit_group_fk' )
            ->options( Options::inst()
                ->table( 'unit_group' )
                ->value( 'unit_group_pk' )
                ->label( 'unit_group' )
                ->where( function ($q) {
            $q->where( 'unit_group.type', 'learning_event' );
                })
            ),
        Field::inst( 'unit_group.unit_group' )  
    )
     ->leftJoin( 'unit_group', 'unit_group.unit_group_pk', '=', 'week.unit_group_fk' )
            ->on( 'preCreate', function ( $editor, &$values ) {
        $editor
            ->field( 'week.week_full_name' )
            ->setValue( $values['week']['week'] . ' - ' . $values['unit_group']['unit_group']);
    } )
        ->on('preEdit', function ($editor, $id, $values) {
        $editor
            ->field('week.week_full_name')
            ->setValue($values['week']['week'] . ' - ' . $values['unit_group']['unit_group']);
} ) 
    ->process($_POST)
    ->json();

allan

Are you inline editing possibly? It will only submit the changed field value, not the whole row. That can be changed using the form-options submit parameters as shown in this example.

If it is not that, I think I'd need a link to the page showing the issue so I can inspect the data being sent to the server.

Allan

peterbrowne

Alan

No inline editing. I have another almost identical page that uses preCreate/preEdit and no issues. That code is:

Editor::inst( $db_cm_mjd_plmed, 'unit', 'unit_pk' )    
    ->field(
        Field::inst( 'unit.unit_code' ),
        Field::inst( 'unit.unit_name' ),
        Field::inst( 'unit.unit_full_name' ),
        Field::inst( 'unit.points' ),
        Field::inst( 'unit.modified' ),
        Field::inst( 'unit.modified_by' )->setValue( $user ),
        Field::inst( 'unit.year_fk' )
            ->options( Options::inst()
                ->table( 'year' )
                ->value( 'year_pk' )
                ->label( 'year_name' )
            )
            ->validator( 'Validate::dbValues' ),
        Field::inst( 'year.year_name' )
    )
    ->leftJoin( 'year', 'year.year_pk', '=', 'unit.year_fk' )
    ->join(
        Mjoin::inst( 'program_outcome' )
            ->link( 'unit.unit_pk', 'program_outcome_unit_lookup.unit_fk' )
            ->link( 'program_outcome.program_outcome_pk', 'program_outcome_unit_lookup.program_outcome_fk' )
           ->order( 'program_outcome.program_outcome asc' )
            ->fields(
                Field::inst( 'program_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'program_outcome' )
                       ->value( 'program_outcome_pk' )
        ->label( array('type', 'program_outcome') )
        ->render( function ( $row ) {
            return $row['type'] . ' - ' . $row['program_outcome'];
        } )
                        ->order( 'type' )
                    ),
                Field::inst( 'program_outcome' )
            )
    )
        ->on( 'preCreate', function ( $editor, &$values ) {
        $editor
            ->field( 'unit.unit_full_name' )
            ->setValue( $values['unit']['unit_code'] . ' ' . $values['unit']['unit_name'] );
    } )
        ->on('preEdit', function ($editor, $id, $values) {
  $editor->field('unit.unit_full_name')->setValue(
    $values['unit']['unit_code'] . ' ' . $values['unit']['unit_name']);
} )
    ->process($_POST)
    ->json();

The only difference I can see is the second field to be joined in the preCreate is part of a left join in the problematic code in the first post. In this code that works, the right most join field in the preCreate is further up in the Fields list.

peterbrowne

This is the client side code for the problematic server side data code:

var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/week_data.php",
                table: "#week_table",
                template: '#week_form',
                fields: [ {
                    label: "Week:",
                    name: "week.week"
                }, {
                    label: "Week Full Name:",
                    name: "week.week_full_name"
                }, {
                    label: "Unit Group:",
                    name: "week.unit_group_fk",
                    type: "select",
                    placeholder: "Select Unit Group..."
                } ]
            });
            

            var table = $( '#week_table' ).DataTable( {
                responsive: true,
                 "pageLength": 50,
                "autoWidth": false,
                "lengthMenu": [
                    [ 5, 10, 25, 50, -1 ],
                    [ 5, 10, 25, 50, "All" ]
                ],
                columnDefs: [ {
                    targets: 1,
                    render: $.fn.dataTable.render.ellipsis( 150, true )
                } ],
                ajax: "program_data/week_data.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "week.week"
                }, {
                    data: "week.week_full_name"
                }, {
                    data: "unit_group.unit_group"
                }, {
                    data: "week.modified"
                }, {
                    data: "week.modified_by"
                } , {
                    data: null,
                    className: "center",
                    defaultContent: '<a href="" class="editor_edit">Edit</a>'
                }],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: []
            } );

peterbrowne

Alan, I sent a PM regarding access to a page which shows the issue. Peter

allan

Many thanks for the link by PM - I see the issue and understand what is causing it now. $values contains the submitted data, and unit,unit_group is not being sent to the server. Include it in your form as a hidden field and that should resolve the issue.

Allan

peterbrowne

Alan, I don't quite follow , sorry.

In the preCreate/preEdit part, the following combines two fields: week and unit group from their respective tables.

->setValue( $values['week']['week'] . ' - ' . $values['unit_group']['unit_group']);

this combined value is for insertion to column week.week_full_name.

There is not meant to be any table for unit in this code.

The field unit_group.unit_group is referenced further up in the server side script, week_data.php:

Field::inst( 'unit_group.unit_group' )

I still don't understand why it would be causing an error in this code:

Editor::inst( $db_cm_mjd_plmed, 'week', 'week_pk' )->fields(
        Field::inst( 'week.week' ),
        Field::inst( 'week.week_full_name' ),
        Field::inst( 'week.modified' ),
        Field::inst( 'week.modified_by' )->setValue( $user),
        Field::inst( 'week.unit_group_fk' )
            ->options( Options::inst()
                ->table( 'unit_group' )
                ->value( 'unit_group_pk' )
                ->label( 'unit_group' )
                ->where( function ($q) {
            $q->where( 'unit_group.type', 'learning_event' );
                })
            ),
        Field::inst( 'unit_group.unit_group' )  
    )
     ->leftJoin( 'unit_group', 'unit_group.unit_group_pk', '=', 'week.unit_group_fk' )
            ->on( 'preCreate', function ( $editor, &$values ) {
        $editor
            ->field( 'week.week_full_name' )
            ->setValue( $values['week']['week'] . ' - ' . $values['unit_group']['unit_group']);
    } )
        ->on('preEdit', function ($editor, $id, $values) {
        $editor
            ->field('week.week_full_name')
            ->setValue($values['week']['week'] . ' - ' . $values['unit_group']['unit_group']);
} ) 
    ->process($_POST)
    ->json();

As I indicated earlier, I just want to grab the last eight characters from ```unit_group''' instead of the whole value of that field. Can you possibly give me a more detailed explanation?

Here is the code from the form/editor client side:

var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/week_data.php",
                table: "#week_table",
                template: '#week_form',
                fields: [ {
                    label: "Week:",
                    name: "week.week"
                }, {
                    label: "Week Full Name:",
                    name: "week.week_full_name"
                }, {
                    label: "Unit Group:",
                    name: "week.unit_group_fk",
                    type: "select",
                    placeholder: "Select Unit Group..."
                } ]
            });
            

            var table = $( '#week_table' ).DataTable( {
                responsive: true,
                 "pageLength": 50,
                "autoWidth": false,
                "lengthMenu": [
                    [ 5, 10, 25, 50, -1 ],
                    [ 5, 10, 25, 50, "All" ]
                ],
                columnDefs: [ {
                    targets: 1,
                    render: $.fn.dataTable.render.ellipsis( 150, true )
                } ],
                ajax: "program_data/week_data.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "week.week"
                }, {
                    data: "week.week_full_name"
                }, {
                    data: "unit_group.unit_group"
                }, {
                    data: "week.modified"
                }, {
                    data: "week.modified_by"
                } , {
                    data: null,
                    className: "center",
                    defaultContent: '<a href="" class="editor_edit">Edit</a>'
                }],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: []
            } );

peterbrowne

In the error:

Notice: Undefined index: unit_group in /var/www/html/curriculum_mapper/programs/cm_mjd_plmed/program_data/week_data.php on line 43 {"data":[{"DT_RowId":"row_127","week":{"week":"testing","week_full_name":"testing - ","modified":"2022-05-09 08:43:44","modified_by":"00082563","unit_group_fk":"10"},"unit_group":{"unit_group":"Learning Events - PATH3309"}}]}

Re no index for unit_group, the data is actually there in Learning Events - PATH3309, so I don't know why there is that error.

As I said I have another page that uses preEdit/preCreate with no problems. The difference is that page both combined fields are columns of the main table.

With this problematic page with the above error, the column that is problematic is coming from a left join as you can see. Perhaps that is where the issue is?

allan

Add:

{
  name: "unit_group.unit_group",
  type: 'hidden'
},

To your Javascript Editor fields. That should resolve it.

Allan

peterbrowne

Thanks Allan. That resolved the error.

So going back to my original question, how do I extract the last 8 characters from:

$values['unit_group']['unit_group']);

to use only those 8 characters in combined preEdit/preCreate insert?

peterbrowne

Tried:

->setValue( $values['week']['week'] . ' - ' . $values['unit_group']['unit_group'].slice(-8));

Fatal error: Call to undefined function slice() in /var/www/html/curriculum_mapper/programs/cm_mjd_plmed/program_data/week_data.php on line 43

colin

slice() is JS, as you're in PHP you'll need to use something like substr(),

Colin

peterbrowne

What was I thinking...of course. I blame the brain fog. Works perfectly with:

->setValue( $values['week']['week'] . ' - ' . substr($values['unit_group']['unit_group'], -8));

Thanks Colin!

peterbrowne

Ok there is a problem with using the hidden field:

        var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/week_data.php",
                table: "#week_table",
                template: '#week_form',
                fields: [ {
                    label: "Week:",
                    name: "week.week"
                }, {
                    label: "Week Full Name:",
                    name: "week.week_full_name"
                }, {
                    label: "Unit Group:",
                    name: "week.unit_group_fk",
                    type: "select",
                    placeholder: "Select Unit Group..."
                }, {
                    name: "unit_group.unit_group",
                    type: 'hidden'
                }, ]
            });

When creating or updating a record, records in the column in table unit_group.unit_group are set to empty WHERE the value equals the value in the Editor form for that field.

Alan, you can test this with the previous link sent via PM a few days ago. Create a new record, note the unit group you selected, then refresh the page. You will see all records on that page that should have the same value for the unit group as the one created, have lost the value for Unit Group column.

allan

Add ->set(false) to the PHP field if you don't want editor to write to it.

Allan

peterbrowne

Tried that but now the unit_group is missing from the field week_full_name with:

    ->on( 'preCreate', function ( $editor, &$values ) {
        $editor
            ->field( 'week.week_full_name' )
            ->setValue( $values['week']['week'] . ' - ' . substr($values['unit_group']['unit_group'], -8));
    } )
        ->on('preEdit', function ($editor, $id, $values) {
        $editor
            ->field('week.week_full_name')
            ->setValue($values['week']['week'] . ' - ' . substr($values['unit_group']['unit_group'], -8));
} ) 

Full PHP code:

Editor::inst( $db_cm_mjd_plmed, 'week', 'week_pk' )->fields(
        Field::inst( 'week.week' ),
        Field::inst( 'week.week_full_name' ),
        Field::inst( 'week.modified' ),
        Field::inst( 'week.modified_by' )->setValue( $user),
        Field::inst( 'week.unit_group_fk' )
            ->options( Options::inst()
                ->table( 'unit_group' )
                ->value( 'unit_group_pk' )
                ->label( 'unit_group' )
                ->where( function ($q) {
            $q->where( 'unit_group.type', 'learning_event' );
                })
            ),
        Field::inst( 'unit_group.unit_group' )
        ->set(false)
    )
     ->leftJoin( 'unit_group', 'unit_group.unit_group_pk', '=', 'week.unit_group_fk' )
            ->on( 'preCreate', function ( $editor, &$values ) {
        $editor
            ->field( 'week.week_full_name' )
            ->setValue( $values['week']['week'] . ' - ' . substr($values['unit_group']['unit_group'], -8));
    } )
        ->on('preEdit', function ($editor, $id, $values) {
        $editor
            ->field('week.week_full_name')
            ->setValue($values['week']['week'] . ' - ' . substr($values['unit_group']['unit_group'], -8));
} ) 
    ->process($_POST)
    ->json();

and the client-side:

            $.fn.dataTable.Editor.display.lightbox.conf.windowPadding = 50;

            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/week_data.php",
                table: "#week_table",
                template: '#week_form',
                fields: [ {
                    label: "Week:",
                    name: "week.week"
                }, {
                    label: "Unit Group:",
                    name: "week.unit_group_fk",
                    type: "select",
                    placeholder: "Select Unit Group..."
                }, {
                    name: "unit_group.unit_group",
                    type: 'hidden'
                }, ]
            });
            

            var table = $( '#week_table' ).DataTable( {
                responsive: true,
                 "pageLength": 50,
                "autoWidth": false,
                "lengthMenu": [
                    [ 5, 10, 25, 50, -1 ],
                    [ 5, 10, 25, 50, "All" ]
                ],
                columnDefs: [ {
                    targets: 1,
                    render: $.fn.dataTable.render.ellipsis( 150, true )
                } ],
                ajax: "program_data/week_data.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "week.week"
                }, {
                    data: "week.week_full_name"
                }, {
                    data: "unit_group.unit_group"
                }, {
                    data: "week.modified"
                }, {
                    data: "week.modified_by"
                } , {
                    data: null,
                    className: "center",
                    defaultContent: '<a href="" class="editor_edit">Edit</a>'
                }],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: []
            } );

peterbrowne

Strange, it looks like the unit_group part of the combined fields in preCreate/preEdit is missing along with the unit_group column as per my previous post on Create. If I Update that record the week_full_name is complete with the unit_code. I'm not sure what is happening...

Alan, could you check the previous sent link via PM to have a look at this?

The PHP code:

Editor::inst( $db_cm_mjd_plmed, 'week', 'week_pk' )->fields(
        Field::inst( 'week.week' ),
        Field::inst( 'week.week_full_name' ),
        Field::inst( 'week.modified' ),
        Field::inst( 'week.modified_by' )->setValue( $user),
        Field::inst( 'week.unit_group_fk' )
            ->options( Options::inst()
                ->table( 'unit_group' )
                ->value( 'unit_group_pk' )
                ->label( 'unit_group' )
                ->where( function ($q) {
            $q->where( 'unit_group.type', 'learning_event' );
                })
            ),
        Field::inst( 'unit_group.unit_group' )
        ->set(false)
    )
     ->leftJoin( 'unit_group', 'unit_group.unit_group_pk', '=', 'week.unit_group_fk' )
            ->on( 'preCreate', function ( $editor, &$values ) {
        $editor
            ->field( 'week.week_full_name' )
            ->setValue( $values['week']['week'] . ' - ' . substr($values['unit_group']['unit_group'], -8));
    } )
        ->on('preEdit', function ($editor, $id, $values) {
        $editor
            ->field('week.week_full_name')
            ->setValue($values['week']['week'] . ' - ' . substr($values['unit_group']['unit_group'], -8));
} ) 
    ->process($_POST)
    ->json();

Client side:

<script type="text/javascript">


            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/week_data.php",
                table: "#week_table",
                template: '#week_form',
                fields: [ {
                    label: "Week:",
                    name: "week.week"
                }, {
                    name: "week.week_full_name",
                    type: 'hidden'
                }, {
                    label: "Unit Group:",
                    name: "week.unit_group_fk",
                    type: "select",
                    placeholder: "Select Unit Group..."
                }, {
                    name: "unit_group.unit_group",
                    type: 'hidden'
                }, ]
            });
            

            var table = $( '#week_table' ).DataTable( {
                responsive: true,
                 "pageLength": 50,
                "autoWidth": false,
                "lengthMenu": [
                    [ 5, 10, 25, 50, -1 ],
                    [ 5, 10, 25, 50, "All" ]
                ],
                columnDefs: [ {
                    targets: 1,
                    render: $.fn.dataTable.render.ellipsis( 150, true )
                } ],
                ajax: "program_data/week_data.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "week.week"
                }, {
                    data: "week.week_full_name"
                }, {
                    data: "unit_group.unit_group"
                }, {
                    data: "week.modified"
                }, {
                    data: "week.modified_by"
                } , {
                    data: null,
                    className: "center",
                    defaultContent: '<a href="" class="editor_edit">Edit</a>'
                }],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: []
            } );

peterbrowne

Strange, it looks like the unit_group part of the combined fields in preCreate/preEdit is missing along with the unit_group column as per my previous post on Create. That is, ALL **values in table unit_group.unit_group are now empty where the value equals the selected unit_group in the Editor on **Create.

This is with:

        Field::inst( 'unit_group.unit_group' )
        ->set(false)

If I Update that record, after manually repopulating that unit_group column in the DB table, the week_full_name is complete along with the unit_code. I'm not sure what is happening...

Alan, could you check the previous sent link via PM to have a look at this?

The PHP code:

Editor::inst( $db_cm_mjd_plmed, 'week', 'week_pk' )->fields(
        Field::inst( 'week.week' ),
        Field::inst( 'week.week_full_name' ),
        Field::inst( 'week.modified' ),
        Field::inst( 'week.modified_by' )->setValue( $user),
        Field::inst( 'week.unit_group_fk' )
            ->options( Options::inst()
                ->table( 'unit_group' )
                ->value( 'unit_group_pk' )
                ->label( 'unit_group' )
                ->where( function ($q) {
            $q->where( 'unit_group.type', 'learning_event' );
                })
            ),
        Field::inst( 'unit_group.unit_group' )
        ->set(false)
    )
     ->leftJoin( 'unit_group', 'unit_group.unit_group_pk', '=', 'week.unit_group_fk' )
            ->on( 'preCreate', function ( $editor, &$values ) {
        $editor
            ->field( 'week.week_full_name' )
            ->setValue( $values['week']['week'] . ' - ' . substr($values['unit_group']['unit_group'], -8));
    } )
        ->on('preEdit', function ($editor, $id, $values) {
        $editor
            ->field('week.week_full_name')
            ->setValue($values['week']['week'] . ' - ' . substr($values['unit_group']['unit_group'], -8));
} ) 
    ->process($_POST)
    ->json();

Client side:

<script type="text/javascript">


            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/week_data.php",
                table: "#week_table",
                template: '#week_form',
                fields: [ {
                    label: "Week:",
                    name: "week.week"
                }, {
                    name: "week.week_full_name",
                    type: 'hidden'
                }, {
                    label: "Unit Group:",
                    name: "week.unit_group_fk",
                    type: "select",
                    placeholder: "Select Unit Group..."
                }, {
                    name: "unit_group.unit_group",
                    type: 'hidden'
                }, ]
            });
            

            var table = $( '#week_table' ).DataTable( {
                responsive: true,
                 "pageLength": 50,
                "autoWidth": false,
                "lengthMenu": [
                    [ 5, 10, 25, 50, -1 ],
                    [ 5, 10, 25, 50, "All" ]
                ],
                columnDefs: [ {
                    targets: 1,
                    render: $.fn.dataTable.render.ellipsis( 150, true )
                } ],
                ajax: "program_data/week_data.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "week.week"
                }, {
                    data: "week.week_full_name"
                }, {
                    data: "unit_group.unit_group"
                }, {
                    data: "week.modified"
                }, {
                    data: "week.modified_by"
                } , {
                    data: null,
                    className: "center",
                    defaultContent: '<a href="" class="editor_edit">Edit</a>'
                }],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: []
            } );

allan

Many thanks - I think I understand the issue a little better now. week.week_full_name and unit_group.unit_group are being submitted as empty strings, since their values are not being set when selecting a value from the _Unit Group` dropdown.

When a value is selected from that field, you'll want to set the value for the other dependent fields as well. That can be done with dependent().

Something like:

editor.dependent('week.unit_group_fk', function (val, data, cb) {
  // Get the text from the `select` field
  editor.field('unit_group.unit_group').val(
    editor.field('week.unit_group_fk').input().find('option:selected').text()
  );

  cb({});
});

In all honesty though, I would recommend against this approach. Consider the case whereby if someone updated a unit group name, that wouldn't then be reflected in this field and couldn't be unless its value to was updated.

What I would strongly recommend in this case is that you store only the raw data components in the database, and then on the client-side use a rendering function to combine the data. That way you side step the issue discussed here and also integrity issue I mentioned above.

Allan

peterbrowne

Thanks Allan, the dependant does the trick nicely. I will use that as the unit_groups are created by the application and aren't editable.