Relationship
I having a hard time getting straight this relationship thing.
Here is what I understand
I have 2 tables
Leads & Contacts
In table Leads I created the following fields
_kp_leads_id ( this is my primary key )
name
--------------------------
In table contact I created the following fields
_kp_contact_id
name
_kf_leads_id
then I created a relationship from the _kp_leads to the kf_leads_id
Now I get confused from here on in....
I want to be able to create a record in the leads table and at the same time create the same record in the contacts table.
When I create a record in the contacts table, I don't want to create that record in the leads table.
Also How do I show the contacts info in the Leads table?
Can someone please explain it to me in a very simple way, so that i can understand this very important step and be able to move forward.
Thanks All
Here is what I understand
I have 2 tables
Leads & Contacts
In table Leads I created the following fields
_kp_leads_id ( this is my primary key )
name
--------------------------
In table contact I created the following fields
_kp_contact_id
name
_kf_leads_id
then I created a relationship from the _kp_leads to the kf_leads_id
Now I get confused from here on in....
I want to be able to create a record in the leads table and at the same time create the same record in the contacts table.
When I create a record in the contacts table, I don't want to create that record in the leads table.
Also How do I show the contacts info in the Leads table?
Can someone please explain it to me in a very simple way, so that i can understand this very important step and be able to move forward.
Thanks All
ASKER
I just want to understand how it all works, regardless if I need it or not.
I want all of my Leads to create a contact, but when I create a contact it will not be a lead, so I don't want to to create a lead when a contact is created.
That's just something that I'm playing with to understand the relationship and how it work.
That's what I would like a simple clear cut explanation of.
Thanks
I want all of my Leads to create a contact, but when I create a contact it will not be a lead, so I don't want to to create a lead when a contact is created.
That's just something that I'm playing with to understand the relationship and how it work.
That's what I would like a simple clear cut explanation of.
Thanks
Relationships don't create data.
Depending on your database software you may use triggers that could create data in other tables. For more information about relationships, check out:
http://office.microsoft.co
Triggers are discussed here: http://office.microsoft.co
Depending on your database software you may use triggers that could create data in other tables. For more information about relationships, check out:
http://office.microsoft.co
Triggers are discussed here: http://office.microsoft.co
ASKER
So you mean to say that if I create a relationship between the Leads & Contacts tables I can only show data from the Contacts table?
I'm talking about File Maker Pro...
I'm talking about File Maker Pro...
No, that's not what I mean.
Here is how I would do it:
Let's say your contact table has the following fields; your will have many more fields, I am sure:
contactID
contactName
contactPhone
Your Lead table has the following fields:
leadID
lcID (Contact ID FK to ContactID)
leadStatus
leadManager
When a contact is created, all their information is stored in the contact table. If a lead is created, then the contact portion of the information is stored in the contact table, and the lead portion is stored in the lead table. If you want to see the full information about a lead, you would issue a SQL statement like
That should return all the lead information including the contact side of the data.
Here is how I would do it:
Let's say your contact table has the following fields; your will have many more fields, I am sure:
contactID
contactName
contactPhone
Your Lead table has the following fields:
leadID
lcID (Contact ID FK to ContactID)
leadStatus
leadManager
When a contact is created, all their information is stored in the contact table. If a lead is created, then the contact portion of the information is stored in the contact table, and the lead portion is stored in the lead table. If you want to see the full information about a lead, you would issue a SQL statement like
Select *
From Lead
Join Contact on lcID = contactID
Where <put whatever filters you need>That should return all the lead information including the contact side of the data.
ASKER
Bill
Thank you so much for the info you have given me, it very valuable, but I'm doing the data base on File Maker Pro.
Dose the same theory apply???
If so can you explain how I would do it on File Maker Pro?
Thank you so much for the info you have given me, it very valuable, but I'm doing the data base on File Maker Pro.
Dose the same theory apply???
If so can you explain how I would do it on File Maker Pro?
The theory is correct, in that you would have your contact data (name, address etc) in the Contacts table, and the 'lead specific' data in the Leads table. This assumes that there could be more than one Lead per Contact. If there will only be one lead per contact then as billfusion suggests, there is no need for a Lead table...
Create a _kf_contact_id field in the Leads table.
In the relationship graph, link Contacts::_kp_contact_id to Leads::_kf_contact_id and 'enable creation of new records' on the Leads side of the relationship.
On your Contacts layout, add a portal to Leads and choose the fields from the Leads table that you want to use.
When you want to add a new Lead, first enter all the contact details, then the lead details in the portal row. If you have a new lead for that contact, you can enter them on a new line.
Alternatively, you could do it the other way around, by creating the contact record from the Lead layout, but you run the risk then of duplicate contacts if you are creating a lead for a pre-existing contact. The first method is better in my opinion.
Hope this makes sense.
Create a _kf_contact_id field in the Leads table.
In the relationship graph, link Contacts::_kp_contact_id to Leads::_kf_contact_id and 'enable creation of new records' on the Leads side of the relationship.
On your Contacts layout, add a portal to Leads and choose the fields from the Leads table that you want to use.
When you want to add a new Lead, first enter all the contact details, then the lead details in the portal row. If you have a new lead for that contact, you can enter them on a new line.
Alternatively, you could do it the other way around, by creating the contact record from the Lead layout, but you run the risk then of duplicate contacts if you are creating a lead for a pre-existing contact. The first method is better in my opinion.
Hope this makes sense.
ASKER
TV
I think I'm starting to understand, correct if I'm wrong, this is what I like to do.
I have a table ( Leads ) in that table I have for experiment prepossess the following fields
_kp_leads_id ( primary key )
_kf_leads_id ( foreign key )
name
phone
I also have a table ( contacts ) in that table I have the following fields
_kp_contacts_id ( primary key )
_kf_leads_id ( foreign key from the leads table )
name
phone
If I understand you correctly I draw a line from the Leads table _kp_leads_id to the kf_contacts_id, this will allow creation of a records in the contact table when I create a new Leads record.
I don't want to create a new lead when I crate a new contact.
I do understand why I should do it the other way to prevent data duplication.
This is just testing on my side...
Do I have it right???
I think I'm starting to understand, correct if I'm wrong, this is what I like to do.
I have a table ( Leads ) in that table I have for experiment prepossess the following fields
_kp_leads_id ( primary key )
_kf_leads_id ( foreign key )
name
phone
I also have a table ( contacts ) in that table I have the following fields
_kp_contacts_id ( primary key )
_kf_leads_id ( foreign key from the leads table )
name
phone
If I understand you correctly I draw a line from the Leads table _kp_leads_id to the kf_contacts_id, this will allow creation of a records in the contact table when I create a new Leads record.
I don't want to create a new lead when I crate a new contact.
I do understand why I should do it the other way to prevent data duplication.
This is just testing on my side...
Do I have it right???
Hi
Not exactly :)
You don't want to have the name and phone fields in more than one table. Keep those in the Contacts table and simply show the 'related' fields on the Leads layout.
Attached is a quick example file. There is a Leads layout which also displays the Name and Phone fields from the Contacts table. If there is no related Contact, then typing in either of those fields will create one.
As I said before, I wouldn't do it like this, but for your testing purposes I hope this helps to explain how it works...
Cheers
EE-Demo.fp7
Do I have it right???
Not exactly :)
You don't want to have the name and phone fields in more than one table. Keep those in the Contacts table and simply show the 'related' fields on the Leads layout.
Attached is a quick example file. There is a Leads layout which also displays the Name and Phone fields from the Contacts table. If there is no related Contact, then typing in either of those fields will create one.
As I said before, I wouldn't do it like this, but for your testing purposes I hope this helps to explain how it works...
Cheers
EE-Demo.fp7
In your original post you said you wanted to create a record in leads and at the same time create a matching record in contacts. This creates a redundancy in data which is what the relationship model is trying to avoid. There are often good reasons to have this kind of redundancy. But if you are trying to understand the basics of the relationship model it is not a good choice.
Tables are designed to hold unique data and the relationships are built to show the (usually) one to many records relationship. For example, in Contacts if you wanted to enter unlimited phone data you could create a table to hold the phone data so that you would not be limited to the number of phones that you could enter. The phone table and the contacts table would be related by the ID field which would match. The main contact record would have a portal (filemaker speak) into which you could enter phone numbers and a description of it. the phone records would be the related records to the contact record.
Contact Phones
(ID) (Name) (ID) (Phone info)
100 home phone
100 John 100 cell phone
100 work phone
the creation of a record in a related table does not happen automatically when a record is created in a parent record. The child records are created by entering data in a portal or by using a script trigger to populate the child record when something else happens (Clicking a Contact checkbox for example).
Relationships are also used for filtering but that is a lesson for another day.
Tables are designed to hold unique data and the relationships are built to show the (usually) one to many records relationship. For example, in Contacts if you wanted to enter unlimited phone data you could create a table to hold the phone data so that you would not be limited to the number of phones that you could enter. The phone table and the contacts table would be related by the ID field which would match. The main contact record would have a portal (filemaker speak) into which you could enter phone numbers and a description of it. the phone records would be the related records to the contact record.
Contact Phones
(ID) (Name) (ID) (Phone info)
100 home phone
100 John 100 cell phone
100 work phone
the creation of a record in a related table does not happen automatically when a record is created in a parent record. The child records are created by entering data in a portal or by using a script trigger to populate the child record when something else happens (Clicking a Contact checkbox for example).
Relationships are also used for filtering but that is a lesson for another day.
ASKER
John
Thanks for your explanation, thanks to TV example I'm starting to understand
Thanks for your explanation, thanks to TV example I'm starting to understand
ASKER
TV
Can I or should I show the records in a portal instead of just putting the related fields in the layout like in your example?
Can I or should I show the records in a portal instead of just putting the related fields in the layout like in your example?
That depends on what you want to do. You can certainly show related records in a portal if it matches your data model, and indeed that's what I started to do in the example file, but (to my mind at least) a Lead would not be related to multiple contacts. However, I don't know how you intend to use this, it may be that this is what you want.
In any case, yes you can show records in a portal, that's what they're designed for, for showing multiple 'child' records linked to one parent.
Regards
In any case, yes you can show records in a portal, that's what they're designed for, for showing multiple 'child' records linked to one parent.
Regards
ASKER
TV
I was able to create a new test db to re-create what you have explained to me so far and it worked, I was able to create a record in the dealers & contact table.
Now when I tried to create a record in the dealers table form the contact table I get an error.
I'm following the same format....
Why the error?
I was able to create a new test db to re-create what you have explained to me so far and it worked, I was able to create a record in the dealers & contact table.
Now when I tried to create a record in the dealers table form the contact table I get an error.
I'm following the same format....
Why the error?
In FileMaker it's all about context....
Is your 'Contacts' layout based on the 'Contacts' table occurrence? If so, then you'll want the 'Dealers' fields to be from the 'Dealers to Contacts' table occurrence (not 'Dealers').
I suspect that you're using the latter, which would require a corresponding record in 'Leads', since the link between 'Contacts' and 'Dealers' is via this table occurrence. You'd need 'Allow creation of related records' enabled in the relationships between Contacts and Leads, as well as Leads and Dealers.
That said, from looking at your graph and the layout, I'm not entirely sure that you have the best data structure, but that's perhaps a different question...
Hope this helps.
Is your 'Contacts' layout based on the 'Contacts' table occurrence? If so, then you'll want the 'Dealers' fields to be from the 'Dealers to Contacts' table occurrence (not 'Dealers').
I suspect that you're using the latter, which would require a corresponding record in 'Leads', since the link between 'Contacts' and 'Dealers' is via this table occurrence. You'd need 'Allow creation of related records' enabled in the relationships between Contacts and Leads, as well as Leads and Dealers.
That said, from looking at your graph and the layout, I'm not entirely sure that you have the best data structure, but that's perhaps a different question...
Hope this helps.
ASKER
This is just for testing....
OK, I got it working...
But I now see what you mean.
In the contact layout I created a contact and a dealer, which works correctly
But when I go to the Dealers layout to see if it created a dealers, and it did create it I see that the contact part is empty...
I need to be able to show the contact info that was created in the contact layout, if not someone can come in an type in a different contact for that dealer, there by having to different contacts for the same dealer and the same problem holds true from Dealers to contacts.
How can I fix this?
I pretty sure that this is what you where talking about in the begging, correct???
OK, I got it working...
But I now see what you mean.
In the contact layout I created a contact and a dealer, which works correctly
But when I go to the Dealers layout to see if it created a dealers, and it did create it I see that the contact part is empty...
I need to be able to show the contact info that was created in the contact layout, if not someone can come in an type in a different contact for that dealer, there by having to different contacts for the same dealer and the same problem holds true from Dealers to contacts.
How can I fix this?
I pretty sure that this is what you where talking about in the begging, correct???
Where are you getting those Contacts fields from? As I said earlier, in FileMaker it's all about context...
You should be using fields from the Contacts to Dealers table occurrence (TO), assuming your layout is based on the Dealers TO. It may help to think of TOs as rooms, and the relationships as windows between them. "If I'm standing in the Dealers room, which window do I look through to see Contacts?"
Hope this helps.
You should be using fields from the Contacts to Dealers table occurrence (TO), assuming your layout is based on the Dealers TO. It may help to think of TOs as rooms, and the relationships as windows between them. "If I'm standing in the Dealers room, which window do I look through to see Contacts?"
Hope this helps.
ASKER
So are you saying that my relationship is wrong?
Here is a look at the relationships, so if I change them I should have the contacts filled out when I create a dealer and input the contact info?
Relationship
Contact to Dealer
Dealer to Contact
What do I need to correct so that when I create a dealer and input the contact info it also shows the contact info when I switch to the contact layout.
Also,
Thank you for all of your help with this Relationship, your explanations have help me a lot in understanding how they work and why.
Here is a look at the relationships, so if I change them I should have the contacts filled out when I create a dealer and input the contact info?
Relationship
Contact to Dealer
Dealer to Contact
What do I need to correct so that when I create a dealer and input the contact info it also shows the contact info when I switch to the contact layout.
Also,
Thank you for all of your help with this Relationship, your explanations have help me a lot in understanding how they work and why.
ASKER
TV...
Just to make it more clear in case I did not explain myself correctly.
Below you see where I created a contact on the contact table/layout and a dealer
When I switch to the dealers table/layout you can see the dealer that was created in the contact table/layout, but the contact is blank.
I'm think to avoid duplicate data entry the contact must show in the dealer table/layout. Correct???
How do I correct this issue?
Just to make it more clear in case I did not explain myself correctly.
Below you see where I created a contact on the contact table/layout and a dealer
When I switch to the dealers table/layout you can see the dealer that was created in the contact table/layout, but the contact is blank.
I'm think to avoid duplicate data entry the contact must show in the dealer table/layout. Correct???
How do I correct this issue?
Can you post your file? When I last checked there was an issue with uploading .fp12 files to Experts-Exchange, they don't recognise the file type, so if possible can you please email it to fmtvkid@gmail.com
Thanks
Thanks
ASKER
OK will do...
The other thing you should understand is that a relationship goes both ways. If you make a connection between two tables can be used from either end. Therefore you don't need both the connection by dealer id as well as the connection by contact ID. Just pick one. Additional relationships can be made for filtering purposes but use only one for data entry.
ASKER
Sorry,
I don't understand anything that you explained above.
Would you please explain exactly what you mean?
Also did you see the file that I emailed you?
Sorry to bother you, but I'm more confused.
I don't understand anything that you explained above.
Would you please explain exactly what you mean?
Also did you see the file that I emailed you?
Sorry to bother you, but I'm more confused.
ASKER
TV...
Did you get my email with the db?
Did you get my email with the db?
Hi,
Sorry, I've been out of touch for a bit...
I didn't get your email I'm afraid. It wasn't me who posted about relationships going both ways, although that is a valid point.
I've only been attempting to answer your specific questions, as you've said that you're just testing rather than trying to get the data model right. However, you seem to be tying yourself up in knots a little unnecessarily. If you can send me your file I'll take a look and make some changes that will hopefully make it a little clearer.
Cheers
Sorry, I've been out of touch for a bit...
I didn't get your email I'm afraid. It wasn't me who posted about relationships going both ways, although that is a valid point.
I've only been attempting to answer your specific questions, as you've said that you're just testing rather than trying to get the data model right. However, you seem to be tying yourself up in knots a little unnecessarily. If you can send me your file I'll take a look and make some changes that will hopefully make it a little clearer.
Cheers
The sample you have chosen to learn about the relationship model is a bit complicated for a basic parent-child relationship. Can Dealers be Contacts? Can contacts be dealers? I suspect the answer to both questions is Yes. Therefore I would design this with only two tables, Names and Leads and then create two relationships from Name to leads, one each on DealerID and ContactsID. make both relationships able to create related records from the Leads-based layout.
In Leads when you enter an ID in the dealerID it will relate to the names table and show the related data for the dealer side. I would create two value lists to help pick the Names. When you enter a value into the contactsID it will show contact info from the same Names table. You can determine if a particular name is a contact, a dealer or both from the leads table. If the name does not exist in the Names file it will be created automatically when you type in a name in the Leads-based layout.
EETest.fp7
In Leads when you enter an ID in the dealerID it will relate to the names table and show the related data for the dealer side. I would create two value lists to help pick the Names. When you enter a value into the contactsID it will show contact info from the same Names table. You can determine if a particular name is a contact, a dealer or both from the leads table. If the name does not exist in the Names file it will be created automatically when you type in a name in the Leads-based layout.
EETest.fp7
ASKER
TV...
I re send the file, let me know if you get it/
I re send the file, let me know if you get it/
ASKER
Jo
Thank you so much for you help, I'm going to look at it and hopefully figure it out.
Thank you so much for you help, I'm going to look at it and hopefully figure it out.
As JoJohn has said, I suspect you don't need a Dealers table separate from Contacts.
I'm a little confused with what you're trying to achieve, could you try explaining again please?
I'm a little confused with what you're trying to achieve, could you try explaining again please?
ASKER
Well,
OK
The first part I'm pretty sure that you already helped me with, which is to enter that data from the Leads table and via the relationship and putting the related fields in the Leads layout have it fill in on the Contacts and Dealers table.
Now the second part is what I can't seem to figure out.
I want to be able to create a contact and automatically fill in the dealers info in the dealers layout and then do the same thing on the contacts table.
Which to some extend I can do.
example...
If I create a new contact and via the relationship I fill in the related fields in the Dealers when I go to the Dealers table I do see that Dealers info, but the problem is that the contacts area is empty. My concern is that someone would go into the Dealers table see the contact fields empty and input the contact for that Dealer and create duplicate data.
Did you still not get my file that I e-mailed you?
OK
The first part I'm pretty sure that you already helped me with, which is to enter that data from the Leads table and via the relationship and putting the related fields in the Leads layout have it fill in on the Contacts and Dealers table.
Now the second part is what I can't seem to figure out.
I want to be able to create a contact and automatically fill in the dealers info in the dealers layout and then do the same thing on the contacts table.
Which to some extend I can do.
example...
If I create a new contact and via the relationship I fill in the related fields in the Dealers when I go to the Dealers table I do see that Dealers info, but the problem is that the contacts area is empty. My concern is that someone would go into the Dealers table see the contact fields empty and input the contact for that Dealer and create duplicate data.
Did you still not get my file that I e-mailed you?
Hi
Yes, I got the file, sorry.
OK, leaving aside whether or not you should be setting up your tables in this way...
The relationship you are using on the Contacts layout to create the Dealer is Dealers~contacts, which is linked via the contacts_ID field. However, from the Dealers layout you're trying to view Contacts using the Contacts~dealers relationship, which is linked via the dealers_ID field, hence there is no match and nothing is displayed.
The short answer is therefore to use the same match field in both cases.
HTH
Yes, I got the file, sorry.
OK, leaving aside whether or not you should be setting up your tables in this way...
If I create a new contact and via the relationship I fill in the related fields in the Dealers when I go to the Dealers table I do see that Dealers info, but the problem is that the contacts area is empty.
The relationship you are using on the Contacts layout to create the Dealer is Dealers~contacts, which is linked via the contacts_ID field. However, from the Dealers layout you're trying to view Contacts using the Contacts~dealers relationship, which is linked via the dealers_ID field, hence there is no match and nothing is displayed.
The short answer is therefore to use the same match field in both cases.
HTH
ASKER
Not sure, I follow...
can you draw it out for me...
I'm not sure, but I think I did what you just told me to, but then when I try to type in the filed I get the error that I showed you before, which at that time you told me to change it to the way I have it now....
can you pls draw it out so that I can see what you mean.....
Sorry to be a pain...
can you draw it out for me...
I'm not sure, but I think I did what you just told me to, but then when I try to type in the filed I get the error that I showed you before, which at that time you told me to change it to the way I have it now....
can you pls draw it out so that I can see what you mean.....
Sorry to be a pain...
Sure. Here's the amended relationship graph from the file you sent:
Here are the relationships:
And here is the Contacts layout. As you can see, I've created a new Contact record and can now add the related Dealer info...
And here is the Dealers layout showing the newly created Dealer and corresponding Contact info...
I'll send the amended file back via email.
Here are the relationships:
And here is the Contacts layout. As you can see, I've created a new Contact record and can now add the related Dealer info...
And here is the Dealers layout showing the newly created Dealer and corresponding Contact info...
I'll send the amended file back via email.
ASKER
TV
Thank you so much for all of your help and patience.
That works exactly how I want it to.
But You seem concern that I'm still doing something wrong...
If I'm not duplicating data, whats the harm?
Thank you so much for all of your help and patience.
That works exactly how I want it to.
But You seem concern that I'm still doing something wrong...
If I'm not duplicating data, whats the harm?
Managing large numbers of table occurrences and their relationships is complicated enough without having more than one that does the same thing. This will become apparent as your project grows. Creating a sound foundation is critical for a large project.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
TV
Again, thank you very much for all of your help.
Again, thank you very much for all of your help.
ASKER
Very good explanations
Relationships are useful when you have mutiple contacts referring to the same lead, multiple leads to contacts, or multiple contacts to multiple leads. Which doesn't seem to be what you need.