List Builder

[michaelnyc]

Hey,

Again I appeal to the Forum. I have a script that looks like the following:

Loop

Set Variable [$loop1 : $loop1 + 1]

Set variable ( [$variable] : $variable & ¶ & Evaluate ( "field" & $loop1 ) )

end loop if [$loop1 = 10]

End loop

What I'm trying to do is take values from 10 fields titled "field1", "field2", "field3" and so on and insert them into a list variable ($variable) that builds as the loop goes on. The problem I'm facing is with empty values, especially if "field1" is empty. The script will build so that if "field1" is empty, the value for "field2" will be preceded by 2 carriage returns, not one. If I change the calculation to

"¶ & Evaluate ( "field" & $loop1 )"

it will give me a leading carriage return even if "field1" is populated, and conversely

"Evaluate ( "field" & $loop1 ) & ¶"

will give me an ending carriage return.

I feel like this should be simple but I want only 10 values in $variable that build exactly as the values of the fields are. Am I not thinking of something simple?

Thanks in advance for any help.

Michael

[RalphL]

I would change the script to:

Set Variable [$loop1 : 1]

Set variable ( [$variable] : Evaluate ( "field" & $loop1 ) & ¶ )

Loop

Set Variable [$loop1 : $loop1 + 1]

Set variable ( [$variable] : $variable & Evaluate ( "field" & $loop1 ) & ¶)

Exit loop if [$loop1 = 10]

End loop

[comment]

You could set your $variable to:

Let (

item = Evaluate ( "field" & $loop1 )

;

$variable & Case ( not IsEmpty ( item ) ; item & ¶ )

)

Or, you could just write it out as:

List ( field1 ; field2 ; field3 ; ... ; field10 )

However, if you have 10 fields named field1, field2, etc., AND you need to list them, then it's very likely you should have a related table of 10 (or any number) records instead. Then you can get your list very easily by List ( related::field ).

[Ocean West]

You could do this without a script & loop using a custom function: http://www.fmfunctions.com/fid/118

[michaelnyc]

For the first option, wouldn't it give me a trailing carriage return?

And for the second it wouldn't take into account empty fields, right?

I really appreciate the help with this issue. I seem to be stumped.

[michaelnyc]

I tried this and it seems to be giving me a trailing carriage return. Should I just build in a manual check that says

Set Variable ([$variable] ; $variable & Evaluate ("field" & $loop1) & (If ($loop1 < 10 ; ¶ ))

[comment]

Yes to both. But a trailing CR is not necessarily an obstacle: all Filemaker's xValues functions return a trailing return. And you can remove it at the end.

But I suggest you consider the third option.

[michaelnyc]

Makes sense. Thanks for the help, comment.

[MHAriete64]

I have two questions from reading this custom function.

1) Can I do this with a non-numeric set of data? For instance. Get(TableNames) produces a list of all my table names separated by a paragraph mark and I want to cycle through and pull out each Table name to insert into its own field. This way I create a list of the Table names (so I can develop a value list of my Table Names).

2) Once I place each of these Table Names into a separate field in their own table, can I go on to create new fields in another table with these Table Names as Field Names.

Essentially, I'm trying to automate the creation of fields in a table. I want to create a "flat file" with data from multiple tables. So, I'm considering taking an empty table and creating new fields with data from multiple fields.

[bruceR]

List( Field1; Field2; Field3; ... Field10)