setFormatter always returning ifEmpty format

rwgreenwood

I have this on an editor field
->setFormatter('Format::ifEmpty', null),

but I am always getting null, even if it's not empty.
The field is valued in the form data properly.
I can't see what I am missing.
Thanks

allan

Could you show me your full PHP code and Javascript code please?

Thanks,
Allan

rwgreenwood

Sure. It's the acres_inspected column and it is a numeric in the database.

PHP :

// Build our Editor instance and process the data coming from _POST
$haycert_editor = Editor::inst( $db, 'service_haycert', 'id' )
    ->fields(
        Field::inst( 'service_haycert.case_id' ),
        Field::inst( 'service_haycert.certifcate_date' )
          ->validator( 'Validate::dateFormat', array(
              'empty' => false,
              'format' => 'm/d/Y'
          ) )
          ->getFormatter( 'Format::datetime', array(
              'from' => 'Y-m-d',
              'to' =>   'm/d/Y'
          ) )
          ->setFormatter( 'Format::datetime', array(
              'from' => 'm/d/Y',
              'to' =>   'Y-m-d'
          ) ),    
        Field::inst( 'service_haycert.staff_id' )
         ->options( 'staff', 'staff_id', 
            'staff.first_name || \' \' || staff.last_name' ,
            function($query) {
                $query->where('status','Active','=');
            }
        ),  
        
        Field::inst('staff.last_name'),
        Field::inst('staff.first_name'),
        Field::inst( 'service_haycert.field_description' ),
        Field::inst( 'service_haycert.acres_inspected')
            ->setFormatter('Format::ifEmpty', null),
        Field::inst( 'service_haycert.package_type' ),
        Field::inst( 'service_haycert.cutting' )
            ->options(function() {
                $out = array(array("value"=>"First","label"=>"First"),
                    array("value"=>"Second","label"=>"Second"),
                    array("value"=>"Third","label"=>"Third")
                );
                return $out;
            })
        ,
        Field::inst( 'service_haycert.forage_product' )
            ->options(function() {
            $out = array(array("value"=>"Barley Straw","label"=>"Barley Straw"),
                  array("value"=>"Alfalfa","label"=>"Alfalfa"),
                  array("value"=>"Alfalfa Grass","label"=>"Alfalfa Grass"),
                  array("value"=>"Grass","label"=>"Grass")
            );
            return $out;
        })
        ,
        Field::inst( 'service_haycert.requirement_level' )
            ->options(function() {
                $out = array(array("value"=>"A","label"=>"Exceeds requirements"),
                    array("value"=>"B","label"=>"Meets requirements"),
                    array("value"=>"C","label"=>"Complies with minimum requirements")
                );
                return $out;
            })
        ,
        Field::inst( 'service_haycert.weeds_noted' ),
        Field::inst( 'service_haycert.additional_comments' )
    )
    ->leftJoin('staff','staff.staff_id', '=', 'service_haycert.staff_id')
    ->where('service_haycert.case_id', $case_id, '=')
    ->process( $_POST )
    ->json();

