AnsweredAssumed Answered

Ideas for easy transfering records between 2 unrelated tables

Question asked by on May 10, 2018
Latest reply on May 11, 2018 by Jason Wood

First - everyone here is amazing and I couldn't do my job without you - so thank you!  I have filemaker pro 12.



I have four tables: project, contacts, region, and interests.  The basic set up is I am forced to have 3 linking table for 3 many-many tables for projects::contacts, regions::contacts, and interest::contacts. The end goal is to identify all the contacts that will be participating in a project, by either region(s) and/or interest(s).  I already know the regions and interests of each contact, both of which are managed in separate linking tables: region::contact linking table and the interest::contact linking table.   The contact id is the same throughout the database.



Basically, I need to take all the contacts ids from x regions and paste it in the project::contact linking table and add the project id to complete the link.  I can either do this step one at a time by copying contact id from the regions form which has the contacts listed in the child portal (which is the regions::contacts linking table) and then pasting it in the project form's child table (which is the project::contact table linking table).  OR - I can do it in batches, where I am searching for the desired regions in the region::contact linking table, and then export all the contacts ids into an excel spreadsheet, from which I manually add a column with the project id and then import it into the project::contact linking table.


I do the same thing with the interests, because sometimes I need to add clients with particular interests, regardless of their region and vice-versa (nothing standard).  I am just taking potential contacts from a pool of set information.  In the end, I have a full list of contacts for my project.



There has to be a simpler way.  And, my colleagues are not proficient enough with databases to be able to do the export/import and sometimes we have over 600 contacts, so copying and pasting one at a time is not an option.  Ideally, the user needs to start in the region form, searches for desired regions, and clicks a button to set off a script to copy all associated contact ids and paste it into the project::contact linking table along with the desired project id.  I was thinking I could create a loop script.


In a nutshell - how can I copy found contact ids from one portal and paste it into another (while also adding the project primary key)?



I can see what I need to do, but not sure how to get there.  Any ideas/suggestions?