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
