Hi, I am trying to get this to work and have succeeded but it stopped working or I probably changed something and now I cannot get it to work again.

all help is appreciated. Please see the attached file (it is a bit messy - under construction, sorry for that).

Take a look at my relationships. There is a table called Products. Basically once the Vendor is created in the Vendor table you can create Inventory line items for said Vendor. When you created the line items you can go to Product table and create products for said Inventory items.

In products table you will need to enter Item_Id and Vendor_Id and it will then pull records from Inventory table and auto-fill the rest. Now, it is possible that two different Vendors can have the same item. So in Product table when I change the second relation matching field I would like for the information to change to other vendor. That part is working ok so far and it is changing but I believe that it can be done better so advice on that is appreciated. For second matching field I would like to have a value list that will display only the vendors that carry that item. Right now I am getting it to display only one vendor even if I have added a self-join and checked show related records only. I think my relationship is a bit off.

Hope this is clear - if not please do let me know and I will try to explain better.

Thank you so much.

StandAlone.zip

Ok - I have been working on this and I came to a conclusion that what I have is a many to many relationship.

Basically table Inventory, for example, has two records:

X01 Vendor1

X01 Vendor2

Table Product will have only one record for the item X01.

Also table Product has a field Item and Vendor.

So what I want is when I enter X01 into the Item field a value list in the Vendor field will offer the choice of Vendor1 or Vendor2. Please note that Vendor field is a match field for relationship.

My value list works correctly and displays the choice of Vendors only if I enter one of the Vendors first manually - then it will pickup the relationship and will start to display both choices. If I do not enter it manually it will display value not defined.

Any suggestions on how to fix that?

The more I read this I think that it might belong into relation or value list threads so if it is so please move this accordingly.

Thank you.,

Is a different relationship match field, like a foreign key, a thing to use here?

Solved my problem. Finally figured it out. Just needed to add some relations...