Cut or copy linked table records from one row to another without overwriting what is already in the destination row

[Matthew_Moran]

Long topic title… sorry.

I have a table of employees that are linked to various other tables.

At times, duplicate entries have been created in error or linked records attributed to the wrong person.

Is there a way, from within the employee table, to take all records from one linked field and paste them into another employee record, without overwriting what is there?

For example:
Julie Smith is duplicated in error. There is a table called clients.

Julie Smith (record 1) has 50 linked client records.
Julie Smith (record 2) has 20 linked client records.

I want to move the client records from Julie Smith (record 2) into Julie Smith (record 1). If I cut and paste the 20 records, it overwrites the existing 50 records.

This is true for 5 other linked tables across several records.

I can change the name of Julie Smith (record 2) to “Fix Julie Smith” and then paste over every record in the 5 linked tables. But, I’m providing a single example of this occurring. I’ve taken over administration and development on a base that has been in use for nearly 2 years and has numerous data integrity challenges.

It would be significantly easier to do this inside a source table and move linked data to consolidate records.

I hope this was explained clearly. Any insight is greatly appreciated.

[Matthew_Moran · Accepted solution]

I solved it:

Here is what I did.

First, in every linked table I have a formula field to give me the actual record ID:
Record_ID()

I do this to use in cut and paste procedures but also have a formula for a link to the record for key views - it saves my users some time.

In the above example: I create a field to the right of the linked client column in the employee table. It is a rollup on the client table, client record ID field, using ARRAYUNIQUE(values) as the rollup.

This gives me a column of comma separated client record ID’s.

I copy the field for both records (for Julie - above) and paste them into a Google Sheet. This gives me two cells with all the record ID’s for the linked clients.

So, I’ve copied one into A1 and the next record into A2.

I concatenate those in the google sheet:

In cell A3
= A1 & “,” & A2

I copy the value in A3. In AT, In the Julie Smith record I want to keep, I delete the values from the linked records cell and then paste what I copied from A3 into the field.

Voila! 70 records as needed.

Then I delete the Julie Smith field that is no longer necessary.

I hope I explained this well enough. If not, just ask and I’ll make it clearer.