Need to store both Null and 0 in database

danvanwijk

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?

Aryan1703

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

danvanwijk

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) )

Aryan1703

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))

danvanwijk

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!

allan

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

danvanwijk

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.

allan

{
                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

danvanwijk

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?

allan

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

danvanwijk

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

allan

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

danvanwijk

Hi Allan,

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

Thanks!

allan

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

danvanwijk

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?

allan

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

Allan