Copying and reorganizing records into new table

Note As I neared the bottom of this message, I realized a much simpler method that would likely work for you. I’m leaving the longer version in case the quick-and-dirty method doesn’t work for you, or for anyone reading over your shoulder who might have a use for the more complicated approach.

I ran into a similar problem on a client base a while back. Their main data source consisted of attendee records from various conferences they sponsored, taken from badge scans performed at the door of each conference session. An individual’s badge number remained constant across all sessions for a given conference, but there was no overarching ID associated with an individual that held constant across conferences. (To make matters worse, over a 2- or 3-year period, a significant percentage of their attendees changed email addresses, either as a result of job transitions or as a side effect of mergers and acquisitions in their target industry.)

What I did was create a data layer beneath the main one for the base – that is, beneath the attendee level. With the first block of session data imported, I assigned each previously unknown attendee an arbitrary user ID based on an autonumber mashed with a few additional pertinent fields. Subsequent blocks pulled together all of an individual’s attendance data from that conference, tied together by badge ID.

I followed the same process with subsequent conferences – except now I activated data deduplication routines similar to the ones I published a few months back. I used a technique similar to the one I describe in the dedupe users guide under Merger of Authentic Duplicates to allow me to link together records representing the same individual across multiple conferences. In my case, this was by necessity a manual process, as I had to check near-matches in name, last name + employer, and/or email, but with a common key (email), much more of your task could be automated. I then passed attendee data from the most recent conference to the master user record. (That is, the master user record links to multiple conference records for the same individual; each conference record contains information on the individual’s employer, title, mailing address, phone, email, and the like at the time of the conference. Based on the assumption the most-recent data have the greatest probability of being accurate, and using the trick @Matt_Bush outlines in this reply, I bubble up the most-recent data to the master user record.)

Possibly simpler approach: It just dawned on me your situation offers a potential short-cut:

1. Define a [MasterUser] table. The primary field is email address; you can include whatever other fields you deem necessary ({Member ID} seems like a good bet…)
2. Define a new linked-record field in your main table that links to [MasterUser].
3. Define a view in your main table that places {Link2MasterUser} adjacent to {Email}. (Actually, to be safe, you should probably create a formula field called {CookedEmail} with the formula LOWER({Email}). Place {CookedEmail} adjacent to {Link2MasterUser}.)
4. Left-Click the header for {CookedEmail}, which will mark all the cells in the column. (A handy message ### cells marked will appear in the lower-left of your screen.)
5. Press Ctrl-C to copy all of the values in the {CookedEmail} column. (The same, but for ### cells copied.)
6. Left-click the header for {Link2MasterUser}. (Ditto.)
7. Press Ctrl-V to paste the copied values into {Link2MasterUser}. (Pasting... floats up in the lower left, followed by Paste complete.)

At this point, you will have a [MasterUser] table containing a single record for each unique email found in your base, with each master user record linked to all applicable orders for that user. You can then continue to batch-load later orders whenever convenient (filter your view by {Link2MasterUser} is empty), manually enter new records one at a time (Ctrl-C, Right-Arrow, Ctrl-V), or set up a Zap to perform the copy/paste on each new record created.

Obviously, once you have [MasterUser] created, you can use a lookup or rollup field to pull {MasterUserID} (which you either get from somewhere or generate using some autonumber-containing algorithm) back into your individual order records.

(I hope this is coherent; I’ve been waging a losing battle with my right contact lens for most of this reply, so my proofreading ability isn’t up to par. Let me know if anything needs another pass…)