We'd need to know more about your database structure before we can advise you effectively. When you say "migrate them over to a new service location", what exactly are you trying to do?
Enter data in a new table?
Make new entries in the same table but with one or more fields (such as a "service location") modified?
Please describe your tables, relationships and portals in more detail.
We have multiple service locations for our clients. We are opening a new office and will be transferring some clients to the new location. We want to keep the current records in place for the clients' current location and start the new office with all of each client's history. Each client has a unique CaseID (serial number assigned by the database) so we will need to change the CaseID (new CaseID for each client transferred to the new office) and the office location. On the client screen we have a portal with two tabs that will need to be duplicated with the client. We also have a related table containing daily log entries for each client from date of intake. CaseID is the link between all the relationships.
One client to one referral and/or intake
One client to many daily logs
I am self-taught on this, so my terminology may not be accurate! :smileyhappy:
It sounds like you want to duplicate a group of records, keep them in the same table, but assign a new value to the CaseID field.
I think you have three tables involved:
With the following relationships:
The key question: are the records you want to duplicate stored in Client or Referrals?
In either case, it should be possible to use a layout based on either Client or Referrals and
Find the records.
Use Import Records to copy the found set from the current table into the current table
Use Replace Field Contents to assign a new CaseID value to each newly imported/duplicated record.
It is possible that you might be better served by creating a ServiceLocation Table with a ServiceLocationID field and then link your clients to the appropriate record in that table. Moving clients to a different service location would then simply require changing the ServiceLocationID field of each such record in Clients.
Each client's main information is stored in the client table. Each client has a "referral table" and an "Intake table" related to them via the caseID. We do have a separate table for service locations. And there is a portal on the daily logs for a table containing daily contact codes that needs to be copied also.
I didn't quite know how to do all you suggested, but was able to use what I did understand to accomplish the task. I copied my database (it isn't overly large), deleted all the records I didn't want to duplicate from the main client entry table which deletes all related information also, changed the office location, updated the CaseID field, and imported the resulting information back into my original database. It worked great. Thanks for the help!