Searchpanes Help

Oburgwin

Screenshot of panes for context

In the status pane, I want to change each status to it's respective name, so rather than 1, it displays new

In the Port ETA pane, I'd like to change dates to the format to appear by Month. So 2021-09-04, 2021-09-16, 2021-09-17, etc would be merged into September 2021

In the CTR Size pane, the are two nulls, this happens in multiple panes when it displays the same data. In some it does, in others it doesn't. How would this be rectified, and changed to "Blank". Example below shows a separate case where it shows 'null' twice and '' twice but it's all the same selection

Any advice on these would be appreciated.

Particularly The Port ETA pane, I've read a few articles like this but not got as much out of it as I'd like

Oburgwin

Solved the Null Issue, I assume being able to rename 1 to new would also solve renaming "No Data" to "Blank"

Oburgwin

For Status label rendering, I tried:

{
    searchPanes: {
        show: true,
        options: [
            {
                label: 'New',
                value: function(rowData, rowIdx) {
                    return rowData['status'] === '1';
                }
            },
            {
                label: 'In Production',
                value: function(rowData, rowIdx) {
                    return rowData['status'] === '2';
                }
            },
            {
                label: 'Cargo Ready',
                value: function(rowData, rowIdx) {
                    return rowData['status'] === '3';
                }
            },
            {
                label: 'Loaded',
                value: function(rowData, rowIdx) {
                    return rowData['status'] === '4';
                }
            },
            {
                label: 'On The Water',
                value: function(rowData, rowIdx) {
                    return rowData['status'] === '5';
                }
            },
            {
                label: 'Arrived',
                value: function(rowData, rowIdx) {
                    return rowData['status'] === '6';
                }
            }
        ]
    },
    targets: [3]
}

However this caused the Searchpane to not function as a filter, the counts were all correct, but selecting an option would not give the relevant rows.

So instead I've changed the rendering in the server-side processing to be the below:

Field::inst('status')->searchPaneOptions(
            SearchPaneOptions::inst()
                ->render(function ($str){
                    switch ($str){
                        case '1':
                            return 'New';
                            break;
                        case '2':
                            return 'In Production';
                            break;
                        case '3':
                            return 'Cargo Ready';
                            break;
                        case '4':
                            return 'Loaded';
                            break;
                        case '5':
                            return 'On The Water';
                            break;
                        case '6':
                            return 'Arrived';
                            break;
                    }
                })
        ),

I'm fairly certain I could render the panes with dates in a similar way to band Months together.

I'll update this with my solution once I've got it, Maybe someone else finds it useful in future

Oburgwin

Field::inst('requestedshipdate')->searchPaneOptions(
    SearchPaneOptions::inst()
    ->render(function($str){
        return date('M Y',strtotime($str));
    })
)

Simple enough to format, but as above it's still split by day of the month.

Any ideas?

sandy

Hi @Oburgwin ,

This sounds like a use for columns.searchPanes.orthogonal data to me if you can work it on the client side. Take a look at this example that makes use of it for the status rendering.

Thanks,
Sandy

Oburgwin

Thanks for your response, i think this would have solved status' client side had I not done it server-side which is useful for future!

I tried applying the same fix to dates and did not work, I'll compile related code here now and hopefully can figure out what I need to do to combine the days in a Month to one.

Oburgwin

