Skip to main content

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.

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.


Reply