RightWords ( Product_CodeDesc ; WordCount ( Product_CodeDesc ) - 1 )
So simple, that worked perfectly.
So now I have a different problem, the new field was to be used in a value list but guess because its a calc field I get the message field cannot be indexed.
So I created a text field and added as auto enter calc your formula but the field ends up being empty.
Auto enter fields only gets values when data is entered?
Is Product_CodeDesc a field that is defined in another related table?
Its pulling data from a related TO based on another DB.
A little clarification.
Product_CodeDesc is a field in table A in DB #1.
The value list is based on a TO in DB #2 from DB #1.
Did I confuse you cause I think I am.
That does identify the problem. When you specify that a field be used as the source of values for a value list, the field must be indexed and stored. But your calculation field cannot be stored and indexed if it refers to a field from another table occurrence. Your auto-entered calculation field can be stored/indexed but it will not update each time you modify the Product_CodeDesc field as auto-enter calculation fields can only update when a field used in the calculation that is from the same record is updated.
Also, such auto-enter calculations do not automatically compute a new value when you edit the defined calculation nor do they assign values to the field for existing records when you first add the field. When you add such a field to an existing table, you often need to copy that expression to the clipboard and then use it with a Replace Field Contents operation to update all the existing records with the correct values.
You may have to rethink this to find a way to get a stored, indexed value into a field where your value list can use it. You may, for example, need to define this calculation field in the same table as where Product_CodeDesc is defined and then refer to that field in your Value List setup.
Actually the calc field "Product_Desc" and the field "Product_CodeDesc" are in the same DB/table #1.
Ok, think I'm hooped.
Product_CodeDesc is an unstored calc field built from several different fields of which one is an unstored calc and I am unable to store it.
If I try to change storage I get the message "The calculation Product_Desc cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage."
I have the data in the field but am unable to use it.
Not sure which direction to go from here, do I do the Replace Field Contents option with the auto enter calc or .....
There are a number of options, but it's difficult to make a solid recommendation from here as we don't know the structure of your tables and how you need to use this part of your system.
One way is to define a simple text field in the appropriate table with an auto enter calculation that pulls the current values each time a new record is added to this table. Clear the "do not replace existing value..." option so that any edits to local fields it uses will update the calculation correctly. Then on any fields defined in other tables, use an OnObjectSave script trigger on the field to run a script that updates this text field for you. This method may work very well by updating just one or just a few records each time the external field(s) is/are edited or it could be very impractical, requiring the script to update hundreds of records each time such a field is edited--it depends on how you've set this up.
Ok, so I created the plain auto enter text field.
Then ran the replace field contents.
Modified the value list to select from the new field and i believe I have lift off.
The true test is when I give it to the user, very finicky these people.
Still want to muck around a bit more before unleashing.
Need to verify that this field gets updated when the user creates a new record in DB #1
In the mean time Thank you very much raybaudi , PhilModJunk for your help.