Code below, drilled down a bit. Has the serverside and clientside parts most relevant to dates. I don't think I've missed anything out

            var editor; 

            $(document).ready(function() {

                editor = new $.fn.dataTable.Editor( {
                    ajax: "/php/searchPanes.php",
                    table: "#example",
                    fields: [ 
                        {
                            label: "Requested Ship Date:",
                            name:  "requestedshipdate",
                            type:      'datetime',
                            def:       function () { return new Date(); },
                            displayFormat: 'DD/MM/YYYY',
                            wireFormat: 'YYYY-MM-DD',
                        },
                        {
                            label: "Cargo Ready Date:",
                            name:  "cargoreadydate",
                            type:      'datetime',
                            def:       function () { return new Date(); },
                            displayFormat: 'DD/MM/YYYY',
                            wireFormat: 'YYYY-MM-DD',
                        },
                        {
                            label: "Current ETD:",
                            name:  "currentetd",
                            type:      'datetime',
                            def:       function () { return new Date(); },
                            displayFormat: 'DD/MM/YYYY',
                            wireFormat: 'YYYY-MM-DD',
                        },
                        {
                            label: "Requested ETA:",
                            name:  "requestedeta",
                            type:      'datetime',
                            def:       function () { return new Date(); },
                            displayFormat: 'DD/MM/YYYY',
                            wireFormat: 'YYYY-MM-DD',
                        },{
                            label: "ETA UK:",
                            name:  "etauk",
                            type:      'datetime',
                            def:       function () { return new Date(); },
                            displayFormat: 'DD/MM/YYYY',
                            wireFormat: 'YYYY-MM-DD',
                        }
                    ]
                } );
                
                moment.updateLocale(moment.locale(), { invalidDate: "" });

                var table = $('#example').DataTable( {
                    ajax: {
                        url: "/php/searchPanes.php",
                        type: "POST"
                    },
                    columns: [
                       
                        { data: 'requestedshipdate', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )  },
                        { data: 'cargoreadydate', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )  },
                        { data: 'currentetd', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )  },
                        { data: 'requestedeta', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' ) },
                        { data: 'etauk', render: $.fn.dataTable.render.moment( 'DD/MM/YYYY' )  }
                    ],
                    searchPanes:{
                        initCollapsed: true,
                        cascadePanes: true,
                        layout: 'columns-7',
                        viewTotal: true
                    },
                    "iDisplayLength": -1,
                   
                    "order": [[ 7, "asc" ]],
                    columnDefs:[
                      
                        {
                            searchPanes:{
                                show: true,
                            },
// Removed some columns to make easier to read so this targets is not correct for the data here
                            targets: [1,2,3,4,5,7,8,9,10,11,12,13,15,16]
                        }


                    ],
                    serverSide: true,
                    processing: false
                } );


 $.fn.dataTable.render.moment = function ( from, to, locale ) {
                if ( arguments.length === 1 ) {
                    locale = 'en';
                    to = from;
                    from = 'YYYY-MM-DD';
                }
                else if ( arguments.length === 2 ) {
                    locale = 'en';
                }

                return function ( d, type, row ) {
                    if (! d) {
                        return type === 'sort' || type === 'type' ? 0 : d;
                    }

                    var m = window.moment( d, from, locale, true );

                    return m.format( type === 'sort' || type === 'type' ? 'x' : to );
                };
            };

searchPanes.php

<?php

// DataTables PHP library
include( "../lib/DataTables.php" );
include '../ASEngine/AS.php';

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\SearchPaneOptions;


// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst( $db, 'containers' )
    ->fields(
        Field::inst('currentetd')
            ->searchPaneOptions(SearchPaneOptions::inst()
                ->render(function($str){
                    if(strtotime($str) == 0){
                        return '';
                    }else{
                        return date('M Y',strtotime($str));
                    }
                })
            )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql('Y-m-d' )
            )
    )
    ->debug(false)
    ->process($_POST)
    ->json();

Hopefully I can have these merged into one soon enough. I can't imagine getting much success out of custom panes but that's my last resort.

Thanks again

sandy

Hi @Oburgwin ,

Could you please show me what your response from the server looks like?

Thanks,
Sandy

Oburgwin

Hi @sandy sorry for the slow response

Response below is extracted from 27000 lines. I think I've included everything and I've removed identifying information.

Dates are given like "Jan 1st, 2021" for now as it's a live system I'm pulling data from and without being able to combine data from a given month I've been displaying date labels in searchpanes as "M jS, Y" whereas with combined it would be as "M Y" as in previous posts here.

