Lookup value list with Abbreviations and descriptive text

[Neville310]

Sorry to bother with my neophyte questions...

Several years ago, I developed a fairly extensive database on another platform. It handled value lists mainly through a lookup table. One could add multiple fields through queries and set the data source field independent from the list displayed value. This technique became handy for saving abbreviations and displaying descriptive text in forms and reports. The query would form the basis for the drop-list; and the drop-down control would have its source value set to the appropriate column.

The simplest explanation relates to a State lookup table.

StateID => CA

StateName => California

The drop down control box would display California, yet when the user selected a value, the drop down control would save CA.

This technique is fairly straight forward in Filemaker (even for a newcomer). My question revolves around multiple layouts and utilizing a more descriptive text in one layout, which has more details about the status; and the abbreviation in another layout. My objective relates to tracking phases in a project. It would be like multiple check boxes tracking progress; yet the other layout would display the full descriptive status for all phases. In other words, the status could have ten steps summarized into in progress or done. Or the following stages below.

RS => Ready to Start

NA => Non Applicable

IP => In Progress

DN => Done

So IN PROGRESS is anything not DONE while the other layouts has a detail status; like ordered, received, verified, packaged, shipped, and receipt confirmed. Presently, a spreadsheet serves this purpose helping me get from start to finish. I hope to develop a similar layout in Filemaker.

One approach would be to create a calculation field, which I have less familiarity with calculations; Should it be a calculation in the value list, table field, or form control?

The other approach would involve using multiple fields; one for the descriptive status; another one for the status abbreviation. Then tie it together with a table occurrence. Again, both techniques require me to shift my mindset about value list for Filemaker.

Please let me know if anybody has crossed any tutorials or databases utilizing these two techniques. Any help pointing me in the right direction would be appreciated.

Edited February 18, 2009 by Guest

[bcooney]

The first thing I'd do is create a table that supports this value list. Each stage would be a record, and would have a unique ID. You will always store the ID, so the words can change without breaking anything.

I see In Progress as a calculation that looks at the stage field. Basically, if Stage<>"5" (5 is the unique ID for Done from our value list table), then flag_InProgress =1.

See how I didn't store the word, "In Progress". I use flag fields, then a display calc. This further separates my logic from any text values.