Creating a relationship based off a subset of a field's value

[Rapt Productions]

First off, I apologize if this is answered elsewhere on this forum, but I wasn't able to track it down.

I've got two tables, one for "companies," one for "contacts." The contacts are individuals who may work with more than one company, so I've got a field for "companies" in the "contacts" table. I want a portal in the "companies" table to show all the contacts who work with the company whose record I'm viewing. So I need to create a relationship based on the "companies" field in "contacts" containing a company's name, but not necessarily being limited to it.

Does that make any sense? I feel like I explained it really poorly. Thanks in advance for any help!

[T-Square]

Welcome aboard!

Let's see if I can help. I'll start by confirming something: you said "individuals may work with more than one company" -- is that true? Because, if it is, you will have to have a join table between Contacts and Companies (call it "WorksFor") that holds ContactID and CompanyID (and might include "Role" or other elements). To view all of a person's companies in this situation, you'd also have to place a portal on the Contacts layout.

Your later description implies that you only have one person and one company, which would be simpler.

Second, have you got the relationship created between contacts and companies? If not, you need to go to the relationship graph and make one between the ID field in companies and the company ID field in contacts. These fields must be of the same type [e.g., Number fields], and must contain the same data to match.

Regardless, if you have a relationship between the contact and the company, you can put a portal on the company layout that is based on the related contacts table, and it will show you only those contacts that have the current company ID. The concept is the same with a join table, but the portal is based on the join table rather than the actual contacts table, and you need to have relationships joining all three tables.

HTH,

David

[Rapt Productions]

Thanks David. I think I've already jumped the gun in attempting to set up the relationship as I did - I need to take a step back and plan out my database better. Thanks so much for taking the time to suggest a solution, though, I really appreciate it!

[Rapt Productions]

Hi David, I wonder if you could explain to me a bit more about join tables? I made a little bit of progress and got hung up as detailed in this post but it sounds like a join table might be the solution... what do you think? Thanks again!

Edited December 4, 2008 by Guest

[aldipalo]

Take a look at these two files.

Company Contacts shows you how to set up a simple relationship for a one to many (One company/Many Contacts) DB.

Contacts_Multiple_Companies shows you a many to many relationship (Many companies to many contacts).

HTH

Company_Contacts.zip

Contacts_Multiple_Companies.zip

[Rapt Productions]

Great! The Contacts_Multiple_Companies is exactly what I'm trying to do. But - I think I'm still missing something. I've tried to set it up in my Database. How are records created with Contacts_Companies? I made sure that "Allow creation of records" was checked, but - well, yeah, I think I'm missing something. It seems to me like something that would require lookups, but I couldn't find any...

Thank you so much for the help!

Edited December 5, 2008 by Guest

[aldipalo]

It's automatic. When you create a contact you enter the CompanyID in the Company Portal on the contact layout. The contact is now related to the company and vice-versa. Perhaps you should post a clone of your file so we can see what you are doing?

Edited December 5, 2008 by Guest

[Rapt Productions]

OK, here it is - I apologize for the mess, this is only my third database. So please feel free to point out any other mistakes I'm making, I appreciate any and all suggestions! Thanks again for your time.

Oh yeah - login is "sony"

RaptClone.fp7.zip

Edited December 5, 2008 by Guest

[aldipalo]

Serial numbers should be in number format (Preferred not always done). Whenever possible AVOID using text data in your relationships, especially Contact and Company names. Names can change and then ALL your relationships are lost.

I.E. ABC, Inc get acquired by XYZ Corp. You have 100 contacts. You would need to change the company name in all 100 records. By relating with a serial number if you change the company name the serial number does not change and all your relationships are intact.

I changed the keys in your relationships, added a value list, I also added a Phone number Custom Function so your data entry will be consistent.

Your file is now working. Please study the relationships and value lists to get a better feel for what was done.

hth

RaptClone_Mod_12-5.zip

[Rapt Productions]

Thank you so much! This will probably sound ridiculous, but it never crossed my mind that a match field could also be a lookup. That's what's going on, right, that the match field in phone, email and address is looking up the ContactID? How do the company and contact ID value lists come into play? Thank you again for your help and advice!

[aldipalo]

Thank you so much! This will probably sound ridiculous, but it never crossed my mind that a match field could also be a lookup. That's what's going on, right, that the match field in phone, email and address is looking up the ContactID?

Lookups in FM are a specific function. You set a lookup through a field that is related to the table you are looking from. This is done in options from your File->Manage->Database->Fields->Options->'LookedUp Value'

What's happening here is more of a pass through. There are many more qualified than I to explain the technicalities, but, basically, in FM, you can pass information through relationships that are interconnected. So, the Parent can see data in the grandchild or Great Grandchild table and they can see Parent data as well.

How do the company and contact ID value lists come into play? Thank you again for your help and advice!

When you have corresponding fields pk_ContactID ->fk_ContactID and you need to set the relationship you can do so by getting the Value from the opposite table and placing it in the current table.

Again, I strongly suggest you subscribe to ISO Filemaker Magazine and perhaps Lynda.com for great video tutorials that explain these ideas in great detail while showing you how to implement the techniques. Without, first, the great people on this forum ( Comment, Mr. Vodka, Fenton, Soren, LaRetta, Genx, Ocean West, John Michael Osborne, DJ, Lee Smith, Lord I could keep going on) and the tutorials at ISO Filemaker Magazine as well as Demo's from Ray Cologon, Kevin Frank and David Kachel I'd still be working on Flat Files. I can not tell you how much superb help is available. However, you have to invest the time and do the tutorials, and read the manuals and listen to the advice and, if you're smart like me you know about 10% of what there is to know after 3 years of working 20+ hours a week minimum.

Btw, on this forum there is no such thing as a stupid question!

hth

[Rapt Productions]

OK, after a couple days of poking at the file I think I finally understand what's going on. I wish I really could invest the kind of time needed to properly learn filemaker; it actually happens that it's only part of my job, I work as a video editor and happen to be one of the two guys in the office who has experience with the software. So now I'm trying to convert my end-user experience with filemaker 6 (some four years ago) into developer know-how with filemaker 9! I did read David Kachel's White Paper for FMP Novices, but I only absorbed maybe 40% of it. In any event - I just wanted to say thanks again for your patience and help on this project! It would have taken me weeks to get this far on my own, and weeks I don't have...