I have a requirement for a drop down field to have it display on the form when selecting one value and have another value returned to the database. For example, within the form the drop down displays the description field and returns into the database when a record is saved the sym field value. Description & sym being two fields in my custom table. So the description holds text values, Low, Low-Med, Med, Med-High, High and the sym field holds 1,2,3,4,5. Any ideas?
Given that it is almost always the practice to define a field that is populated from a reference table, as an SREL (foreign key reference) to the reference table, the out-of-box behaviour of a dropdown is typically to save the internal identifier of the reference table record that is selected.
The advantages of that behaviour include the following:
Why do you want to put the symbol into the database? Is it to make reporting easier? You can if you wish add an 'attribute alias' to make the symbol visible through the ODBC interface, instead of the internal id. Or just do the join, which is highly unlikely to have any noticeable performance impact. It is nearly always better to stick with the out-of-box behaviour, which is consistent throughout the data model of the 'mdb'.
The requirement is to retain the sym value for reporting as you've eluded to. On the form they just want to make it clear to users through descriptive text what the values mean as apparently people are getting confused with which value is high and which is low.
As Jerome says, you can specify in Web Screen Painter / Schema Designer that the Description field is displayed instead of the symbol for your custom table. Then the description appears in place of the symbol, both in the dropdown list and when the ticket is saved. On the Table Info tab in Schema Designer, select 'Description' for 'Default Display Field (common name)'. Save, publish, stop service, run pdm_publish, etc.
Then create an Attribute Alias in 'cr' for the field '<your cr field name>.sym', and in CABI / BOXI import the resulting alias - which behaves just as if you've denormalised the 'sym' field into the cr object - into your customised CA Universe.
Have fun :-)
Why to not just just enter your text to be view by your users in the description field of this attributes and then use it in your dropdown vs. the sym.
You then use the principle as describe by James and archive your requirements.
My 2 cents