I have a database that has tables named Company for Location, Contact, and Location. Each table has its own serial ID field (CompID, ContactID, and LocID respectively). The company table contains the company name. That gets related to the location table to match an address with a company (via CompID). The Location table is related to the Contact table via LocID.

I have a contact table with names that I wish to associate with a company. Since a company may have more than one physical address, I want to be able to choose from the multiple addresses associated with a company. This is where I am having a problem.

I thought I could use a value list for the LocID field in my Contact table configured as follows:

Use values from first field - CompID (from Company for Location table), Include only related values starting from - Location (table).

Conceptually, I think this makes sense. I want to see the LocID values that go with a particular CompID in my current view, and choose the one that contains the address data I want. Yet when I click in the LocID field and expect a list of values associated with a particular CompID, I get nothing.

If I remove the Include only related values starting from option, then I see the LocIDs of contacts that are already associated with multiple CompIDs...not what I want.

If someone could tell me what I am doing wrong, or perhaps a better way to do this, I would appreciate it.

Thanks for reading!

Edited January 14, 200619 yr by Guest

when I click in the LocID field and expect a list of values associated with a particular CompID, I get nothing.

To the 20+ people who read my post, thanks for reading. I managed to solve the problem quoted above by changing how my tables were related to one another. However, I still have a problem with value lists. Perhaps this other problem is easier to solve.

When I click in the LocID field, I now see a value list of all of the LocIDs affiliated with a CompID. However, when I choose a LocID other than the first one displayed, the LocID field seems to ignore my input and it continues to display the first LocID in the list.

Now, here's where the problem gets interesting. If I type the second LocID value displayed in the LocID field's value list into the LocID field with the keyboard, then the related fields show the data with the LocID that I typed in (which happens to be the LocID that FMP is refusing to let me choose from the value list).

I suppose the question now is why would FMP refuse to acknowledge my choice in the value list, and what can be done to make it accept my choice and display the related data in the fields?

Thanks for reading (again, if you are revisiting).

Try it this way.

ContactLocationVL.fp7.zip

Comment,

Thanks! It works your way, but can you explain why it did not work my way? I would like to understand why it did not work, as a means of building up my FMP knowledge.

I don't know - I did not see your file, and I didn't quite understand your explanation of it.

I didn't quite understand your explanation of it.

You are an excellent mind reader then! : Would a PDF of the relationships graph help?

Why not an actual file? You can duplicate mine, and change it to what you think should work.