SearchBuilder note relating to columns rendered with mixed types

LimpEmu

Link to test case: live.datatables.net/likeqivi/1/edit?html,output
Debugger code (debug.datatables.net): N/A
Error messages shown: No errors
Description of problem: I am having a hard time figuring out how to render columns differently for exporting, displaying, sorting and filtering. It's my understanding that SearchBuilder uses a column's type to figure out the "Condition" drop-down and the display value to figure out the "Value" drop-down.

My problems stem from that for my data I need to distinguish fields specified as Unknown and fields not specified (Pending). Typically, to represent an Unknown value for a numeric field, internally a number is used that cannot ever occur for that field. For a pending field, the value for the field is empty (null). When interacting with the user, rather than displaying the internal value, some text string is shown, e.g., UNK for unknown.

The test case has two examples, mother's birth date and mother's age.

For Mother's Age the internal representation 999 is used.

          {data:"mage",title:"Mother's Age V1",className:"dt-center nvr qry","visible":true},
          {data:null,title:"Mother's Age V2",className:"dt-center nvr qry","visible":true,
               render: function(data,type) {
                   var thisval=data.mage;
                   if (thisval == 999) return "UNK";
                   else return thisval;
               }
           },
          {data:null,type:"num-fmt",title:"Mother's Age V3",className:"dt-center nvr qry","visible":true,
               render: function(data,type) {
                   var thisval=data.mage;
                   if (thisval == 999) return "UNK";
                   else return thisval;
               }
           },

V1 does not use a render option. V2 and V3 both use the render option. For V3, I have added the type:"num-fmt" option.

For V1, I have to live with showing the internal value to the user. Sorting and SearchBuilder both work as intended.
For V2, sorting works, but SearchBuilder thinks that the column is of type string and gives me the associated Condition drop-down.
For V3, displaying, sorting, exporting work and SearchBuilder is very close. The only problem with SearchBuilder is that the result of Equal to UNK searches include both, rows with maternal age pending or unknown.

So for now, I am going with V3, but of course, the use of the type option should be avoided, so I am not sure this is optimal.

The second example is Mother's birth date. I am using the date 09-09-1909 to internally represent an Unknown birth date.

           {data:"mdate",title:"Mother's DOB V1",className:"dt-center core qry exp","visible":true},
           {data:"mdate2",title:"Mother's DOB V2",className:"dt-center core qry exp","visible":true,
             render: function (data,type) {
                var thisval;
                if (isEmpty(data)) thisval='Pending';
                else if (data == "09-09-1909") thisval='Unknown';
                else if (type == 'sort') thisval=moment(data,"MM-DD-YYYY").format("YYMMDD");
                else if (type == 'display') thisval=moment(data,"MM-DD-YYYY").format("MM-DD-YY");
                else thisval = data;
                return thisval;
           }},
           {data:null,title:"Mother's DOB V3",className:"dt-center core qry exp","visible":true,
             render: function (data,type) {
                var thisval=data.mdate;
                if (isEmpty(thisval)) return null;
                else if (type == 'sort') return moment(thisval,"MM-DD-YYYY").format("YYMMDD");
                else if (type == 'display') return moment(thisval,"MM-DD-YYYY").format("MM-DD-YY");
                else return thisval;
           }},

V1 does not use a render option. V2 uses a render option to a) show unknown and pending values as 'Unknown' and 'Pending.' My data are formatted MM-DD-YYYY, but for this example I want to render the column MM-DD-YY, I also need to make sure that the sort order is YYMMDD. For V3 I removed the display options for Unknown and Pending.

For V1, I have to live with showing the internal value to the user. Sorting and SearchBuilder both work as intended. For the export I would have to add a special rule to make sure the 09-09-1909 is converted to Unknown.
For V2, display, export and sort work, however, SearchBuilder no longer uses the Condition drop-down for dates.
For V3, sort and SearchBuilder both do not work.

It seems that unless I go with the default display for dates, I have to use a work-around. My work-around adds a second hidden column for SearchBuilder that searches are done on (unfortunately, they do include the unknown value in the internal format) while the column displayed to the user uses V2.

Do you have an idea how to better address these situations?

sandy

Hi @LimpEmu ,

Leave this one with us please. This thread lead to us looking into adding an orthogonal option for SearchBuilder that would solve your issues. We are in the process of testing this now and hope to have a fix for it very soon. I'll give you a shout with a solution when we get it working as we would expect

Thanks,
Sandy

LimpEmu

Thank you, @sandy , I much appreciate your response and work on this. It is already a very useful new feature and will only get better, I am sure.

sandy

Hi @LimpEmu ,

We've added the orthogonal option, you can see an example of it in action here. That being said, I'm not actually sure if it is actually going to help here on further investigation.

I've added a column to your example.It's based quite strongly on your V2 column, as this seemed closest to what you wanted? I've set the column type to date so that the datepicker is displayed. I've also added another check for filtering so that the Pending or Unknown text is not returned, but null is instead, which works with SB. You can then also make use of the null option within SB.

Does that help at all?

Thanks,
Sandy

LimpEmu

I like the change you made, this definitely helps me. I prefer it to showing the value of the internal unknown value, and it should also help me in situations where a time is unknown. I will work with this a bit more and let you know if I find any bigger problems.
Thank you!

LimpEmu

Never mind, I just posted a comment that can be ignored.