Lots of 2-Field Lookup Tables?

[PiedPiper]

Oh brother. I have a number field, trying to be efficient - its used in many large dbs, is used in aging calculations so I used a number to keep it space-efficient. It tracks the customer terms. 1 = COD, 2 = Credit card, etc. Cool ... I thought ...

But for all displays, I want it to show COD not 1. so i wrote calculation (pretty proud of myself for figuring out i might mention) with

Case(Terms = 1, "COD", Terms = 2, "Credit Card", Terms = 3, "Cash", Terms = 4, "Net 30", "Other")

But I think a calculation would slow network down more than relationship and I have lots of little things like this to track and if a calculation is created to display each one, it'll be a dawg. Should I create lots of little files just to display these descriptions or is there another way ... like repeating field or multiple line field or ? Just for these small things that only have a number (tiny key?) and one field (description for display/searching). Are these called lookup tables?

I will have have lots of little things like this to track. Its too bad I can't have a database within a database or can I? Because these special types of files are really unique ONE type of thing to our COMPANY. And I thought they could go in our company file with our address. Because these are kinda like defaults that would be changed at the company level and would apply everywhere and be used just about everywhere ... like our fiscal period. but they are multiple and our company record is one.

Do you understand what I'm wondering about putting them in a preference file - or should I keep these as a separate database (ONE?) - or should I have 15 little ones? Can it be in preference (one-record type file) even though there are multiple options (records?) within it? It's just two darned tiny fields.

I'm FM6/Server5/WinXP/Win2000 system with 15-20 people on it.

Geeez. I sure hope my confusion is making sense to someone ... thoughts please. i'd appreciate it a lot.

Pete

[Oldfogey]

If you make the calc a 'stored' calc, it shouldn't affect performance.

On the other hand, it is a major/minor drama to change the calc every time some berk want to change 'COD' to "C.O.D."

I ran into a similar but much smaller problem recently and ended up using small files as tables - e.g. the file 'customer status' has four records looked up from the main file. This is a pain to keep current BUT it is not my problem; the customer is responsible for maintaining the tables.

My first go was to combine two tables in one file. This involves a calculation to get the right link to the right value.

Finally, a really smart and klunky solution -

Value List = (A, B, C, D) Corresponding values = "Bus", ":Train", "Truck", etc.

One separate file containing a heap of value lists based on fields. If you have twenty values lists, you have twenty fields. FIELD1 has values '1', '2', '3, and '4'. FIELD2 has values 'A', 'B', 'C', and'D'. Each of these value fields has a value field - VFld1. If FIELD1 = '1', VFld1 = 'COD'. If FIELD2 = '2', VFld2 = "Train", and so on.

The one nice thing is that you can let your users edit the values in this file. It is easy to show them a layout with just the FIELD1 set or the FIELD2 set, etc.

In the main DB, you define your value lists as fields in the tables file.

You need to define twenty relationships - one for each set of values. The relationships are Feeeld1 = MainDB_FLD1<->TBLE_FILE_FIELD1, etc.

Your displayed fields are Feeeld1::VFld1, Feeeld1::VFld2, etc.

I'm obsessed with letting users/customers/THEM change these tables, hence my rather convoluted methods of letting them do it. Using self-joins in the tables file you can let them change the number of value pairs in each set. In short, you can give them the lot. The only time you have to get involved is when they need another field, such as eye colour. (Then you resign or quote them $4,000.)

[Fenton]

As Oldfogey says, a stored calc is not going to slow down the dbs. Personally, for something that involves business rules, such a money, I wouldn't mess around. I'd just use the terms themselves. They are not that long. Otherwise you're going to end up with many calcs where you can't easily see what's going on, because it's just a bunch of numbers.

There's a reason why languages like XML are supposed to be "humanly readable." With the speed and capacity of today's computers it is more important to keep things understandable than to save a few bytes of space.

