Need to store both Null and 0 in database

Need to store both Null and 0 in database

danvanwijkdanvanwijk Posts: 17Questions: 4Answers: 0

Link to test case: https://joomrp.urtestsite.com/portal/companies/sites.html
Debugger code (debug.datatables.net):
Error messages shown: There is no error message
Description of problem: When editing the Company column we are unable to select the value with 0 value. We want to store both NULL and 0 to database. Can you please suggest an example or datatype for this?

Replies

  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1
    edited May 8

    You can define the column as int and make it nullable. Something like this

  • danvanwijkdanvanwijk Posts: 17Questions: 4Answers: 0

    It is already implemented like that but I need validation at the PHP end to make sure we can store both 0 and NULL in the database. Right now I am facing when the value is selected as 0 because I am using this validation ->validator( Validate::numeric() )
    ->setFormatter( Format::ifEmpty(null) )

  • Aryan1703Aryan1703 Posts: 65Questions: 15Answers: 1

    You could try something like this:

        ->validator(function ($val, $data, $field) {
            if ($val !== null && $val !== 0) {
                return 'Please enter either 0 or leave it empty.';
            }
            return true;
        })
        ->setFormatter(Format::ifEmpty(null))
    
  • danvanwijkdanvanwijk Posts: 17Questions: 4Answers: 0

    I will explain the complete functionality. We need to store three values in database which is 0, 1 and NULL and these are the three values of a checkbox. I am using the code for php as follows:
    Field::inst( 'assembled' )
    ->setFormatter( function ( $val, $data, $opts ) {
    return ! $val ? 0 : 1;
    } )->validator( Validate::numeric() )
    ->setFormatter( Format::ifEmpty(null) ),

    By using this, I am able to store values null and 1 but value 0 is also getting replaced by null. I want to store the 0 as 0 in database. Let me know if you can help with this.
    Thanks!

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    What is your client-side setup for this field? If you submit an empty string as the value for when you want null to be written to the database, that that should be fine.

    Your first formatting function will need to be a little more accurate in your formatting function though since ! $val is loosely "falsy" - i.e. it will match for '', null and 0.

    However, I don't think you need that - try:

    Field::inst( 'assembled' )
      ->validator( Validate::numeric() )
      ->setFormatter( Format::ifEmpty(null) ),
    

    Allan

  • danvanwijkdanvanwijk Posts: 17Questions: 4Answers: 0

    It didn't work after changing the given code. Following is the client site code and we are fixing it first for Assembled field:
    jquery: function () {

            var editor = new DataTable.Editor({
                ajax: '../../controllers/portal/parts.php',
                fields: [
                    {
                        label: 'IPN:',
                        name: 'ipn'
                    },
                    {
                        label: 'Description:',
                        name: 'description'
                    },
                    {
                        label: 'Total SOH:',
                        name: 'total_soh',
                        type:'readonly',
                        default: 0.00,
                        attr:{ disabled:true }
                    },
                    {
                        label: 'Assembled:',
                        name: 'assembled',
                        type: 'checkbox',
                        separator: '|',
                        options: [{ label: '', value: 1 }]
                    },
                    {
                        label: 'Purchased:',
                        name: 'purchased',
                        type: 'checkbox',
                        separator: '|',
                        options: [{ label: '', value: 1 }]
                    },
                    {
                        label: 'Sold:',
                        name: 'sold',
                        type: 'checkbox',
                        separator: '|',
                        options: [{ label: '', value: 1 }]
                    },
                    {
                        label: 'Equipment:',
                        name: 'equipment',
                        type: 'checkbox',
                        separator: '|',
                        options: [{ label: '', value: 1 }]
                    },
                    {
                        label: 'Status:',
                        name: 'status',
                        type: 'select',
                        placeholder: 'Please select a status'
                    },
                    {
                        label: 'Default Owner:',
                        name: 'default_owner',
                        type: 'select',
                        placeholder: 'Please select a Default Owner'
                    },
                    {
                        label: 'Default Manufacturer:',
                        name: 'default_manufacturer',
                        type: 'select',
                        placeholder: 'Please select a Default Manufacturer'
                    },
                    {
                        label: 'Default MPN:',
                        name: 'default_mpn'
                    }
                ],
                table: '#example'
            });
    
            $('#example').DataTable({
                ajax: '../../controllers/portal/parts.php',
                columns: [
                    {
                        data: null,
                        orderable: false,
                        render: DataTable.render.select()
                    },
                    { data: 'ipn' },
                    { data: 'description' },
                    { data: 'total_soh', render: $.fn.dataTable.render.number(',', '.', 2, '') },
                    { data: 'assembled',
                        render: function (data, type, row) {
                            if (type === 'display') {
                                return '<input type="checkbox" class="assembled">';
                            }
                            return data;
                        },
                        className: 'dt-body-center' },
                    { data: 'purchased',
                        render: function (data, type, row) {
                            if (type === 'display') {
                                return '<input type="checkbox" class="purchased">';
                            }
                            return data;
                        },
                        className: 'dt-body-center' },
                    { data: 'sold',
                        render: function (data, type, row) {
                            if (type === 'display') {
                                return '<input type="checkbox" class="sold">';
                            }
                            return data;
                        },
                        className: 'dt-body-center' },
                    { data: 'equipment',
                        render: function (data, type, row) {
                            if (type === 'display') {
                                return '<input type="checkbox" class="equipment">';
                            }
                            return data;
                        },
                        className: 'dt-body-center' },
                    { data: 'status' },
                    { data: 'default_owner' },
                    { data: 'default_manufacturer' },
                    { data: 'default_mpn' }
                ],
                layout: {
                    topStart: {
                        buttons: [
                            { extend: 'create', editor: editor },
                            { extend: 'edit', editor: editor },
                            { extend: 'remove', editor: editor }
                        ]
                    }
                },
                order: [[1, 'asc']],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                rowCallback: function (row, data) {
                    // console.log('data', data);
                    // Set the checked state of the checkbox in the table
                    // console.log(row, "row");
                    if(data.assembled === null) {
                        $('input.assembled', row).prop('indeterminate', true);
                        $('input.assembled', row).prop('checked', false);
                    } else if(data.assembled == 1) {
                        $('input.assembled', row).prop('indeterminate', false);
                        $('input.assembled', row).prop('checked', true);
                    } else if(data.assembled == 0) {
                        $('input.assembled', row).prop('indeterminate', false);
                        $('input.assembled', row).prop('checked', false);
                    }
    
                    // $('input.assembled', row).prop('indeterminate', data.assembled == 0);
                    $('input.purchased', row).prop('checked', data.purchased == 1);
                    $('input.sold', row).prop('checked', data.sold == 1);
                    $('input.equipment', row).prop('checked', data.equipment == 1);
                }
            });
    
            // Activate an inline edit on click of a table cell
            // $('#example').on('click', 'tbody td:not(:first-child)', function (e) {
            //  editor.inline(this);
            // });
    
            $('#example').on('change', 'input.assembled', function () {
                editor
                    .edit($(this).closest('tr'), false)
                    .set('assembled', $(this).prop('indeterminate', false))
                    .set('assembled', $(this).prop('checked') ? 1 : 0)
                    .submit();
            });
    
            $('#example').on('change', 'input.purchased', function () {
                editor
                    .edit($(this).closest('tr'), false)
                    .set('purchased', $(this).prop('checked') ? 1 : 0)
                    .submit();
            });
    
            $('#example').on('change', 'input.sold', function () {
                editor
                    .edit($(this).closest('tr'), false)
                    .set('sold', $(this).prop('checked') ? 1 : 0)
                    .submit();
            });
    
            $('#example').on('change', 'input.equipment', function () {
                editor
                    .edit($(this).closest('tr'), false)
                    .set('equipment', $(this).prop('checked') ? 1 : 0)
                    .submit();
            });
        },
        vanilla: function () {}
    

    Here is the link for the current functionality: https://joomrp.urtestsite.com/portal/inventory/parts.html
    You need to look for Assembled field.

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin
    {
                    label: 'Assembled:',
                    name: 'assembled',
                    type: 'checkbox',
                    separator: '|',
                    options: [{ label: '', value: 1 }]
    }
    

    How is the use to indicate a 0 or null value? checkbox does have a unselectedValue option, but if you want the end user to select 0, 1 or null, you'd need at least one more option there. Probably a 0 and set the unselectedValue option to empty string (to represent a null) - although once a value has been selected, they'd never be able to unselect - so the solution really depends on what you actually want the end user to be able to select.

    Allan

  • danvanwijkdanvanwijk Posts: 17Questions: 4Answers: 0

    Actually, we need to use a three state checkbox like this one https://vanderlee.github.io/tristate/

    Default state will be indeterminate, then when we click once it will be checked. On second click it will be unchecked and on third click it will be indeterminate again and so on.

    Do you think you can suggest something to help with this?

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    Yes, for a tristate checkbox, you would need to create a custom field type plug-in that will work with such a plug-in.

    It might(?) sound a bit scary, but simple plug-ins for Editor are fairly straight forward when you break them down. See for example.

    Allan

  • danvanwijkdanvanwijk Posts: 17Questions: 4Answers: 0

    Ok. Can you provide some similar example on how to achieve tristate checkbox through it?

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    Custom development of a plug-in would be something that normally falls under the support packages, but I'll take a look and see what I can do next week. It might be a fun one to do.

    Allan

  • danvanwijkdanvanwijk Posts: 17Questions: 4Answers: 0

    Hi Allan,

    Thanks for considering to help us with this! Do you have any update regarding this?

    Thanks!

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    Yes, I've just knocked this example together.

    You'll notice that the tri-state values can be set via the options parameter for the new tristate field type. Those options just get passed through to the Tristate initialisation, so you can modify that object as needed. In your case, an empty string to represent null might be a good idea and then use a ifEmpty formatter at the server side to make it properly null.

    Allan

  • danvanwijkdanvanwijk Posts: 17Questions: 4Answers: 0

    Thanks, Allan! It seems to be working well for editing using select and edit. Do you think it will be possible to implement this for inline editing as well?

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    It should work out of the box for inline editing: https://live.datatables.net/lihadiga/2/edit .

    Allan

Sign In or Register to comment.