8 Replies Latest reply on Sep 8, 2011 12:29 PM by philmodjunk

# Use values from repeating field in a text calculation?

### Title

Use values from repeating field in a text calculation?

### Post

Hey folks...

First off, I know repeating fields are evil, but they happen to fit the application in question. What I'm curious about is whether there is a way to assemble the values found in a repeating field into a calculated text string?  For example if FRUIT[1]="apples", FRUIT[2]="oranges" and FRUIT[3]="bananas"... is there a way to complile them into a text string: "apples, oranges and bananas."

Also... is there a way to have it skip any blank entries so if FRUIT[4], FRUIT[5] AND FRUIT[6] are empty it will still return "apples, oranges and bananas"... or for that matter if FRUIT[2] is empty, just "apples and banans"??

Suddenly I'm strangely hungry... anyway, any thoughts... or better ideas??

Cheers

• ###### 1. Re: Use values from repeating field in a text calculation?

In principle, this would work:

Build all the fruits together, separated by a comma

Case (

IsEmpty (Fruit[1]) ; "" ;

Fruit[1] & ","

)

&

Case (

IsEmpty (Fruit[2]) ; "" ;

Fruit[2] & ","

)

&

Case (

IsEmpty (Fruit[3]) ; blah blah

)

That will give you apple, banana, orange,

Then strip off the last comma using the Left function, for the Length of that field - 1

Then Substitute the last comma for an ' and'

And no, I haven't actually done it, as you can tell.

• ###### 2. Re: Use values from repeating field in a text calculation?

Hmm... that'll work.

• ###### 3. Re: Use values from repeating field in a text calculation?

Repeating fields aren't evil, they are just rarely the optimum solution in a relational database. This could easily be such a case here.

Try this simpler expression:

Substitute ( List ( Fruit ) ; ¶ ; ", " )

• ###### 4. Re: Use values from repeating field in a text calculation?

Wow... that is simpler.  Thanks PhilModJunk.  Another FM function I didn't know existed saves the day.

Cheers.

• ###### 5. Re: Use values from repeating field in a text calculation?

Hmm...  There's no 'and' on the end.

• ###### 6. Re: Use values from repeating field in a text calculation?

Nor should there be if you are referring to my suggested expression.

• ###### 7. Re: Use values from repeating field in a text calculation?

Sorry - I was being too particular to the original query; it said, ""apples, oranges and bananas" hence I suggested the last Substitute.

• ###### 8. Re: Use values from repeating field in a text calculation?

Sorry, I'm speeding through a very large list of posts and missed that detail... The expression could be modified to do that.