Creating a Value ValueList from a single global Text field with carriage returns

[Spark63]

Hello, I am trying to create a ValueList from a global field that contains a set of data separated by a carriage return.

The reason is that I have a CSV Text values with carriage returns on the global field, I want the user to specify which line item he wants from the CSV Text field.

The long way is to dump CSV data file into a table and then create it throug the Value List. Is there a shorter way, where I can directly populate the ValueList without creating a table.

I know you can extract the ValueList through the function ValueListItems, but i want to do the opposite, to push the data into the ValueList.

Thanks for any help...

[Fenton]

You cannot create a Value List from a global field, because a value list requires that the field you use be able to be indexed, which a global field cannot. Nor can you populate a value list directly. It would be useful to be able to I suppose, but as of now you can't.

Another possibility, since you are also accessing an external file, would be to use AppleScript, in a Perform AppleScript script step, which can both choose the file, read the file, and offer to choose from the list with multiple selections allowed. The AppleScript code is very much like the words. You would likely need to put the result into a field in FileMaker.

set file_ to choose file without invisibles

tell application "Finder" to set txt to read file_

set txt_list to paragraphs of txt

set choices to choose from list txt_list with multiple selections allowed

set choices_txt to choices as text

--tell application "FileMaker Pro Advanced"

set cell "CSV" of current record to choices_txt

save record

--end tell

You need to use the tell app "Finder" syntax within FileMaker, as it does not like the AppleScript read command.

You do not need to use the tell app "FileMaker" lines within FileMaker, as it knows who it is; but you need to uncomment them if running within Script Editor.

The above works fine with file with old style Mac returns (ASCII 13), but it sets ones with Unix style returns (ASCII 10) into FileMaker as one line. That could be fixed by explicitly translating. But it's almost midnight, so I've run out of steam.

Or you could just import the file into a temp table, and use that for the value list, or use a portal matching with Cartesian X operator, to match all the lines.

Or you could put the text in a regular text field, in a 1 record table, and use that for the value list.

[i should add that the "put the contents into a 1 record table" would not work properly in a multi-user environment. You would need to put it in a multi-record "user" table or something like that.]

Edited March 7, 2009 by Guest

[comment]

Or you could just import the file

That seems to me the most reasonable option. The entire Filemaker application is designed to handle data in records and fields. Placing an entire table in a single field requires custom building of all the tools to handle it - tools that would be otherwise readily available from within Filemaker.

[dwins]

A script can extract, or act on, a particular value, where the cursor is placed in a block of text.

[Fenton]

Here is an alternate method, proposed by Bruce Robertson. It exploits GetValue (text, line#) to arrange the contents of a global text field into separate records (or a portal). All it requires is a table with incremental serial numbers, 1...

http://fmforums.com/forum/showtopic.php?tid/197205/post/299290/hl/Array/fromsearch/1/

[comment]

Ahm... that would be an unstored calculation - so you still wouldn't be able to make a value list out of it.

[Fenton]

No, it's more of a "view and pick from portal" kind of thing.

[Spark63]

Thanks to all, its been very insightful. I will implement the applescript, otherwise i will create a table. Many thanks again.