Avatar of noad
Flag 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 :)
FileMaker Pro

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck


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?


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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.


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.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.


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

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