Data Written from Menu Lists has Extra Characters

6 posts by 2 authors in: Forums > CMS Builder
Last Post: September 17, 2008   (RSS)

By InHouse - September 15, 2008 - edited: September 15, 2008

A new client has successfully used CMSB as a data-entry tool for several thousand records. One of their fields was pull-down list containing the two-letter province abbreviations.

Now I'm trying to sort the info by that province field and find that all the data entered therein has (I think) a tab character both before and after the two letters.

Any suggestions on what is going on here? Better yet, any thoughts on identifying and changing this info would be appreciated.

J.

PS: screenshots of limited usefulness included.
Attachments:

extratabs-navicat.jpg 102K

extratabs-phpmyadmin.jpg 37K

Re: [InHouse] Data Written from Menu Lists has Extra Characters

By Dave - September 15, 2008

Hi J,

Multi-value lists are stored that way for easy parsing and matching. Can you check if that's a multi value list? If so is it possible to covert it to a single value list?

We may be able to do a database query to remove all the tabs after. So long as none of the fields actually have multiple values.

Let me know and I'll help you sort it out.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Data Written from Menu Lists has Extra Characters

By InHouse - September 16, 2008

Hi Dave,

The data are def. single values but the editor is set for Multi. Obviously a careless key click on my part. :-(

If I knew what the extra characters were I could probably scrub the database myself.

Many thanks,
J.

Re: [InHouse] Data Written from Menu Lists has Extra Characters

By Dave - September 16, 2008

The extra characters are tabs. The values are separated by tabs and the field starts and ends with a tab. This is so every value will be surrounded by tabs and we can match a specific value with a single query:

LIKE "%\tVALUE\t%"

Without having to account for values that might be at the beginning or end of the string.

If you run a query (backup first) to just strip out the tabs you should be fine.

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Data Written from Menu Lists has Extra Characters

By InHouse - September 16, 2008

Understood. Many thanks Dave. Will let you know how we got on.
J.