AutoEnter calculation based on multiple fields

[BobWeaver]

I don't know if this has come up before, but...

If you create a calculated autoenter field based on more than one field, it will be filled as soon as you enter a value into one of fields on which the calculation is based, rather than waiting until all fields have been filled.

Suppose you are making an auto-enter field called FullName like so:

FullName = Autoenter calculation:

FirstName & MiddleInitial & LastName

When you create a new record, all fields are blank. As soon as you Enter "Bob" into the FirstName field, the FullName field will autoenter "Bob" and will stay that way even after you enter data into the MiddleInitial and LastName fields. The solution that I found is to use a case statement to force the calculation to return an empty field unless all the fields contain proper values like so:

FullName = Autoenter calculation:

Case(

IsEmpty(FirstName),"",

IsEmpty(MiddleInitial),"",

IsEmpty(LastName),"",

FirstName & MiddleInitial & LastName)

[This message has been edited by BobWeaver (edited January 25, 2001).]

[grant]

Why does it have to be auto enter. Just make the field a calculation field and it will build the name as the information is typed in. ( Unless you have a specific reason for the auto enter ?)

Grant

[LiveOak]

The thing to remember about auto enter calculations is that they operate as a record is created. If they are based upon fields in that record that are not yet filled out, the result will reflect this. When the record's fields are filled out a at later time, the auto enter calculation will have no effect as it only operates at record creation time. -bd

[macavity]

I assume you want to use auto-enter calculation in order to be able to change the entry.

Another way would be to create a calc field (doesn't have to display on layout) firstname & middleinitial & lastname, (with spaces inbetween) and auto-enter the result of that field.

[BobWeaver]

I hope nobody misunderstood. The solution that I described in my first message does exactly what I want. I just thought I would post it in case anyone else had encountered a similar autoenter calc problem.

Macavity: As you assumed, the idea was to put a default value into the field, but allow the user to change it if necessary. Also, once a value has been put into the field, it won't change later if one of the fields that it's based on is changed.

LiveOak: The interesting thing is that the autoenter calculation does not necessarily execute when the record is created. It executes when:

a. The field is empty, and

b. Any field that the calculation uses is modified.

So you can actually leave the record and come back to it later. When you finally complete the 3 fields, then the autoenter will execute.

I was just using the first-middle-last name thing as an example. My actual calculation was a bit more complex, but using the case() statement to check all fields to make sure they are not empty before allowing the calculation is the basic idea.