and the javascript:

          certificateEditor = new $.fn.dataTable.Editor ({
            ajax: "php/table.service_haycert.php",
            table: "#certificate-table",
            fields: [
              {
                label: "Certified By",
                name: "service_haycert.staff_id",
                type: "select"
              },

              {
                label: "Date",
                name: "service_haycert.certifcate_date" ,
                type:   'datetime',
                def:    function () { return new Date(); },
                //format: 'M/D/YYYY h:mm A',
                format: 'MM/DD/YYYY',
                fieldInfo: 'US style mm/dd/yyyy format no time'
              },
              {
                type: "hidden",
                name: "service_haycert.case_id" ,
                def: function () { return currentCase.caseId; }
              },
              {
                label: "Cutting",
                name: "service_haycert.cutting",
                type: "select"
              },
              {
                label: "Product",
                name: "service_haycert.forage_product",
                type: "select"
              },
              {
                label: "Requirement Level",
                name: "service_haycert.requirement_level",
                type: "select"
              },
              {
                label: "Weeds noted",
                name: "service_haycert.weeds_noted"
              },
              {
                label: "Acres inspected",
                name: "service_haycert.acres_inspected"
              },
              {
                label: "Field description",
                name: "service_haycert.field_description"
              },
              {label: "Additional Notes", name: "service_haycert.additional_comments"}
            ]
          })
          .on('initCreate', function (e, node, data) {
                $.ajax({
                  url: "include/service_case.php",
                  data: 'action=getPrimaryLocation&case_id=' + id ,
                  type: "GET"
                }).done(function(json) {
                  certificateEditor.field('service_haycert.acres_inspected').val(json.acreage);
                  certificateEditor.field('service_haycert.field_description').val(json.site_description);
                }); 
              })          
          .on( 'preSubmit', function ( e,  data, action ) {
            data.case_id = currentCase.caseId;
          });
          certificateTable = $("#certificate-table").DataTable({
            dom: "Brtip",
            ajax: {
              url: "php/table.service_haycert.php",
              data: function(d) {
                d.case_id = currentCase.caseId;
              }
            },
             "columns": [
              { "title": "Date", "orderable": "false", data: 'service_haycert.certifcate_date'},
              {"title": "Certified By", data: "staff",
                "render": function(data, type, full) {
                  return data.first_name ? data.first_name + " " + data.last_name : data.last_name
                }
              },
              {"title": "Cutting", data: "service_haycert.cutting"},
              {"title": "Product", data: "service_haycert.forage_product"}
            ],
            "paging": false,
            "language": {
              "zeroRecords": "No certifications found"
            } ,

            select: true,
            buttons: [
              { extend: "create", editor: certificateEditor },
              { extend: "edit",   editor: certificateEditor },
              { extend: "remove", editor: certificateEditor }

            ]

          });
        } else {
          certificateTable.ajax.reload();

        }
      }

allan

I must confess, I don't immediately see why that isn't working I'm afraid, and I've not been able to reproduce it here.

Could you add a new line immediately before ->process( $_POST ) which is:

->debug( true )

That will just include the SQL that Editor is executing in the JSON response, which you will be able to see in the Network inspector of your browser. Could you show me what that says?

Thanks,
Allan

rwgreenwood

See next comment for info

rwgreenwood

OK, went to 1.6 and got this:
An SQL error occurred: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: ""An SQL error occurred: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block{"fieldErrors":[],"error":"Error executing SQL for data get. Enable SQL debug using ->debug(true)","data":[],"ipOpts":[],"cancelled":[],"debugSql":[{"query":"SELECT * FROM service_haycert WHERE service_haycert.id = :where_0 ","bindings":[{"name":":where_0","value":"3","type":null}]},{"query":"UPDATE service_haycert SET case_id = :case_id, certifcate_date = :certifcate_date, staff_id = :staff_id, field_description = :field_description, acres_inspected = :acres_inspected, cutting = :cutting, forage_product = :forage_product, requirement_level = :requirement_level, weeds_noted = :weeds_noted, additional_comments = :additional_comments WHERE service_haycert.id = :where_0 ","bindings":[{"name":":case_id","value":"3596","type":null},{"name":":certifcate_date","value":"2017-06-15","type":null},{"name":":staff_id","value":"","type":null},{"name":":field_description","value":"","type":null},{"name":":acres_inspected","value":null,"type":null},{"name":":cutting","value":"First","type":null},{"name":":forage_product","value":"Alfalfa Grass","type":null},{"name":":requirement_level","value":"A","type":null},{"name":":weeds_noted","value":"","type":null},{"name":":additional_comments","value":"","type":null},{"name":":where_0","value":"3","type":null}]},{"query":"SELECT service_haycert.id as \"service_haycert.id\", service_haycert.case_id as \"service_haycert.case_id\", service_haycert.certifcate_date as \"service_haycert.certifcate_date\", service_haycert.staff_id as \"service_haycert.staff_id\", staff.last_name as \"staff.last_name\", staff.first_name as \"staff.first_name\", service_haycert.field_description as \"service_haycert.field_description\", service_haycert.acres_inspected as \"service_haycert.acres_inspected\", service_haycert.package_type as \"service_haycert.package_type\", service_haycert.cutting as \"service_haycert.cutting\", service_haycert.forage_product as \"service_haycert.forage_product\", service_haycert.requirement_level as \"service_haycert.requirement_level\", service_haycert.weeds_noted as \"service_haycert.weeds_noted\", service_haycert.additional_comments as \"service_haycert.additional_comments\" FROM service_haycert LEFT JOIN staff ON staff.staff_id = service_haycert.staff_id WHERE service_haycert.case_id = :where_0 AND service_haycert.id = :where_1 ","bindings":[{"name":":where_0","value":"3596","type":null},{"name":":where_1","value":"3","type":null}]}]}