{
  "data": [
    {
      "DT_RowId": "row_13255",
      "ctr": "BR3",
      "factory": "Bris",
      "freightrate": "",
      "SO": "",
      "status": "5",
      "customer": "Otters",
      "requestedshipdate": "2021-07-27",
      "cargoreadydate": "2021-07-25",
      "currentetd": "2021-08-23",
      "requestedeta": "2021-09-20",
      "etauk": "2021-09-22",
      "vesselbookedon": "MUNKEBO MAERSK",
      "pod": "FELIXSTOWE",
      "ctrn": "FCIU",
      "term": "DDP",
      "forwarder": "Beacon/MRF",
      "ctr_size": "\t40'H",
      "comments": "",
      "equipment": "",
      "prange": "Turin"
    },
    {
      "DT_RowId": "row_509",
      "ctr": "OUD1",
      "factory": "Oude",
      "freightrate": "",
      "SO": "",
      "status": "1",
      "customer": "Summerhill Nurseries",
      "requestedshipdate": "2022-11-14",
      "cargoreadydate": "2021-11-07",
      "currentetd": null,
      "requestedeta": "2022-12-19",
      "etauk": null,
      "vesselbookedon": "",
      "pod": "",
      "ctrn": "",
      "term": "DDP",
      "forwarder": "Beacon/MRF",
      "ctr_size": "40’H",
      "comments": "",
      "equipment": "",
      "prange": "Catalan"
    }
  ],
  "options": [],
  "files": [],
  "searchPanes": {
    "options": {
      "ctr": [
        {
          "label": "OUD5",
          "total": "1",
          "value": "OUD5",
          "count": "1"
        },
        {
          "label": "TON4",
          "total": "1",
          "value": "TON4",
          "count": "1"
        }
      ],
      "factory": [
        {
          "label": "Bris",
          "total": "295",
          "value": "Bris",
          "count": "295"
        },
        {
          "label": "Genux",
          "total": "4",
          "value": "Genux",
          "count": "4"
        }
      ],
      "status": [
        {
          "label": "Cargo Ready",
          "total": "7",
          "value": "3",
          "count": "7"
        },
        {
          "label": "In Production",
          "total": "24",
          "value": "2",
          "count": "24"
        },
        {
          "label": "Loaded",
          "total": "24",
          "value": "4",
          "count": "24"
        },
        {
          "label": "New",
          "total": "731",
          "value": "1",
          "count": "731"
        },
        {
          "label": "On The Water",
          "total": "20",
          "value": "5",
          "count": "20"
        }
      ],
      "customer": [
        {
          "label": "Altons",
          "total": "2",
          "value": "Altons",
          "count": "2"
        },
        {
          "label": "Barker Bridge",
          "total": "18",
          "value": "Barker Bridge",
          "count": "18"
        }
      ],
      "requestedshipdate": [
        {
          "label": "Apr 11th, 2022",
          "total": "7",
          "value": "2022-04-11",
          "count": "7"
        },
        {
          "label": "Apr 12th, 2022",
          "total": "1",
          "value": "2022-04-12",
          "count": "1"
        }
      ],
      "cargoreadydate": [
        {
          "label": "",
          "total": "1",
          "value": null,
          "count": "1"
        },
        {
          "label": "Apr 11th, 2022",
          "total": "6",
          "value": "2022-04-11",
          "count": "6"
        },
        {
          "label": "Apr 12th, 2022",
          "total": "2",
          "value": "2022-04-12",
          "count": "2"
        },
        {
          "label": "Apr 13th, 2022",
          "total": "2",
          "value": "2022-04-13",
          "count": "2"
        },
        {
          "label": "Apr 15th, 2022",
          "total": "13",
          "value": "2022-04-15",
          "count": "13"
        },
        {
          "label": "Apr 19th, 2022",
          "total": "2",
          "value": "2022-04-19",
          "count": "2"
        },
        {
          "label": "Apr 1st, 2022",
          "total": "1",
          "value": "2022-04-01",
          "count": "1"
        }
      ],
      "currentetd": [
        {
          "label": "",
          "total": "740",
          "value": null,
          "count": "740"
        },
        {
          "label": "Aug 14th, 2021",
          "total": "11",
          "value": "2021-08-14",
          "count": "11"
        },
        {
          "label": "Aug 1st, 2021",
          "total": "2",
          "value": "2021-08-01",
          "count": "2"
        },
        {
          "label": "Aug 23rd, 2021",
          "total": "1",
          "value": "2021-08-23",
          "count": "1"
        },
        {
          "label": "Aug 2nd, 2021",
          "total": "3",
          "value": "2021-08-02",
          "count": "3"
        },
        {
          "label": "Aug 7th, 2021",
          "total": "3",
          "value": "2021-08-07",
          "count": "3"
        },
        {
          "label": "Nov 30th, 0001",
          "total": "1",
          "value": "0001-11-30",
          "count": "1"
        }
      ],
      "requestedeta": [
        {
          "label": "",
          "total": "6",
          "value": null,
          "count": "6"
        },
        {
          "label": "Apr 10th, 2022",
          "total": "3",
          "value": "2022-04-10",
          "count": "3"
        },
        {
          "label": "Apr 11th, 2022",
          "total": "5",
          "value": "2022-04-11",
          "count": "5"
        },
        {
          "label": "Apr 12th, 2022",
          "total": "1",
          "value": "2022-04-12",
          "count": "1"
        },
        {
          "label": "Apr 14th, 2022",
          "total": "2",
          "value": "2022-04-14",
          "count": "2"
        }
      ],
      "etauk": [
        {
          "label": "",
          "total": "740",
          "value": null,
          "count": "740"
        },
        {
          "label": "Nov 11th, 2021",
          "total": "11",
          "value": "2021-11-11",
          "count": "11"
        },
        {
          "label": "Nov 19th, 2021",
          "total": "1",
          "value": "2021-11-19",
          "count": "1"
        },
        {
          "label": "Nov 1st, 2021",
          "total": "2",
          "value": "2021-11-01",
          "count": "2"
        },
        {
          "label": "Nov 27th, 2021",
          "total": "1",
          "value": "2021-11-27",
          "count": "1"
        },
        {
          "label": "Nov 2nd, 2021",
          "total": "4",
          "value": "2021-11-02",
          "count": "4"
        }
      ],
      "vesselbookedon": [
        {
          "label": "",
          "total": "742",
          "value": "",
          "count": "742"
        },
        {
          "label": "CSCL ARCTIC OCEAN",
          "total": "2",
          "value": "CSCL ARCTIC OCEAN",
          "count": "2"
        },
        {
          "label": "EVER ACE ",
          "total": "1",
          "value": "EVER ACE",
          "count": "1"
        }
      ],
      "pod": [
        {
          "label": "",
          "total": "741",
          "value": "",
          "count": "741"
        },
        {
          "label": "Felixstowe ",
          "total": "47",
          "value": "Felixstowe ",
          "count": "47"
        },
        {
          "label": "Liverpool ",
          "total": "13",
          "value": "Liverpool ",
          "count": "13"
        },
        {
          "label": "London Gateway",
          "total": "3",
          "value": "London Gateway",
          "count": "3"
        },
        {
          "label": "Southampton",
          "total": "1",
          "value": "Southampton",
          "count": "1"
        },
        {
          "label": "Teesport",
          "total": "1",
          "value": "Teesport",
          "count": "1"
        }
      ],
      "term": [
        {
          "label": "DDP",
          "total": "510",
          "value": "DDP",
          "count": "510"
        },
        {
          "label": "DP",
          "total": "1",
          "value": "DP",
          "count": "1"
        },
        {
          "label": "FOB",
          "total": "214",
          "value": "FOB",
          "count": "214"
        },
        {
          "label": "Main Stock",
          "total": "81",
          "value": "Main Stock",
          "count": "81"
        }
      ],
      "forwarder": [
        {
          "label": "Beacon/MRF",
          "total": "589",
          "value": "Beacon/MRF",
          "count": "589"
        }
      ],
      "prange": [
        {
          "label": "Amalfi",
          "total": "52",
          "value": "Amalfi",
          "count": "52"
        },
        {
          "label": "Ashfield",
          "total": "9",
          "value": "Ashfield",
          "count": "9"
        }
      ]
    }
  },
  "draw": 1,
  "recordsTotal": "806",
  "recordsFiltered": "806"
}

Hopefully this is what you're after.

sandy

Hi @Oburgwin ,

Looking specifically at the requestedShipDate column, the data being shown in the SearchPanes is not the same as that being sent in your response...

"requestedshipdate": [
        {
          "label": "Apr 11th, 2022",
          "total": "7",
          "value": "2022-04-11",
          "count": "7"
        },
        {
          "label": "Apr 12th, 2022",
          "total": "1",
          "value": "2022-04-12",
          "count": "1"
        }
      ],

There must be something on the client side that is changing these further. If you are rendering on the ServerSide it is probably not a good idea to then render the SP options again on the client side. I think it would be best if you provide a test case that I can look at to see it all at once and do some debugging. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Thanks,
Sandy

Oburgwin

I'm sending labels as M jS, Y in this response if that's what you mean, I've done that to distinguish the dates in the searchpanes while I find a solution to the banded months problem. I haven't done any client side rendering that I'm aware of but I'll give it a look

Oburgwin

I'm replying to say I don't believe what I need is a feature yet.

Thank you for your time anyway