Data from one table to another

noad
noad used Ask the Experts™
on
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 :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
North2AlaskaSenior Engineer

Commented:
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.
Kash2nd Line Engineer

Commented:
North2AlaskaSenior Engineer

Commented:
:-)  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.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
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?

Author

Commented:
innocentdevil

thanks for the link , will read itnow
North2AlaskaSenior Engineer

Commented:
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.

Author

Commented:
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.
Senior Engineer
Commented:
I think you have it backwards.  You should have a PK in leads and contacts.  The FK will be in the leads table pointing to contacts.

Author

Commented:
really????
but I can still create a Lead record when I create a contact...
I'll try linking that way
North2AlaskaSenior Engineer

Commented:
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.

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial