Using Case or If

[ABCRobstown]

I have 22 Mod fields to select from. Usually 4 or 5 are selected. In Mod 1 I enter a 1, in Mod 2, a 2, and so on.

I have another field called Modification. When I enter a number in a Mod field I want it to appear also in the Modification field.

This is my formula, but it's not working. It will enter only two numbers, not the four or five I may need. Also, I need to have a space between each number (for example 1 2 looks like twelve, 12).

Case(Mod 1, 1 & Mod 2, 2 & Mod 3, 3 & Mod 4, 4) and on through 22.

Surely will appreciate any help.

[Vaughan]

First off, FMP has a function called Mod() short for modulus. I recommend you change the field names to avoid confusion.

Case allows selection of ONE condition among many. What you need is to concatenate multiple fields together. So you need to use the "&" symbol.

Assuming that each Mod field is either empty or has the correct number in it, the function could probably something simple like:

Mod1 & " " &

Mod2 & " " &

Mod3 & " " &

Mod4 & " " &

Mod5 & " " &

Mod6 & " " &

...

Mod21 & " " &

Mod22

The problem here is that there will be unwanted spaces betwen the values ie, 1.2...5.6..8............. where a period represents a space. These shoudn't be too hard to remove.

However, using multiple fields to store the same thing usually points to sub-optimal data design. Without knowing your requirements I cannot suggest a better alternative (for instance, a singe field with checkboxes might be sufficient, or a instead use a relational structure). What usually happens, though is that all seems to be going well UNTIL you need to create a report, then it's discovered that the data isn't in a "shape" that fits the reporting requirements.

Resist the temptation to use repeating fields.

[ABCRobstown]

Thank you Vaughn for your help. It works perfectly, but as you said the spaces are a problem. I think I would have to widen my field the width of the paper.

Since I am not that FMP savy, would you mind telling me how to remove the spaces?

Thanks so much.

Gaynell

[comment]

You could try something like:

Case ( Mod 1, "1 " ) &

Case ( Mod 2, "2 " ) &

Case ( Mod 3, "3 " ) &

...

Case ( Mod 22, "22 " )

Overall, it seems a very awkward arrangement to have.

Without knowing your requirements I cannot suggest a better alternative

Ditto.