Multiple value lists for the same field

[Macfreq]

I need to have two different value lists for a field. The value lists show items from different related tables.

The field is a part number and the value lists are (1) parts from the entire inventory and (2) a subset of the parts based on a job, if it defined.

I had the idea of using a Tab field to switch between hidden tabs based on whether a job is defined or not. That would make the part field with the appropriate value list defined "appear". Is there a better way? Can the position/arrangement of a field be scripted to make a field come forward or backward?

Thanks for any tips.

Edited August 27, 2007 by Guest

[Søren Dyhr]

Can the position/arrangement of a field be scripted to make a field come forward or backward?

Yes it can, it's an old trick to posistion an invisible button ontop of the field with a popup attached, and then have a stack of the same field with each it's own valuelist attached. Each version of the field is then put into a tab order, so the scripting based on the condition tabs along until it lands in the correct popup.

However is there hardly any use for such shenanigans any more, since you by the introduction of fm5 have been able to define dynamic valuelist based on relational content, be it selfjoined or split out in a separate table. This is more likely what you are after.

http://www.filemakerpros.com/CONDBASIC.zip

--sd

[Macfreq]

Can the valuelist be based on a relationship that can point to one of two different tables?

[Søren Dyhr]

Isn't it a normalization error to keep in two tables?? Well it can be done ...but you need to put up a good argument here!

--sd

[Macfreq]

I don't think I'm violating normality, but I could be wrong. I have two tables because one table contains the inventory with standard information (pricing), and the other table is related to the first but has customized information (subset of inventory with special pricing) for customer jobs. I think I need the second table so I can keep track of job specific inventory that will be used over and over for orders related to that job.

I want the value list to show either the entire inventory, for a normal order, or a job specific inventory when a jobnumber is defined. Hopefully that makes sense.

Thanks for the help.

[Søren Dyhr]

But as such is there only a single attributal differences, as which is which??

Admitted it depends largely on the definition of a job, but still does it sound like a subset, when a condition is pending - you do actually uses the term subset.

BTW normality/insanety isn't what we debate here, but instead:

http://en.wikipedia.org/wiki/Database_normalization

--sd

[comment]

I believe this a legitimate question that should be answered, not attacked.

One possible solution could be to have a calculation field switching between ValueListItems ( " a value list of all products" ) and ValueListItems ( "a value list of products in the subset table" ).

Then define a relationship to a new TO of Inventory, based on the calculation list, and use a third value list (related values only) to make the selection.

[Macfreq]

Thanks for the idea about the calculated fields, I'll try that out and see if I can get it to work.

On the other subset or not subset issue. I just want to make sure I'm not going to get into trouble later on. The second table I have is a job inventory table that basically has the following fields:

PartNumber (primary key of Inventory Table)

JobNumber

Price

This table is used to define special pricing for a job, which in turn can be used on multiple sales orders for that job.

Should I be storing the job specific pricing in the main Inventory table with the default pricing? I just can't see how that's going to work for multiple jobs each with different pricing.

Just making sure I'm sane.

Thanks

[comment]

I don't think there's a single 'correct' solution for this. Personally, I would lean towards having all prices in one table. So I would probably have a table of Products where each product is a unique record, and a table of Prices, where each product can have multiple related records - one 'list price' record and, optionally, special prices for specified jobs.

But having the default 'list price' in the Products table itself can have its advantages, too. A lot depends on your actual workflow and other circumstances.

Edited August 29, 2007 by Guest

[Søren Dyhr]

Personally, I would lean towards having all prices in one table

That makes two of us, and i apologize if my reply sounded to snotty!

--sd

[Fitch]

You could stack two copies of the field on top of each other. Make them non-enterable, give them object names and define the one on top one as a button, with a script that goes to the top or bottom field/object based on your job criteria.

[comment]

This seems to be a duplicate thread:

http://www.fmforums.com/forum/showtopic.php?tid/189647/

[LaRetta]

Threads have been merged ...