On the other hand, I try to restrict the storage of redundant text data for display whenever possible. For example, not looking up the entire company name into join files, where just the ID would do, with any display handled by a related field on the layout.

I would not let the users have control of critical value lists. My (smallish) clients just call me and ask when they want to change something; can be annoying, but avoids problems. Fields that are used in calculations and "canned" scripts will produce bizarre results if the users change names and even meaning of terms; a "Status" field is always a problem.

A separate file for the values lists they can change is a good idea. I've not gone that far myself, yet. Someone had an good example file for this, but I can't find it on my computer. A basic one would not be hard to do, just a field for each value list, and "use value list from another file" in the other files.

[PiedPiper]

Hi guys, thanks for the input. I was thinking maybe COD would be better in the field than a 1 because then I wouldn't need a calcuation or relationship at all. And it could be handled through Value Lists but I wouldn't know where to put the value lists to be most easiest accessed in every other file I have and best function over a network. yes.

I would love to see a good value list example (as another file). I can't picture how it would work. ive been working on this stuff for months but Preferences, Value List and password databases all remain an elusive mystery to me on how they would work - and work together.

I can't picture what you mean Fenton. With the fields and IDs and globals or what? And then how would this value list file (with all my little lists) be attached and best available to everyone in all files? How would I stop a value list using values from field from grabbing OTHER types of lists and only grab the one needed?

I have the sneaky feeling that Im not the only one that has confusions on these types of files. In fact, I think that's why no one really talks about them or produces examples. Everyone does it different maybe? There should be an example pulling preference, company info, graphics, and value list (lists) together in one file (or a set) than can be attached to everything - because they all do. How to structure it is the problem - what goes where. I'm lost on it.

Pete

[Fenton]

There's more than one way to do the separate file. One big question is whether or not you need to "filter" any of the value lists via a relationship. If so, then a "line items" type file is what you'd need. You could present the value lists in portals for editing, on different layouts, chosen on a "master" portal (or buttons) layout, making it all cute and intuitive. But here's a quick example I just whipped up (not so cute) of a plain jane version and a "conditional" line items version.

Value Lists File.zip

[PiedPiper]

Thank you Fenton. I will study this. I notice you have category and item. So each RECORD might look something like this:

Category - Item (fields)

Terms - COD

Terms - Credit Card

Terms - Net 30

CustomerType - Retail

CustomerType - Wholesale

CustomerType - Special Terms

CustomerStatus - Active

CustomerStatus - Inactive

... sorry for the long example but I want to be sure I get this. I tried this once (i thought) but when I created a VL in Customers and said use values from fields in VL file (whew), it gave me all lists, not just terms. How do I split so that in all my files, it won't put the lists together? I think this probably has to do with the join between my main files and this VL db, right? And you don't have an ID. Are you using Category for the ID instead?

Other than that i'll have to study it harder before I have questions on the rest of it. Thanks Fenton. Oh. And I'll need two types of value list files - one for conditional and one for not? I sure appreciate you taking the time to do this demo for me. I read once that 'a separate value list file must be handled differently if it was conditional' (based upon field contents?). I'd like to just have one Value List file.

Pete

[PiedPiper]

Hi Old Fogey, I'm studying your suggestions also. I see the relationships part. So I will need to establish a relationship between each MAIN database and this Value List database - and one new relationship for EACH category? Well, I have to think. This is kinda what I was hoping to avoid. I'm still studyin' your ideas though and maybe it'll come clear for me. And with what Fenton gave me, maybe it'll all click soon.

Pete

[Fenton]

I just realized a serious limitation of the "use values from a field" option. Which is that the values are sorted alphabetically. I don't believe there is any way to stop this, as it is an index.

If the value list must be sorted another way, there is a 3rd method, which is to just use a regular "custom values" value list in the 1 Value Lists file, with the option to Edit.

