Trying to look up data

[Chalkster]

I posted this earlier but deleted it when I read it back and found I couldnt really undestand what I had asked!

I have essentially a very simple problem with two tables

One table is product list and consists of two columns; numeric ID and name.

Second table defines which products are allowed to mix with others to make a pair. This table consists of four columns ID and name and ID2 name 2.

I want to make both name fields auto enter based on the values I enter in ID and ID2 looking up the name values for the corresponding ID from the product list table but cannot seem to do it.

For example

product list

ID Name

01 Water

02 Plates

03 Badge

Table 2 mixing table...... what i am trying to acheive is that when I enter the product IDs in each field the correspaonding name is automatically entered

ID Name ID2 Name2

01 Water 02 Plates

02 Plates 03 Badge

03 Badge 02 Plates

If I relate the tables by ID then I can only get one product name field to autocomplete successfully.... is it possible to have both name and name 2 to auto complete based on their respective ID fields and the data in the product list table?

[Brian C]

Let me see If I understand what you are asking by attempting to answer:

Create 2 relationships:

"ID01" ID --> Product List::ID

"ID02" ID2 --> Product List::ID

Set field "Name" to lookup via the "ID01" relationship

Set field "Name2" to lookup via the "ID02" relationship

Hope this is the answer you were looking for.

[Chalkster]

That is what i tried but then FM seems to only enter the lookup values if ID01=ID02

I think it is because the relationships table shows relationships as

"ID01" ID = Product List::ID

AND

"ID02" ID2= Product List::ID

And therefore will only look up and auto enter Name1 and Name 2 if ID01=ID02 AND there is a corresponding entry in Product list::ID

If ID01 is not the same as ID02 then nothing is entered in either field automatically.

The ideal would be to find a way of saying

for name1 use relationship ID01=Product list::ID

for name2 use relationship ID02=Product list::ID

But I cannot see how to do it

[Chalkster]

AHH... I have realized what I am doing wrong..... I added a new table in the relationships graph and put the second relationship there... instead of both relationships on the same two tables ....problem solved