Is it possible to make a relation into a table with a literal value. I can do it by putting that literal value into a global field and matching on that field, but direct from a literal would be tidier.

I want to put different product options into value lists, from the same table but selected by an entry_type (option) field value. I could do this with a table per option type, but then loading the data would be a nightmare.

David

Can you provide an example?

A small textual subset (the whole thing is quite large and includes prices):

Chair A can be natural finish or stained one of four colours. It can also have three types of foot pad. Chair B can have the same options. Chair C can only be in natural finish and only one foot type.

My current solution is to have a 'picker' table that gets new choices put into it, each selected by a combination of pop-ups and radio buttons. It had two global fields, one holding a literal 'finish', the other holding 'feet'. I have an 'options' table with three fields relevant to this; 'model', 'option' and 'optionType'. I can create a relation returning a list of 'finish' options by relating the so-far selected model with 'model', and the 'finish' literal field with 'optionType'. This is then used as a value list for controling the 'finish' field on the 'picker' using radio buttons. Similarly for the feet. Of course (?) I have a relation per option type.

It seems that I should be able to use a literal directly in the relation definition, but I can't see how.

There are many many options, so having a table for each would mean updating many tables every time there is a new model. Having a field for each option would mean one record per possible option as now, but much bigger and more difficult to manage.

David

If you have the time I would go for a "many table" solution. Which means: relating your model to:

- a table with the colours

- a table with the materials (wood/metal/plastic etc.)

- a table with sizes etc.

Each time you have a new model you can easily add the features to it (if you create the proper layout/portal/etc). When a feature changes (for instance you have developed a new colour) you only have to update one table.

I spent six month restructuring our database system like this and it really works.

Please see the attached example. I think this is basically what you're looking for.

Even though I've based the value list relation on the product name, as you want to do, I would strongly recommend you don't follow my example in this regard. I encourage you to use a unique ID serial number to accomplish the same thing or you might encounter problems down the road.

I hope that any of this helps.

ValueLists.zip

Thanks Kip and Kent_S

Although this is a largish system (prices, currencies, discounts, offers, invoices, orders, customers ...) and is my 3rd reasonably complex FM system, I'm still really a novice. I'm not good at thinking in the FM way yet. As a Unix SysAdmin and an assembler programmer before that, I think proceeduraly. Kent_S's example pointed out to me that a table used for this purpose doesn't have to contain meaningful data, just have the values you want in some record. That thought opens things up for me. Obvious to you ...

I'd still like to be able to relate on literals though -)

And get a value list from a computation ...

Thanks again

David