Calculation to populate field only if empty.

[kbacon]

I am just now delving into Filemaker and I am having trouble with a field calculation. I want a field "Label Name" to be populated by a combination of 3 other fields: "Title", "First Name", and "Last Name". I only want this to happen if the field is currently empty. If someone has already manually filled in the "Label Name" field (it is not currently a calculation field) then I don't want it to change. I also would like the field to be editable in the future so people can make manual changes to the calculated value. Is this possible?

Here is the formula I tried but recieved a "circular definition" on:

IF (IsEmpty (Label Name), Title &" "& First Name &" "& Last Name, Label Name)

I am not sure what to put in the Result 2 spot. I tried "null" and few other things but I am shooting in the dark!

[BobWeaver]

What you really need is a text field, not a calculation field. You set the autoenter option for the field in field definitions. Set the field to autoenter a calculation, and use the following formula:

case(IsEmpty(Title) or IsEmpty(First Name) or IsEmpty(Last Name),"",

Title &" "& First Name &" "& Last Name)

This will enter a value into the Label Name field as soon as Title, First Name and Last Name all have values in them, and as long as Label Name is currently empty. Plus you can modify it later manually if you want.

[kbacon]

Thanks! I will try that. I was curious, however, how it knows not to overwrite existing data in the field. I suppose that is just the nature of a calculation in a text field? I didn't see anything in the calculation to that effect.

Also what if I want the Label Name field populated even if let's say the Title field is empty?

[BobWeaver]

There is actually a common misconception about when auto-enter fields are auto-entered. I think it's partly to do with the Filemaker documentation.

It's very simple. First the field must be empty. Second, one of the fields used in the auto-enter calculation must be modified.

Contrary to popular opinion, it has nothing to do with when the record is created.

Once there is something in the auto-enter field, it will never automatically modify after that (unless you clear it and modify one of the dependent fields again).

Regarding the optional title field, that creates a bit of a problem. The calculation I gave above, requires all fields to have a value before the calculation will produce a result. You can remove the IsEmpty(Title) from the case statement, however you will have to ensure that if Title will have a value, then it has to be entered before either First Name or Last Name. It's a matter of Filemaker trying to guess whether it should do the calculation or not if Title is empty.

[ April 13, 2001: Message edited by: BobWeaver ]