Join/relationship problem?

[aldipalo]

Ok, I don't get it. I have built a database with 19 tables and multiple relationships. All of them have been working fine. My portals find and sort the data correctly. When I enter an ID number from the parent to the child (Ithink that's how you refer to it )the fields are populated with the correct data. So, now I go and add two more tables. The database is for a recruiting firm. So, I add "Reference Check Names" and "Reference Checks."

Ref Chk names has the following:

REF_ID

CO_ID

Contact_ID

rName

rCompany

rTitle

rPhone

The Contacts database has over 15,000 entries. We specialize in Scientists in the Pharma industry so pretty good chance the reference already exists in the database. If they don't why miss the opportunity to pick up another name. Therefore, if the name does not exist I want to add it, first, to the contacts database, then have it populate the reference names database.

My problem is I do a lookup on Contact name in the contact_ID in the contacts database and expect that the Name/phone/title should be populated in the portal. Just like all my other portals. I look up the ID, hilite it, tab or click out and that field is populated, but nothing else. I have set the relationship several different ways and still nothing.

Any Ideas?

If someone can tell me how to copy the relationship page I'll post it, if that will help.

Thanks for any help you can give.

Al

[RalphL]

Print it to a .pdf and attach the .pdf file.

I am having trouble following what you say.

"I do a lookup on Contact name in the contact_ID in the contacts database and expect that the Name/phone/title should be populated in the portal."

By Lookup are you doing a FileMaker LookUp?

[aldipalo]

Let's take the two main files. Companies and contacts. They are joined by CO_ID. When I am entering a new contact I first do a lookup on CO_ID (A defined value that looks at CO_ID in the companyDB database) if I find that the company exists (I have 22,000 companies so most often it does) then I click on the listing and the Company Name field and the company address, phone, city, state and zip fields are also filled in with the data from the company database.

If I go to the CompanyDB I have a portal that shows all contacts at that company and the fields in the portal are filled in by the CO_ID relationship between companyDB and Contacts. If I want to go to a specific record I have a button script that takes me to the related record.

I have uploaded a picture of the relationship graph for you to look at. I hope I have made myself clearer. Thanks for your help.

Al

Relationships.pdf

[RalphL]

I do not understand how you find if a company is in the Company table. With 22,000 companies, I don't think a user could remember that many Company ID's. It is too many for any pop up.

Why do you need the all the company data in the contact table? The relationship should take care of this.

You should take a look at the Anchor Buoy method for relational graphs at http://www.kevinfrank.com/demo-files-user-group.html

[aldipalo]

Basically, it does. I need to verify that the company is in the database before entering the contact record. So I lookup th CO_ID which is a calculated field containing the first 5 letters of the company name and the zipcode. This way I know it is unique. Many major corporations are multi-divisional and have loccations all over the world. So contacts who work for Pfizer in NYC are doing different things than those that work in Groton, CT. Having this field makes the entry completely unique. So all teh user needs to know is the first few letters of the Company name. They type it into the CO_ID field,. The define value set is set to come back with CO_ID and Company Name. By clicking on the proper CO_ID it inserts it into the field and Voila' the rest of the data fields are populated. I've been doing this in Approach for 10 years and before that with DBase for 10 years. So, when designing FM I have kept the same format for fear of screwing up the import and relationships from Approach.

[RalphL]

My question is why do you need the Company Name, Address, etc in the contact table. All you should need is CO_ID.

[aldipalo]

Address, CSZ is in a tab. Company name and phone is on main screen so I can look at screen and know what company the person works for. I want to see the data. It is helpful when you are talking to the contact to know where he is working. CO_ID just shows first 5 letters of name and zipcode.

As, not only the developer (I use that term VERY losely) and the end user I want to see as much data as possible on my contact. It is extremely helpful when trying to interest them in an "Better Opportunity." That's the problem with most "In the Box" Contact Managers and Applicant Tracking systems. They are not developed by people who do the job everyday. There are some very good programs out there, but none that have been designed for the way I work. It would have been easier and less time consuming to buy FM Recruiter software, but after looking at it, it was just missing the components that were important to me and I had to jump through hoops to get the data I want to have in front of me.

Anyway, any ideas as to why the data is not displaying?

[RalphL]

You can see all this data using related fields, you do not need to duplicate the fields to show the data from related records.

