How to create a duplicate record in one table from another unrelated table

[claykhtx]

I am working on a Business Development Tool where we have one table for tracking leads generated from a bunch of sources within the company. This table has some very basic information such as project name, architect, owner, location, etc. One of the BD guys will then vet the leads and if they are good leads promote them to an opportunities table where most of the work is done on the lead. There is no relationship between these two tables since once the lead is promoted out of the Leads table into the Opportunities table, it is deleted out of the leads table. Can someone help me with a script that would allow me to move a record out of the leads table into the opportunities table? The info contained in the leads table is duplicated in the opportunities table, so it would be easy to map the fields for using an import. Any help would be greatly appreciated.

[No_access]

you may want to look at

http://fmforums.com/forum/topic/86044-scripting-to-copy-fields-between-tables/

[LaRetta]

Welcome to FMForums!

There is no relationship between these two tables since once the lead is promoted out of the Leads table into the Opportunities table, it is deleted out of the leads table.

Moving then deleting records is not the best idea because if you want to search and you know the person's name but not whether they are Lead or Opportunities, you will need to search both tables or make your solution more complex to compensate. Also, if you have a system problem during transfer, you can lose the person in BOTH places. You will also be unable to generate reports such as wanting to count ALL CLIENTS by State.

The info contained in the leads table is duplicated in the opportunities table, so it would be easy to map the fields for using an import.

Even if you must have two tables, they should relate so just set a variable with the LeadID and create an Opportunities record and insert the LeadID (as referenced in the link by No_Access). But all PEOPLE should be in a single table. Different attributes they posses can be in other tables as needed. If you have more questions, please ask away! There are many great folks here (also a pointie-ear'd pink-skinned creature) that are willing to assist.