ColumnControl: using the searchList for entries that include commas

timb72

I have an issue where I'm building a searchList from database records, but the text for each searchList item can contain commas, but because the comma is used to delimit each searchList item this can "break" the items shown in the dropdown as it thinks the commas used in the text for each entry is also the delimiter. Hope that makes sense! So is there a way around this - if I was to escape the commas in the text first would that work? Or is there a way to change what character is used to delimit each entry of the searchList?

Thanks,
Tim

allan

Hi Tim,

but because the comma is used to delimit each searchList item this can "break" the items shown in the dropdown

I'm not sure about that I'm afraid. ColumnControl doesn't do a split on the strings for each cell. Here is a little example with a comma in one of the "Position" column cells: https://live.datatables.net/vupobixi/1/edit .

~~There is an error though, in the number column where something is causing it not to match - I'm not yet sure what that is, but I'll look into it.~~

edit: I was wrong about that - I had multiple filters applied without realising, so it didn't match the multiple filters! The number search list with the comma is in fact working fine.

Are you able to give me a link to a page showing the error you are seeing, or explain what makes you think it is a comma split that is causing the issue?

Allan

timb72

Thanks the quick reply Allan. I'm working on a site with quite sensitive data so I can't easily link to something just yet, but as an example - one of the rows returned from the database is
Rooftop Mortgages, Platform Home Loans, GMAC-RFC, Irish Permanent IOM

and in the searchList that should be one item, but its actually showing four items
Rooftop Mortgages
Platform Home Loans
GMAC-RFC
Irish Permanent IOM

This is all being returned in the columnControl section of an Ajax response - its returned on the first draw and not subsequent draws as we only need it the first time as it doesn't change. You helped me with that side of things over a year ago!

Tim

allan

Hi Tim,

Do you have server-side processing enabled? That might be the key piece of information that I'm missing here.

When using ColumnControl with server-side processing, assuming you are using one of the libraries I publish, you'll have a field with a columnControl method and Options instance - e.g.:

Field::inst( 'sites.name' )
    ->columnControl( Options::inst()
        ->table( 'sites' )
        ->value( 'name' )
        ->label( 'name' )
    )

That is what gets the list of options to display in the searchList. That, I'm guessing is a linked table, and you are perhaps doing an Mjoin to it for the display display?

Allan

timb72

Sorry yes I am doing servier-side processing. I think it may be related to how I'm constructing the list - I'm actually using a MSQL STUFF to create one long string of values - brace yourself because I'm actually working on a legacy Classic ASP site, and an example of this would be..

  If Len(strOriginator)>0 Then      
  outputColumnControl = outputColumnControl & _
  "," & _
  """Originator"": [" & _
    """" & replace(strOriginator,",",""",""") & """" & _
  "]"
  End If 

with strOriginator being the data pulled from MSQL like this..

Originator = STUFF((SELECT DISTINCT ','+[Originator] FROM theTable WHERE (coalesce([Originator],'')<>'') order by ','+[Originator] FOR XML PATH('')), 1, 1, '')

Old school! Think I need to revisit how I do this. But in theory you can have a comma separated text for an entry that will not be confused with the comma delimiter for server side processing?

timb72

Sorry Allan think this is more down to my backend coding than a datatables issue - think I've figured out a better way of doing this - thanks for being there though. Tim

allan

But in theory you can have a comma separated text for an entry that will not be confused with the comma delimiter for server side processing?

Hmmm - maybe. DataTables doesn't use comma delimiting for server-side processing parameters, so the fact that you've mentioned that, makes me think that there might be something else going on that I'm not aware of as well .

In principle, when you send a columnControl parameter back in the JSON data, the list of options given in the array for a column is what will be shown in the search list. It is not processed to split on a comma.

Allan