I was having a discussion the other day with another in house developer over the context of lookups.

Here is the scenario:

tables: Customers, Contacts.

Relationships

Customers::Customer_ID(Base Table) = Contacts::Customer_ID(Table Occurrance)

If there is a lookup that we need to do in Contacts(say, a default value for a toll free phone number)

Is the correct way to do it:

A) In the lookup dialog set the "Starting with table:" option to the Table Occurrance of contacts, looking back to Customers.

OR

Create a new relationship Contacts::Customer_ID(Base Table) = Customers::Customer_ID(Table Occurrance). then make the "Starting with table:" option Contacts, looking over the new relationship to Customers?

I argued that option B was correct, because it was more logical, and although this is a very simple scenario, I think that option A makes it harder to maintain/troubleshoot issues.

Plus(being somewhat obsessive about this stuff) I think it looks sloppy.

Anybody have any other thoughts?

The first scenario is the better choice (between the two options you've given); when from Contacts, you want to look up a toll free number in Customers, you use the same primary relationship. Why create another DUPLICATE relationship when unnecessary?

But even further ... why are you wasting another field in Contacts which holds the toll free number when it is already in Customers? The ONLY reason you might want the number actually planted as static data in Contacts, is if the number might change and it is important to keep the original number (for audit and back-track purposes). But the Contacts should always only display the Company's CURRENT toll free number anyway, right?

So just take the Company's toll free number and plop it on the Contacts layout. In this way, it will display for all of that Company's Contacts AND, if the Company toll free number changes, it will automatically change for all of their Contacts (because it is the Company number anyway).

LaRetta :wink2:

I don't get this "Base Table" vs. "Table Occurrence" thing. Is this something to do with anchor/buoy? Going by your TO names alone, the "new" relationship already exists.

I agree with LaRetta regarding the lookup seeming unnecessary.

you are right, the example(toll free number) given was bad.. and in the solution I was looking at, we do it the way Laretta suggested.

It was just the first field I saw on my other screen when I was typing the question.

My question was more hypothetical than anything.

thanks for the feedback guys!