Populate Field Based on Conditional Calculation

[steigrafx]

I have been populating a "Nickname" field with the first line of another field using the calculation:

GetValue ( Address ; 1 )

I then use the Nickname field to populate a drop-down value list in a related database. It works perfectly.

I now need to populate the Nickname field only on the condition that my field called "Active" is Yes. I tried:

Case (Active = "Yes" ; GetValue ( Address ; 1 )

but it doesn't work.

Your help is appreciated. Thank you.

Kevin

[LaRetta]

Hi Kevin,

I assume this is a regular text field and you are using auto-enter calculation? If so, your existing data will not change unless you force an update. There are several ways to update the existing data but you do not need it as auto-enter text field - it can be type calculation (with result of text) instead in which case it will always update.

The only time you need auto-enter (calculation) is if the data in the field might be changed by the Users. Your calculation itself looks fine.

Added: If you are using names in a relationship (as it sounds like you might) then I suggest you switch to using serial IDs. I just want to give you a 'heads up' to potential issue that I sense here. If you are not using names to relate two tables then please just ignore this added paragraph. :)

[steigrafx]

Thank you, LaRetta.

I changed the field type to Calculation (unstored) and now it works perfectly, except...my value list stopped working. I now get a message that the value list will not work because the field cannot be indexed. Am I missing something?

Thanks again,

Kevin

[LaRetta]

It does not need to be unstored. Those fields, Active and Address both exist in the same table, right? If so then go to Storage Options on the Nickname and uncheck 'do not store'

[steigrafx]

Thank you so much, LaRetta. I've followed your advice and all seems to work perfectly now. My Nickname field only populates if Active is set to Yes, and my value list has returned.

Have a wonderful day, and thanks again.

Kevin