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?