Jump to content

Replacing contents in field with contents in another field


FMN
 Share

This topic is 2809 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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!

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 2809 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.