Now to your problem. Are you in contact and you want a portal to show contacts? If so you need another relationship from contact to contact 3.

[aldipalo]

First, are you saying that I should be pulling company data from companyDB and not have corresponding fields in Contacts and thes same for contacts VS REf Check Names?

Next, I'm in contact and I want to show fields, in a tab and portal environment, from Ref Check Names, where the names already exist in contacts. So I get the contact name to populate via the defined value, but the rest of the fields, Company/Title/Phone do not fill in at the same time.

My first thought was that I am trying to have two different contacts on the same screen. If this is so, then how do I go about creating this table and getting data. It seems a waste of time and resources to re-enter the name.phone etc. into the ref check names database.

[RalphL]

Yes there is no reason to have this data stored in more than one table. Once you have the key you can display the related data on your layout.

I will get back later on the rest.

[RalphL]

If I understand this correctly, you need to add another Company TO to graph and make a relationship from Reference Checks Names to it using CO_ID as the key.

Is Ref_ID the same as Contact_ID for the reference person?

It looks to me that Reference Checks Names is the join table in a many to many self relationship of Contact Names.

[aldipalo]

Sorry it took a while to reply. I'm travelling until Dec. 1.

Yes, the ref_ID and Contact_ID are the same. I couldn't figure any other way to pull duplicate data from the contacts table.

And yes, the same goes for Reference names. My first thought was just have ref_checks table and pull data from contacts, but, I couldn't get that to work. So, I added Reference Check Names, thinking I needed another table to get data from.

Whenever I try to pull data for contact and reference using contact_ID I get only one set of data.

[RalphL]

As I see this you have companys, employees, candidates and references. The later 3 are all in the same table contact. For employee there is a one to many relaationship from company. For candidate there is many to many relationship to company. For reference there is a many to many relationship with candidate. This will require 2 join tables, 1 between company and contact and 1 between contact and contact.

[aldipalo]

Ok, I have the relationship between company and contact. CO_ID.

I set up 2 ID fields for Contacts. Contact_ID and Ref_ID. I set the relationship between the two as Contacts2 table. I still can't get the "Candidate data" and the "Reference data" on the same layout. Is my relationship wrong? Should I use another field?

[RalphL]

Have you set up the 2 join tables?

[RalphL]

Attached is a crude example of what I have been saying.

Aldi.fp7.zip

[aldipalo]

Hi Ralph:

I'm getting a "file damaged" error message. Can you re-download it? Thanks.

Al

[RalphL]

Have you expanded it?

Here is another copy.

Aldi.fp7.zip

[aldipalo]

Hi Ralph:

Ok, I set up my relationships they way you laid it out. (I think!) Here's what's happening. First, I enter the ref_ID, let's say 77514, where the contact/candidate is 77515. When I tab out of the field 77515 shows instead of 77514 and the data fields are filled with 77515 data. So, I go back and re-enter 77514 and then it fills the field correctly. Then when I click outside the portal the data disappears. However, the data shows correctly in the reference check layout. Why would this happen? Conversely, when I enter data in the reference check layout it does not show up in the portal on the contacts layout. Any ideas as to why this is happening?

Thanks again for your assistance.

Al

[RalphL]

Hi Al,

Sorry, I missed this yesterday.

What table are you in? Where are you entering ref_ID?

Is your Reference Check the same as my Reference?

Is your layout using the correct TO?

[aldipalo]

What table are you in? [color:blue]I've attached a zip file with 2 files. The relationship graph and the screen layouts and tables associated with them.

Where are you entering ref_ID?

[color:blue]I'm entering the data into the portal. I tried it the other way as well and the data still does not show in the portal.

Is your Reference Check the same as my Reference?

[color:blue]See attached graph.

Is your layout using the correct TO?

[color:blue]Not sure. Please explain.

Ref_Check_relationships-2.zip

[RalphL]

I have downloaded your file. I will look it over and get back to you.

[RalphL]

Your relationships are not quite like mine.

I reworked my crude demo. I use the Anchor- Bouy method fro my graph.

The tables Reference and Candidate are join tables. They have only the foreign keys. They can have other fields but not fields that are in another table.

My reworked demo is attached.

Aldi.fp7.1.zip

Edited December 3, 2005 by Guest