simple calculation problem

[kmrjohnson]

I'm new at this so would appreciate your help.

I have a field "gender" with either "male" or "female" in it, and want to generate a salutation including "Mr." or "Ms.". I tried to set up the salutation field in a calculation as follows:

If(Exact(GetField ( "gender" );"male");GetValue( "Mr.¶Ms." ; 1 );GetValue( "Mr.¶Ms." ; 2 ))

but this returns nothing visible into the salutation text field

What am I missing?

[efen]

Use Case instead of If so that when the gender field is blank it shows nothing.

Case (

gender = "male"; "Mr";

gender = "female"; "Ms";

""

)

[TheTominator]

If(Exact(GetField ( "gender" );"male");GetValue( "Mr.¶Ms." ; 1 );GetValue( "Mr.¶Ms." ; 2 ))

but this returns nothing visible into the salutation text field

What am I missing?

As efen illustrated, there are probably a lot simpler ways to accomplish the goal you specified. I don't see where your code specifically goes wrong, but the use of GetField() and Exact() complicates seeing where it has a problem.

There may be a reason why you chose such a complicated way to accomplish this. An analysis of your formula may help you to figure out what simplifications you can use or why your calculation is failing you.

GetField() is used when you don't want to or cannot hard code the name of the field directly into the calculation. This usually would occur when you don't know the name of the field and need to calculate it or when you want to process many fields in a loop that have a predictable pattern in their names (e.g. Field_01, Field_02...).

Using GetField() instead of typing in the field name directly has at least two pitfalls.

(1) You need to get the name parameter exactly right. FileMaker won't complain when you hit OK on the calculation dialog since it can't check the validity of the name until the calculation executes.

(2) You will need to change every GetField() that uses a specific field name any time you change the field name in the table.

You can rewrite your calculation as

If(Exact(gender;"male");GetValue( "Mr.¶Ms." ; 1 );GetValue( "Mr.¶Ms." ; 2 ))

to avoid using GetField().

The Exact() function is designed to match exactly the two strings being compared including upper/lower case. In this example, "male" and "female" are sufficiently distinct that you can use a more loose comparison. If someone happens to enter "Male" (via import records for example) then you would probably still want the calculation to generate "Mr.". Thus the common "=" will do.

If(gender="male");GetValue( "Mr.¶Ms." ; 1 );GetValue( "Mr.¶Ms." ; 2 ))

To reduce the maintenance and avoid typos it would be a good idea to figure out to eliminate the repetition of "Mr.¶Ms.". If you change the salutation format or convert to a different language, then changing the labels in one place is desirable.

Let(

[titleList = "Mr.¶Ms."];

If(gender="male");GetValue( titleList ; 1 );GetValue( titleList ; 2 ))

)

Since the list is two items and you only use one item in each result, you probably would want to code it as just

If(gender="male");"Mr.";"Ms.")

As efen pointed out this will give "Ms." when the gender field is blank. A Case() structure works around this providing a default value (empty string in this case) when no match is found.

Case(

gender="male"; "Mr.";

gender="female"; "Ms.";

""

)

Edited March 28, 2010 by Guest

[LaRetta]

Let's look at this a bit:

[color:gray]... you probably would want to code it as just

If(gender="male"[color:red]);"Mr.";"Ms.")

This is close to being right except it has an extra parenthesis (indicated in red).

[color:gray]As efen pointed out this will give "Ms." when the gender field is blank.

Calculations, when the ONLY field in the calculation is empty, [color:green]*do not evaluate so the calculation would automatically produce null. That is why the checkbox at the bottom of the calculation box is default checked.

[color:gray]A Case() structure works around this providing a default value (empty string in this case) when no match is found.

Case(

gender="male"; "Mr.";

gender="female"; "Ms.";

""

)

Case() and If() work the same on handling default false results (since vs. 7); both producing null. The only difference is If() only allows two possible results and Case() can allow more.

Case ( Gender = "Male" ; "Mr." ; "Mrs." ) or even

If ( Gender = "Male" ; "Mr." ; "Mrs." )

... is all that is required if the choices are only these two or the field is empty.

UPDATE: [color:green]* Max() (in some versions) evaluates a calculation even if the field is empty, if it includes text within it, such as Max ( 300 ; aField ) but that is a known, confirmed bug and exception.

Edited March 28, 2010 by Guest
Added update

[kmrjohnson]

Incredibly detailed help! Unfortunately when I enter

contact_title=If(gender="male";"Mr.";"Ms.")

it doesn't work.

To back up: I have a field entitled "gender" that contains one of two values from a value list, either "male" or "female". I have another field entitled "contact_title" that is a text field and is supposed to have a calculated value via the above formula. I have the correct table selected from which to find the "gender" field for the calculation. There are no missing entries for the gender field,all contain either male or female, I have double checked this. Nonetheless, none of the "contact_title" fields contain any text when tested.

[TheTominator]

I have another field entitled "contact_title" that is a text field and is supposed to have a calculated value via the above formula.

To clarify the field named "contact_title" should be of type "Calculation". In the calculation definition dialog, enter

If(gender="male";"Mr.";"Ms.")

The calculation should return a "Text" value.

There is another way of having calculated values in a field of type Text. The field would be configured to be auto-enter using a calculation. Since both gender and contact_title are in the same table and gender is stored, it is better to make the field a Calculation instead of a Text field that is auto-entered. This auto-enter value would only be filled in as you do data entry in the gender field.

If you created the contact_title auto-enter field

after doing data entry in the gender field on a set of records, the contact_title would not be auto-entered for the existing records.

It would only be filled in for new records or old records where you changed the value of gender.

Edited March 29, 2010 by Guest

[comment]

To clarify further:

You may want to keep salutation as a Text field with auto-entered calculation - in case you need to override the calculated result with "Dr." or "Lord" etc.

However, an auto-entered calculation will evaluate only when a referenced field is modified (if set to replace existing value), so you need to populate the existing records. This can be done by changing the field to a Calculation and back to Text, or by using Replace Field Contents with calculated result.

[kmrjohnson]

I changed the field type to "calculation" and now it works great. Thanks to all of you for your help.

KJ