I have been a bit puzzled about how to do things efficiently and 
elegantly when one needs a value list/lookup table to ensure data 
integrity. To illustrate, let's say you have a field which indicates 
"OrderStatus" and can have the labels "Ordered", Shipped", "Received" (a 
bit arbitrary and simplified for the sake of illustration).

First, good normalization rules would require that the order status 
labels are stored in their own table with a primary key (e.g. a serial 
number), and that the Orders table, which must store an order status for 
each order, would use a foreign key consisting of the serial number of 
the relevant OrderStatus label.

This method has two advantages: 1) it saves space because each order 
record stores only a number instead of a whole word; 2) if one wants to 
modify one of the labels (let's say you want to rename "Received" to 
"Acknowledged", or you spelt the label wrong in the first place), you 
just change it once in the lookup table.

So far so good, but I am battling with the implementation in Filemaker 
(I started with FMPA 8.5 and have now upgraded to FMPA 11): the value 
lists don't make it so easy to display the data in an elegant way. 
Either you create a value list which shows both the key field and the 
label, and you get the order correct, or you choose to display only the 
label but then you are forced to use alphabetical order for the display 
of the labels. To use another example, if the labels are months, you 
must either display "1 January", "2 February", etc., or you must display 
"April", "August", "December", etc., which hardly communicates a sense 
of orderliness and control. To make things worse, the pop-up field 
control displays the list very differently from the drop-down control. 
(And while I am on the subject, why is it impossible to specify your own 
choice of field borders for a pop-up - you either have nothing or you 
must have FMP's shadow borders?)

I just can't figure out how to solve this in an elegant way. I cannot 
believe that the answer is to just make a value list of labels alone - 
that is too contrary to the rules of database normalization, is too 
wasteful of space and creates too much risk of compromising the 
integrity of the data.

Can anyone help me to understand the best way to handle this issue in 
Filemaker? Maybe I am missing something obvious!

(P.S. I was very happy to move from MS Access to FMP, but this is one 
thing that Access handled really easily.)

Thanks in advance
Bruce Button