Is there any way that I can set choices for a column that show “East, West, North, South” but the real values saved in database is “E,W,N,S” or “1,2,3,4”?
Since the list for choices is not long, I would prefer not to make another table and use reference, if possible.
I don’t think this is possible using the Choice or Choice List column types, since the value(s) selected are literally set to the chioces that are defined for the column.
I think you will have to create a separate “lookup table” for the choices, then you can define the numbers (or use the row id), abbreviations, and full names. Then in your table where you’re using the Choice List right now, change the column type to a Reference, pointing to this new lookup table (and choosing whatever field you want to show, such as the one that has the E/W/N/S abbreviations).
Although this particular use case is pretty straightforward, I pretty much always prefer to use Lookup Tables unless the choices are very static (and of course, I’m OK showing the exact string/value that is defined for each choice). In even slightly more complex cases, having the lookup tables is much more powerful, since you can define other columns of data, and display them whenever needed, depending on the use case. For example, even in your case, perhaps some other table needs the direction, and there you want to show “East” instead of “E”. With Choice List columns, you don’t have that flexibility.
I will, however, mention one hacky method I tried which kind of works:
= LEFT(yourColumn, 1), which will change the value selected to the first letter only.Now, if you select “North”, it will get changed to “N”. Of course, this column’s value is no longer technically valid (since “N” is not in the choices), but “N” does indeed get stored there, it’s what shows, and it’s what’s present if you export the data. This obviously only works in cases where your choices have unique first letters, which is the case here…
T