Value List with related values from 2 tables

[TheLagosChap]

Hi,

I think I've seen a similar problem posted recently (subject "Intersections" by eric.p on 10-02-09) but no useful answer. So am trying again, rephrasing the problem, it might help.

I'd need a way to show a value list with records from one table that are linked to 2 different tables (it's an intersection indeed).

There's no way to do this in the "Value List" interface, hence me posting this in the "Relationships" section.

To be more practical, if the description above is too theoretical...

I have an "Item" table linked to a "Factory Items" table and also to a "Machine Items" table. These 2 last tables store which Item can be produced in a particular Factory and by a particular Machine respectively.

Now I want to have a Value List showing me which Items can be both produced by in Factory and by one Machine (i.e. Intersection of the Items records linked to Factory Items and linked to Machine Items).

Any interesting way to do this automatically (i.e. not via a script that will, each time, create records in another table with the resulting intersection)?

Thanks!

[comment]

Why not keep all items in the same table to begin with?

[TheLagosChap]

All Items are in one table (Item).

The 2 other tables have relationships to the Item table.

And sometimes, I need to see which Items can be produced by a Machine X that has been assigned to a Factory Y, i.e. the Intersection of both.

Edited October 7, 2009 by Guest

[comment]

I am afraid I don't understand the logic here. First you say an item can be produced by a machine OR by a factory. Then you say (or so it seems) that a factory can produce an item BECAUSE it has the right machine.

[TheLagosChap]

OK, I see I'm not very clear, sorry about that. It's partially because I'm trying to simplify the description of the problem (and reduce the number of tables & relationships to get there) I suppose...

A machine can produce any number of items.

A factory can also produce any number of items (but without any link to which item can be produced by machines).

When placing a machine into a factory, the intersection of the items that can be produced by the machine and the ones that can be produced in the factory is what can actually be produced.

Or explained otherwise, the list of possible items produced is constrained by the list of items that a factory can produce and the list of items that a machine can produce.

That's the (value) list I'm trying to display.

Hopefully this is more clear?

Thx for taking the time!

Edited October 7, 2009 by Guest

[comment]

OK, I think I understand now. I am not sure where you are when you need this value list, but let's say we are on a record of Machine X, assigned to Factory Y.

1. You can get a list of factory items (pulling through the Factories table from the FactoryItems table)

factItems = List ( FactoryItems ; ItemID )

2. You can get a list of machine items (pulling directly from the MachineItems table):

machItems = List ( MachineItems ; ItemID )

3. You can calculate the intersection as:

FilterValues ( factItems ; machItems )

4. Now you can define another relationship to Items as:

Machines::cIntersection = ItemsIntersect::ID

and use it for your value list.

[TheLagosChap]

This sounds a very promising idea!

I'll try it out and revert.

Thanks!

[TheLagosChap]

WORKS!

Thank you!

I thought I knew a few things about Filemaker but I'm happy to say we all learn everyday.