Link to home
Start Free TrialLog in
Avatar of noad
noadFlag for United States of America

asked on

Data from one table to another

I have a table called New Leads, I like populate the first name, Last name, work phone ect.. or to the Contact table.
both the New Leads table and the Contact table have a PK and the contact table also has a FK.

So if I enter a new Lead and I type in the new Leads first name, last name ect.... I also want to make a new contact at the same time.

What I don't want is the opposite, meaning if I start a new contact, I don't want it to start a new lead.

I have tried different things, linking the Pk to Pk / Pk to Fk...
I cant even get it to populate over from the new leads table to the contact table.

How would I go about doing thins???

Thanks :)
Avatar of North2Alaska
North2Alaska
Flag of United States of America image

In FM relationships are bi-directional.  Not much you can do about that.  But just because it can doesn't mean you have to use it.  :-)

Can a lead have multiple contacts?  Can a contact be linked to multiple leads?  We have to answer these two questions before we can move forward with a design.
:-)  While this information is very interesting and may play a part in your solution, you haven't answered my questions.  ;-)

So, assuming that you have a many to many relationship, you will have to create a join table.  This would consist of the keys from each table; Lead_ID and Contact_ID.  

Next create a layout based on Leads.  Create a portal for Contacts based on the join table.  From here things get a little trickier.  You will need to create a method to select/create the contact, grab the key and store it in the joining table.
Avatar of noad

ASKER

North,

I think that your answer will be from Leads ( One to One )
in other words I like to create a lead, while only creating one contact for that lead.

AS for the Contact I think that answer there would be ( None ) ???
I want to be able to create a contact, but not generate a lead.

Can that be done?
Avatar of noad

ASKER

innocentdevil

thanks for the link , will read itnow
OK,  so what I'm hearing is that a Lead will only have one contact assigned to it.  And (this is still fuzzy) one contact can be assigned to multiple leads.  

Now that doesn't mean to say that when you create a contact, a lead gets created too.  What it means is if I'm one of your contacts, I can provide you with many leads.

It makes a difference as to how to model the data.

With these assumptions, your leads table will have a primary key, ID.  Your contact table will have a primary key, ID.  Now the leads table will also have a foreign key for Contact_ID.  So for every lead, you can assign only one contact.  But through the join a contact can be assigned to 0 to many leads.

Now, create the leads layout.  And we have to populate the Contact_ID on the leads table (depending how you do it, it may not have to be displayed).  There are many ways to populate this column, but at the end of the day the value will be the Contact_ID.  Your method will need to know which contact you have selected and what the ID is to push into the Leads table.  You will also need to consider how to create a new lead, either through this process or some other method entirely.
Avatar of noad

ASKER

North

I created a test file, but, cant upload b/c I'm using FMP12
I'll send you snap shoot.
I have done most of what you explain, except when I create a lead
It dose create a contact which is what I want. But  when I create a contact
It still create a lead, which is what I don't want.

On the Lead table I have a PK on the Contact table I have a PK & FK
And I made a relationship been the the Leads PK n Contact FK
And the Contact layout is getting info from the leads table.
ASKER CERTIFIED SOLUTION
Avatar of North2Alaska
North2Alaska
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of noad

ASKER

really????
but I can still create a Lead record when I create a contact...
I'll try linking that way
The relationship is the key.  When you create a Lead, there is a place to put the contact_id making the relationship.  If the contact already exists, you will need a way to select that contact and assign the contact_id of the leads table.  Or you can create a new contact when you create the new lead and FM will make the assignment for you.  

Now if you create a new contact just from the contacts layout, there is nothing to assign on the contact side to make a lead, so no lead is created.
Avatar of noad

ASKER

North

OK...
I think I understand exactly what you mean. I'll try it

North,
 going to post a new question, love you help on it...
Really need this to be working A.S.A.P.