trying to focus (further) a self join 'company' field

[bhamm]

hello.. i have a second table occurrence of a 'contacts' table showing related contacts working at the same company. This is fine, but it's very rigid. For instance, contacts at "Widget Corp" and "Widget Corp, Inc." aren't showing that they're related.

short of forcing every company to be spelled EXACTLY the same, what can i do to make this relationship 'bend' a little?

the only 2 ways i can think of at the moment would be either:

1. adding a new 'tag' field.. and typing the common word ("widget") there. Then basing the relationship on that.. OR

2. somehow exploding each word in the Company string and doing an "OR" type of comparison..

something more elegant that i'm missing?

thanks in advance :

brian

Edited August 11, 2008 by Guest

[bcooney]

It sounds as if you are matching on the company name, rather than a company ID.

You should have a Companies table, and each company should be assigned a unique ID. This is done, typically, by defining an auto-enter serial number option to a field, __kP_CompanyID, where k stands for key and P stands for primary.

The same for your Contacts, __kP_ContactsID. In the Contact record, you also have a _kF_CompanyID, which assigns that Contact to that specific Company.

With those relationships, you can display a portal on the Company Form layout that shows all related Contacts.

[bhamm]

It sounds as if you are matching on the company name, rather than a company ID.

You should have a Companies table, and each company should be assigned a unique ID.

hi.. thanks for the quick reply :

while i don't have a companies table, even assigning an ID for each would allow no variance on the company names (ie. names wouldn't be 'fuzzy'). We have a number of large clients with different divisions, and i'd ultimately like to show those in a portal. For instance, i want "Baylor Hospital Heart Center" and "Baylor Hospital Cancer Center" to show some kind of relationship.

rather than have records for "heart center" and "cancer center" both related to a "baylor" parent record.. and then relating "baylor" to several client records, i think creating a 'tag' field might serve to allow the kind of fuzzy associations i'm looking for in this case.

i'm open to other suggestions of course.. :

thank you again for the speedy response

brian

Edited August 11, 2008 by Guest

[bcooney]

You're not being consistent with your requirements.

At first you said:

"For instance, contacts at "Widget Corp" and "Widget Corp, Inc." aren't showing that they're related. "

Well, are they? or are they two different companies and their contacts shouldn't be related?

EDIT: Even worse, are they the SAME company entered two different ways (see need Company table!).

Now you've introduced the concept of divisions. Sorry, but I'm for a Companies table and a Divisions table. Then, each contact record would store the foreign keys to each. I know you say you want fuzziness, but I don't think you really do. You want all contacts at Baylor Hospital. Easy with a find on the Baylor Hospital ID (or a portal to Contacts with this matching CompanyID). It won't get you all the contacts at Baylor Ice Cream!

Edited August 11, 2008 by Guest

[grumbachr]

I thought about your plan some and just dealing with the word "University" gave me chills. So I'm in agreement with bconney, especially if you are dealing with companies who's names might be similar.

I've had to deal with hospital names and I can't imagine trying to setup a system that could be precisely fuzzy enough for it to be right.

We deal with several Facilities (thats what we called companies) where the Word "Spine" is in the name and some are named exactly the name "Spine Institute" that are located in different states but have no affiliation. How would you "fuzzy" logic take those into account? I think in the long run trying to perfect your system would take much longer than setting up another table to start with and it would probably have few headaches in the end.

[bcooney]

And remember, Find can be a bit fuzzy.

You can always Find in Companies for "Baylor" and it'll find the companies that have that word in its name.

[bhamm]

You're not being consistent with your requirements.

At first you said:

"For instance, contacts at "Widget Corp" and "Widget Corp, Inc." aren't showing that they're related. "

Well, are they? or are they two different companies and their contacts shouldn't be related?

EDIT: Even worse, are they the SAME company entered two different ways (see need Company table!).

Now you've introduced the concept of divisions. Sorry, but I'm for a Companies table and a Divisions table. Then, each contact record would store the foreign keys to each. I know you say you want fuzziness, but I don't think you really do. You want all contacts at Baylor Hospital. Easy with a find on the Baylor Hospital ID (or a portal to Contacts with this matching CompanyID). It won't get you all the contacts at Baylor Ice Cream!

i see what you're getting at using company/divisions tables. What we have over here are records like "Merck & Co." and "Merck Research Labs". These are part of the BIG umbrella that is Merck, but there are different

entities under this umbrella. My 'division' example probably wasn't the best way to describe this earlier.

part of my task here is going to be having management decide how far out they'd like to focus when looking at associated records.. the scope, in other words. Ideally, i'd like something to adjust this scope.. show "just Merck & Co." records, or "any companies including the term Merck". Or i could be over-thinking things a bit.. management may be fine with a very narrow scope (the way it's working right now).

i was just curious if i could make this type of relationship somewhat flexible, where a user could widen (or narrow) the scope.

[bhamm]

And remember, Find can be a bit fuzzy.

You can always Find in Companies for "Baylor" and it'll find the companies that have that word in its name.

exactly! I want the fuzziness of a find request without going into Find mode. Maybe i'm getting into trouble in trying to force a portal to be a find request :

[comment]

It's possible, but the "fuzziness" must be carefully tuned. For example, you could break out individual words in the company name into a multi-line key. However, this would also match on "The" and "Ltd.". You could limit it to words of 4 or more characters, and/or add an exclusion list of common words such as "Company" and "Hospital", and so on.

[Vaughan]

When dealing with people's names you should consider Soundex.