Multiple Value Lists to Determine Related Records

[korbin]

I am trying to build a database which stores information about three levels of items used in our company...

Raw Components

- These can be used in multiple Assembled Parts

- This layout needs to include a portal to display a list of Assembled Parts which use this Raw Component

Assembled Parts

- These can be used in multiple Packages

- These can include multiple Raw Components

- This layout needs to include a portal to allow data entry of the Raw Components used in this Assembled Part

- This layout needs to include a portal to display a list of Packages which use this Assembled Part

Packages

- These can include multiple Assembled Parts

- This layout needs to include a portal to allow data entry of the Assembled Parts used in this Package

What I am trying to make happen is to be able to enter data for an Assembled Part, including a list of all the Raw Components that the Assembled Part contains.

I'd like a portal on the Assembled Parts layout which is connected to a LineItems table which stores the list of Raw Components used in the associated Assembled Part. The user should be able to do the following, in this order...

1) Select the Brand of the Raw Component from a pop-up list.

2) Select the Name/Description of the Raw Component from a pop-up list which only lists related values (of the selected Brand).

3) Type the quantity of the Raw Component needed for the Assembled Part.

I'd like the same type of setup for the Packages layout as well, allowing the user to enter data for a Package, including a list of all the Assembled Parts that the Package contains.

Although I've been able to get the value-list to work, returning only the related field values from Brand and ItemName, I cannot get the cost information to relate correctly. I think it is because my match field is set to ItemName, when it should actually be set to the RawComponentLineID. However, if I switch to that as a match field, I am not able to get the value list to work correctly...

Sorry about the length of this, and any help is greatly appreciated!

Thanks in advance,

Korbin

layout.pdf

WOD-b14.zip

[Sanjai]

It is a good idea to create a relationship based on LineID instead of ItemName. Why can't you have two relationships which you have mentioned. One to show the matching Items for a brand and other relationship to show the cost.

I don't have version 7 in my office. If anyone else can look into it before I go home, it is fine else I will try to look into the files and write you a solution.

[korbin]

That is actually what I would prefer to do, but I was not able to get the value list to work properly when I used LineID as the match field.

If I had LineID as the match field, after selecting a Brand using the first value list in the portal, it would not return any Items (specific to that brand) in the portal.

It must be something that I've set up incorrectly in the relationships used to generate the value list I've named "Component Limiter". That value list is set to only display items which match with the "Brand" value list selection.

Thanks in advance for your help,

Korbin