A little trick is that you only 1 global field to show any number of these custom lists on the layout(s). It's basically a "dummy" (you don't need a field at all if you don't put them on any layout). You attach the different value lists to multiple occurrences of the same field. The value in the field is really immaterial (except it might confuse people to see an odd value until they click). I guess it might be better to just create more fields. This file is only for adminstrators though.

This "custom value lists, using global fields to show on the layout" is compatible with the structure of the "conditional" line items value list file (which has multiple records).

But the particular value lists using this custom method cannot be conditional (no custom value list can be).

Also, the Value Lists file should be password protected. The PW is "fenton."

Value Lists Fixed.zip

[Oldfogey]

Whoa! I just re-read some of this thread.

Pete,

Just how many potential lists do you have? You have a note up top that suggests just two.

[PiedPiper]

HI Paul

How many lists? Umm, err, I don't know yet but all those little things that I need to track, such as StaffType (S=Salesperson, A=Administrative, M=Management); God, I was thinking a code is quickest and keeps field contents smaller and then look up the real names.

It can even be messages that someone can select for the field but it will display the entire message on a Proposal. Lots of things. Maybe my thinking is all screwey. I just don't want to store a lot in a field if I can store a little but I need to display the entire thing sometimes. Many categories with 2-10 or so optional selections. Maybe 30 total categories. Stuf I'd need to use in almost every file. May be even error and messages - scrpt or calc produces 1 - means display error message 1. I just want it all in one location or all in the same type of setup so I can just attach once to each db and that stuff is accessible to each file without having to do everything again in every file. Am I making sense? Hope so, if not I'l keep trying.

Hey Fenton. I'm still working on this. Sorting yeah. Maybe I add a field called sort order. Treat the value list file like a portal which can sort any which way. Display portal with just the phrase in it when needed. 1 to 1 from main to vl - if TERMS =1, display COD. But what if two 'value lists' needed to appear on the same form? Id be sol, huh.

Pete

[Fenton]

A little philosophical observation. There is a trend among languages toward the more "verbose." XML is explicitly designed to be verbose and "humanly readable." The reason is that the biggest problem in computing today is a lack of understanding between different information systems, and a realization that it is important, more so than drastically abbreviating code to be brutally efficient. Computers today are fast and disks are large.

Every day we are faced with choices about this, just how descriptive does a field need to be? Each case has to be evaluated as to what is understandable to the user, and what the uses are internally; these two sometimes conflict, especially if concatenated keys are involved. Sometimes the conflict is so great that one has to do something about it, create calculations, use a related file, etc..

But not unless it's needed. The examples you've given so far don't seem to need such drastic measures. Perhaps a compromise would suffice. For example:

Sales, Admin, Mngmnt

A Unstored calculation could produce the full names if needed on a layout. But you'd still be able to tell what was going on in calculations or keys with the above tags.

I see no reason to shorten something like "COD" to "1." This is actually adding complexity and confusion, which are contradictory to efficiency in the long run.

I agree with storing long, often used text in Unstored fields. Sometimes a separate little file is needed, to provide a Serial ID which can be used to stand in for a long phrase, like a standard business letter. In that case the data is text in its own file, but is shown/printed as a related field. If it has its own file it is easily found and edited there; and you need a decent interface for doing so.

And commonly used value lists could be usefully stored in their own file.

I think you always need to ask yourself, "If I go away then come back in 6 months, how long is it going to take me to figure this out?" If the answer is "a long time," then you need to either make the structure more logical and intuitive, or document better (and yes, this is for me too :-)

[bruceR]

"I will have have lots of little things like this to track. Its too bad I can't have a database within a database or can I? Because these special types of files are really unique ONE type of thing to our COMPANY. And I thought they could go in our company file with our address. Because these are kinda like defaults that would be changed at the company level and would apply everywhere and be used just about everywhere ... like our fiscal period. but they are multiple and our company record is one."

You can do this now. Just add a 3rd field, table name. There are a number of developers doing this with value lists.