This is a postgres database, the column is numeric and allows nulls. Thanks in advance.

allan

Sorry about this - one more bit of information needed. Could you show me the data that is set to the server with the edit request? That is shown in the "Headers" tab of the network request.

Thanks,
Allan

rwgreenwood

Allan, here you go.
action:edit
data[row_3][service_haycert][staff_id]:
data[row_3][service_haycert][certifcate_date]:06/15/2017
data[row_3][service_haycert][case_id]:3596
data[row_3][service_haycert][cutting]:First
data[row_3][service_haycert][forage_product]:Alfalfa Grass
data[row_3][service_haycert][requirement_level]:A
data[row_3][service_haycert][weeds_noted]:
data[row_3][service_haycert][acres_inspected]:
data[row_3][service_haycert][field_description]:
data[row_3][service_haycert][additional_comments]:
case_id:3596

allan

Thank you. It is showing:

data[row_3][service_haycert][acres_inspected]:

So it is correct that the null would be submitted into the database under that condition.

However, that obviously isn't what you are expecting to happen - do you have a value in that field in the Editor form?

Allan

rwgreenwood

Sorry I've been slow getting back to this. This is my editor form setup:

{
                label: "Acres inspected",
                name: "service_haycert.acres_inspected"
              },

Is that what you were asking?

rwgreenwood

I think I see the issue was something completely different than what I had first thought. Once I fixed what the REAL error was, it seems to be functioning properly. Thanks for you time, it helped me work through it.

allan

Thanks for posting back - great to hear you've got it working now.

Allan

stevevance

I'm having the opposite problem. setFormatter doesn't seem to be invoked when that field has an empty value.

Field::inst( 'places_json' )
            ->setFormatter( 'Format::ifEmpty', null )
            ->getFormatter( function($val, $data, $opts) { // format the server value for the client
                error_log("getFormatter: ". serialize($val));
                if(!empty($val)):
                    $values = json_decode($val, true); // convert JSON string to array
                    $slugs = [];
                    foreach($values as $slug):
                        $slugs[] = [
                            "slug" => $slug
                        ];
                    endforeach;
                    $response = $slugs;
                else:
                    $response = null;
                endif;
                return $response;
            }),

Here's how I figured out that setFormatter isn't being invoked:

->setFormatter( function($val, $data, $opts) { 
                // format the client value for the server
                // convert array from the browser to JSON string for storage in the database
                error_log("setFormatter: ". serialize($val));
                if(!empty($val)):
                    $response = json_encode($val);
                else:
                    $response = json_encode([]);
                endif;
                return $response;
            })

I can't see the setFormatter message from the error_log function in my php_error.log file, but I can see the getFormatter message.

allan

Can you show me the data being submitted by the client-side please? You'll be able to get that from the Header part of the Networking tab for that Ajax request.

Allan