On part of my database I am tracking the licensing status of items submitted to our licensor. In one field I want to pull the status from a pull down menu.

Once that is selected (this is where I need help)I want another field to show the abbreviate term. I.E. First field will say Item Approved, second field will say I/A. I want it to automatically insert the abbreviated field when I select the corresponding status. I know how to do this with a script, but only if there is a button pushed. Is there a way to do it automatically as soon as the field info changes, the second field will change.

Also, I want to select a color for the status. I want it to be red if it needs to be resubmitted and green when approved. I want it to be automatically create here in then in the merge field that has the same status shown next to the date. Is this possible?

Use a calculation field of Case(

Status = "item approved"; "I/A"; Status = "something else"; "S/E"; ... ) for the abbreviation.

For the color, try using an auto-enter calculation for the Status field, with 'do not replace existing value' deselected, of

TextColor( Status; RGB( 255 * (Status = "resubmit"); 255 * (Status = "approved"); 0 ) )

Use a calculation field of Case(

Status = "item approved"; "I/A"; Status = "something else"; "S/E"; ... ) for the abbreviation.

When I try this with the field it is like the fields are idential. Field A (regular words...Item approved) copies that same text (item approved) instead of the abbreviated (I/A) to the Field B (abbreviated field). I inserted this in the calculated field when defining the field codes. Which field should have it inserted here (I tried it both ways) which field should replace "status".

Thanks.

Make sure the calculation field for the abbreviation is indeed a calculation and not a text field with an auto-enter calculation. Although you could use an auto-entered calculation (with 'do not replace' deselected), there should be no need to do so in this case.

Replace 'Status' with the name of your status field.

Make sure the calculation field for the abbreviation is indeed a calculation and not a text field with an auto-enter calculation. Although you could use an auto-entered calculation (with 'do not replace' deselected), there should be no need to do so in this case.

Replace 'Status' with the name of your status field.

I replaced the field already. But when I entered the script as a calculation field and go back and try it it says the field cannot be modified. Why?

(in case: this is my script -)

Case( Licensor Stage Status_Concept to Lic_Paper 1 = "Concept Submitted"; "C"; Licensor Stage Status_Concept to Lic_Paper 1 = "Concept Approved"; "C/A"; Licensor Stage Status_Concept to Lic_Paper 1 = "Revised Concept Submitted"; "RC/S"; Licensor Stage Status_Concept to Lic_Paper 1 = "Revised Concept Approved"; "RC/A"; Licensor Stage Status_Concept to Lic_Paper 1 = "Final Art Submitted"; "FA/S"; Licensor Stage Status_Concept to Lic_Paper 1 = "Final Art Approved"; "FA/A"; Licensor Stage Status_Concept to Lic_Paper 1 = "Approved with Changes"; "AwC"; Licensor Stage Status_Concept to Lic_Paper 1 = "Pre-Production Samples Submitted"; "Pre/S"; Licensor Stage Status_Concept to Lic_Paper 1 = "Pre-Production Samples Approved"; "Pre/A"; Licensor Stage Status_Concept to Lic_Paper 1= "Contractual Samples Sent"; "CS/S")

Here's a sample with the calculation you provided and the abbreviation/color changes. You can tweak the color calculation to include different criteria; but at least it provides a good example.

Note that there is no scripting involved, only calculations.

AbbreviationsAndColors.zip

That helped. Thanks. Exactly what I wanted. Thanks!

I was working on a similar problem. I need to change the background fill color in addition to the text color. Anyone have Any ideas?

Thanks.