Replacing contents in field with contents in another field

[FMN]

Hello,

 

In the address section of my database, when you select either Canada or USA it provides a list of relevant ‘Provinces’ or ‘States’ in the Province/State field. Currently the provinces/states are written in full but I would like that once a selection is made, the field automatically inputs the abbreviation - ex. someone selects ‘Saskatchewan’ and it auto-enters/replaces it to ‘(SK)’ on commitment.

 

I managed somewhat to achieve this using the substitute function but only for one entry, I was not able to enter multiple substitutions – ex. Substitute (Province ; “Saskatchewan” ; “(SK)”). Is there a way to enter multiple substitutions as a conditional value? Is this the best way to go about it?

 

FYI, my address section is linked to conditional value lists with the following fields:

 

Country (list of countries)

Prov/State (list of provinces and states)

Prov/StateAbb (list of provinces and state in abbreviated form)

 

Thank you in advance for your help!

[Matthew F]

Well you could define a second "State Abbreviation" field as a calculation that translates the values in the State/Province field to a shorter value. 

 

Alternatively, I'd consider defining two value lists in a related "States" table.  On this table enter all the examples of "StateName" and "StateAbbreviation" on separate records. This table would only be used for defining State attributes.  Link it to the original table using an "any" relationship [X]. On your original layout, format the "State" field as a DropDown List, and link it to a value list based on "StateAbbreviation", but is set to also display values from the second field "StateName". Also select the option to show values only from the second field. The end result is that the users would pick from a list of complete state names, but the field will contain the abbreviated form. 

[comment]

IMHO, the best (and simplest) way to achieve this is to define your value list to:

 

Use values from first field: StateAbbreviation

[x] Also display values from second field: State

 

[x] Show values only from second field

 

This will enable you to select from a list of full names, but the actual value entered upon the selection will be the abbreviation - so nothing needs to be replaced.

 

 

Note:

FYI, my address section is linked to conditional value lists with the following fields:

 

Country (list of countries)

Prov/State (list of provinces and states)

Prov/StateAbb (list of provinces and state in abbreviated form)

 

Hopefully, you do not have any fields with a "/" in their name!

[FMN]

Thank you, will give a try. Btw, I do not have any "/" in my field names! (They are actually just 'Prov' and 'ProvAbb' - phew).

[FMN]

Works - thanks so much.