Verrry long Case statement

[bbaliner]

Hi all,

I have 16 fields, and have to write a case statement that says:

Case(any one of these fields = some result and

any other one of these fields = some result, 1, 0)

There are 256 different possibilities right there, but what if you have 32 such fields, or more?? This could get maddening. Is there some function in File Maker to aliviate the pain of this? What is done in such case?

Thank you

[Thom]

Try this:

((field1=val1) + (field2=val2) + (field3=val3)) >= 2

[LiveOak]

Last time I checked, 2**16 was a lot larger than 256!

The other approach is to make the whole thing "table driven". That is to say create another file with a table of input values and results. Create a composite key by concatenating (I'm assuming the contents of your 16 fields are binary, yes/no) the contents of the 16 fields into a number (1011011101010010) and using this as a key to find a related result. This just picks out one of the 2**16 enteries in your second file and give the contents of the result field. Your new file's records look like:

0000000000000000 1 <-- or whatever the result is

0000000000000001 0

0000000000000010 0

0000000000000011 1

.

.

.

1111111111111111 1 This is the 65,536th entry.

If you really want 32 bits, you'll exceed the 20 character indexing capability for FM for a single "word". In this case make your key fields 2 groups of 16 digits separated by a space. The indexing capacity on one line is 60 characters for all "words". For example:

1011111111011111 1010110010101011

This is the electronic equivalent of a book of tables. Instead of a formula, you just lookup the result based upon the input.

-bd

[bbaliner]

OK, I will give here an example of a similar problem with 8 fields, not 16.

Suppose fields are:

Brother1

Brother2

Brother3

Brother4

Sister1

Sister2

Sister3

Sister4

I also have 8 fields for their ages, such as Brother1_Age. But this does not complicate things, because I use a simple and, such as:

case((Brother1="y" and Brother1Age<45) and (Brother2="y" and Brother2Age<45), 1, 2)

These case statements are in the calculated field called "Priority", which can only be either 1 or 2. When I have 2 siblings that are both less than 45, Priority should be 1.

I got a feeling that there's a simple solution, but I'm missing it. Please help.

[LiveOak]

Aha! This is really a boolean algebra question. Thom and I have given you the two extremes. You don't necessarily need a complex equation or the huge table approach to accomplish what you wish. You do, however, need to define your logic in table form and preform a boolean reduction of the function to be implemented. For instance, if I have three fields and a result as follows:

A B C Result

0 0 0 0

0 0 1 0

0 1 0 0

0 1 1 0

1 0 0 0

1 0 1 0

1 1 0 0

1 1 1 1

This is just the boolean equation Result = A AND B and C, a simple AND of the three input fields.

Another example:

A B C Result

0 0 0 0

0 0 1 0

0 1 0 0

0 1 1 0

1 0 0 1

1 0 1 1

1 1 0 1

1 1 1 1

Unreduced, this is just the boolean equation:

Result = (A AND notB AND notC) OR (A AND notB AND C) OR (A AND B AND notC) OR (A AND B AND C). But, reduced it is just: Result = A

This point of all this is you need to itemize all your imputs and the resulting output in a table and then perform a boolean algebra reduction using some technique (sorry, some things lapse into engineering). You might need to get some help, if the logical result isn't simple enough to guess.

-bd

[bbaliner]

Oak, this is interesting. Let me see if I get this straight, though. Instead of Calculated field you're suggesting to use a look up. Yopu're suggesting to create File2.

In File1 create Calc filed: If(Field1="yes",0000000000000001, 0000000000000000) And so forth for each of 16 fields.

Then if In File1 Field1="yes" in File2 Field1= "0000000000000001".

Then create relationship between Calc filed in File1 and this Field1= "0000000000000001 in File2.

Do I understand this correctly? I need to have a field called Priority, which gives Priority=1 or Priority=2 based on the combination of 16 fields.

Thanks again for the advise

[LiveOak]

Sort of. It doesn't need to be a lookup. You can use a related field from the second file. Create the key in your first file as follows:

Key (calculation, text, indexed) =

If(Field1 = "yes","1", "0") &

If(Field2 = "yes","1", "0") &

If(Field3 = "yes","1", "0") &

If(Field4 = "yes","1", "0") &

If(Field5 = "yes","1", "0") &

If(Field6 = "yes","1", "0") &

If(Field7 = "yes","1", "0") &

If(Field8 = "yes","1", "0") &

If(Field9 = "yes","1", "0") &

If(Field10 = "yes","1", "0") &

If(Field11 = "yes","1", "0") &

If(Field12 = "yes","1", "0") &

If(Field13 = "yes","1", "0") &

If(Field14 = "yes","1", "0") &

If(Field15 = "yes","1", "0") &

If(Field16 = "yes","1", "0")

The key must represent a combination of your 16 fields, not just one field. You will have one entry in the second file for each possible combination of your 16 fields. You will need to create 16,384 entries in the second file and assign a result to each (Have fun).

If you are REALLY only trying to test if more than one field is set, do as Thom suggested. Your mention of a lot of combinations suggested otherwise to me.

If you give us a description of what you are really trying to do, we may be of more help.

-bd

[bbaliner]

Thank you, Oak and Thom. You taught me a valuable lesson here.

B good

Bikeboy