I access much our data via ODBC from web scripts. When I pull a field consisting of a value list via ODBC I get all checked items in that field separated by spaces. I can then split this out and use it however necessary in my web scripts...for the most part.

The problem that I'm having is with value lists that contain something similar to the following, a list of accessories included with a computer...

Keyboard - USB

Mouse - USB

Restore CD

If I pull this into a web script via ODBC I get the following:

Keyboard - USB Mouse - USB Restore CD

So the problem here is that if I split using a space I end up with the following:

Keyboard

-

USB

Mouse

-

USB

Restore

CD

Instead of

Keyboard - USB

Mouse - USB

Restore CD

If, instead of a space, I could separate the values with something like a |...

Keyboard - USB | Mouse - USB | Restore CD

Then I could split on the | and would get the values I'm expecting. Any idea how I can make FM use something besides a space here... Any information I can get would be greatly appreciated. Thanks!

Hi Drew

FMP uses a carriage return to seperate multiple values in one field, not sure why it comes through ODBC as a space.

You can try creating a calcfield with result as text as follows:

Substitute (MyListField; "¶" ; "|")

See what happens if you pull the values from the calcfield rather than the MyListField.

That gives me a SQL error when I try and pull in the calculated field. "Error in result columns."

Just to add on, that calculation does work within FileMaker. If I drag the calc. field out into a layout I do indeed get the checked items separated by |. So that would be perfect except that when I try and pull this calc. field into my web script via ODBC I get the following error:

[DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Error in Result Columns

And the the web script can't even load because of that. As soon as I remove the calc. field from my SQL SELECT in the web script it works fine again.