Defining a Field based on other Fields?

[Nigel Mellish]

Hello everyone,

I'm just starting with Filemaker, and am probably trying something a little too complex for my level, but once I get this down, I'll have my first Filemaker Application down.

I'm using Filemaker Pro 7. I have a Field, "FieldC" that I want to calculate based on the text input of InputofFieldA and InputofFieldB. The user inputs InputofFieldA and InputofFieldB using a pulldown menu of a non-modifiable value list (same list for both Fields) with 6 values.

So I've defined FieldC as a calculation, and in the calculation declaration space, I built the following:

If (InputofFieldA = "Very Low" and InputofFieldB = "Very Low" ; "Medium")

Else if (InputofFieldA = "Low" and InputofFieldB = "Very Low" ; "High")

Else If (InputofFieldA = "Medium" and InputofFieldB = "Very Low" ; "Very High")

Else If (InputofFieldA = "High" and InputofFieldB = "Very Low" ; "Very High")

Else If (InputofFieldA = "Very High" and InputofFieldB = "Very Low" ; "Very High")

Else If (InputofFieldA = "Very Low" and InputofFieldB = "Low" ; "Medium")

Else If (InputofFieldA = "Low" and InputofFieldB = "Low" ; "Medium")

Else If (InputofFieldA = "Medium" and InputofFieldB = "Low" ; "High")

Else If (InputofFieldA = "High" and InputofFieldB = "Low" ; "Very High")

Else If (InputofFieldA = "Very High" and InputofFieldB = "Low" ; "Very High")

Else If (InputofFieldA = "Very Low" and InputofFieldB = "Medium" ; "Very Low")

Else If (InputofFieldA = "Low" and InputofFieldB = "Medium" ; "Low")

Else If (InputofFieldA = "Medium" and InputofFieldB = "Medium" ; "Medium")

Else If (InputofFieldA = "High" and InputofFieldB = "Medium" ; "High")

Else If (InputofFieldA = "Very High" and InputofFieldB = "Medium" ; "Very High")

Else If (InputofFieldA = "Very Low" and InputofFieldB = "High" ; "Very Low")

Else If (InputofFieldA = " Low" and InputofFieldB = "High" ; "Very Low")

Else If (InputofFieldA = "Medium" and InputofFieldB = "High" ; "Low")

Else If (InputofFieldA = "High" and InputofFieldB = "High" ; "Medium")

Else If (InputofFieldA = "Very High" and InputofFieldB = "High" ; "High")

Else If (InputofFieldA = "Very Low" and InputofFieldB = "Very High" ; "Very Low")

Else If (InputofFieldA = "Low" and InputofFieldB = "Very High" ; "Very Low")

Else If (InputofFieldA = "Medium" and InputofFieldB = "Very High" ; "Very Low")

Else If (InputofFieldA = "High" and InputofFieldB = "Very High" ; "Low")

Else If (InputofFieldA = "Very High" and InputofFieldB = "Very High" ; "Medium")

Filemaker violently throws up all over me.

Ok, it just returns the following, it highlights the first "Else If" and says "This Function Can not be Found"

Anyone have any clue what I'm doing wrong?

Thanks

[mr_vodka]

You can try something like this to clean it up so that its more easier to read...

Let ( [ a= InputofFieldA; 

	b=InputofFieldB;

	vl="Very Low";

	l="Low";

	m="Medium";

	h="High";

	vh="Very High"

      ];

	Case ( 	a=vl and b=vl; m;

		a=l and b=vl; h;

		a=m and b=vl; vh;

		a=h and b=vl; vh;

		a=vh and b=vl; vh;



		a=vl and b=l; m;

		a=l and b=l; m;

		a=m and b=l; h;

		a=h and b=l; vh;

		a=vh and b=l; vh;

		

		a=vl and b=m; vl;

		a=l and b=m; l;

		a=m and b=m; m;

		a=h and b=m; h;

		a=vh and b=m; vh;



		a=vl and b=h; vl;

		a=l and b=h; vl;

		a=m and b=h; l;

		a=h and b=h; m;

		a=vh and b=h; h;	



		a=vl and b=vh; vl;

		a=l and b=vh; vl;

		a=m and b=vh; vl;

		a=h and b=vh; l;

		a=vh and b=vh; m

	     )

     )

[-Queue-]

I would use

If( not (IsEmpty(InputofFieldA) or IsEmpty(InputofFieldB));

Case(

InputofFieldA = "Very Low"; Case( PatternCount( InputofFieldB; "Low" ); "Medium"; "Very Low" );

InputofFieldA = "Low"; Case( InputofFieldB = "Very Low"; "High"; InputofFieldB = "Low"; "Medium"; InputofFieldB = "Medium"; "Low"; "Very Low" );

InputofFieldA = "Medium"; Case( InputofFieldB = "Very Low"; "Very High"; InputofFieldB = "Low"; "High"; InputofFieldB = "Medium"; "Medium"; InputofFieldB = "High"; "Low"; "Very Low" );

InputofFieldA = "High"; Case( PatternCount( InputofFieldB; "Low" ); "Very High"; InputofFieldB = "Medium"; "High"; InputofFieldB = "High"; "Medium"; "Low" )

InputofFieldA = "Very High"; Case( PatternCount( InputofFieldB; "Low" ); "Very High"; InputofFieldB = "Medium"; "Very High"; InputofFieldB = "High"; "High"; "Medium" )

)

)