I currently have an address table that uses lookups to update City, State, ZipCode.

If a user enters a ZipCode, it will update City/State.

If a user enters City/State it will update ZipCode.

Now I am attempting to make sure that the ZipCodeSuffix also follows logical behavior by using the Auto-Enter Calculated Value.

I would like the ZipCodeSuffix field to set itself to an empty value if Address/City/State/ZipCode change.

If a user is actually inputting the ZipCodeSuffix I do not want its contents to change.

This will insure that any time an address is changed, a user has the opportunity to put in the correct ZipCodeSuffix and the solution will not allow an outdated suffix to remain.

So far I can only seem to make it always reset the contents or to never reset the contents.

Does anyone know how to make this work?

Hi,

You may use a calculation formula involving Get( ActiveFieldName ) so that you're conditionnally looking up data if the input is *not* made from the given field.

Quickly put, something along the lines of

If ( Get ( ActiveFieldName ) = "ZipCodeSuffix" ; ZipCodeSuffix ; Lookup ( Related::ZipCodeSuffix ; "" ) )

Now, if as you stated, you'd like it to be set to empty anytime data is changed into City/State or ZipCode, you may delete the Lookup part and once again use Get ( ActiveFieldName ) with the other field names and set the second part of the If/Case expression so that a "" is returned.

HTH

Edited May 2, 200817 yr by Guest

I have been burning the midnight oil lately working and my tired brain doesn't always want to be creative :

Thanks for the help Ugo!