Dedupe - Merge fields from duplicated records, Merge data A (AND) data B, instead of data A (OR) B

[UNION]

Hello, I like Dedupe function, but I feel one thing still missing…
For example, We have 1 set of Duplicated data here,
2 data has 2 different information in the same field, and I would like to merge 2 different information in the primary record.
Data 1 -Email = aaa@xxx.com Data 2-Email = 0 aaa@xxx.com
Data 1 -purchased= Apple Date 2-purchased = Lemon

I need to merge 2 data because e-mail is same, but I want to keep Apple and lemon under same data.
(I wanted to add a picture, but this system does not seem to allow me to do it… )

I understand we can select only one field to be primary field, but Is there any way to keep and merge all information from all duplicated data?
Looking forward to hear from you soon.

[JonathanBowen]

Hi @UNION - have a look at this thread, seems similar to your question in some way:

Form to enter contacts in linked column Views

Hi, I’m not sure this is even possible but this could be a lifesaver for me. I go into the field every day and take an inventory of what new items my contacts have purchased in the last few weeks. I would like to add their name to the specific product row, whether it’s one item or multiple items. I already have a tab with all my contacts and another tab with all the inventory but I need an easy way to add their name to each item they purchase, preferably a form I can fill out during my stop or …

I don’t totally get your scenario, but I seems like it is people purchasing things. I wonder if the same solution - 3 tables, people, products and purchases - would work for you.

Then you wouldn’t need to dedupe, but could just summarise using the email as the grouping field:

(in this example the summary is by name, but obviously, this could be summary by email address).

JB

[UNION]

Dear Jonathan,
Thank you for your quick response to me!
But I am afraid this is not my case,
In your example, Jim purchased Item 1 and Item 2 and Item3.
And, I want to merge the 3 different row for Jim into one row, and put Item 1-3 in [Item purchased] field.
I am hoping this is possible with Airtable…

[JonathanBowen]

Hi @UNION - you could make the item purchased field allow linking to multiple records:

Which could then give you this:

Does that work?

JB

[UNION]

Hi Jonathan, thank you again for your reply.
I already tried to link the field to multiple records.

But, in that case Item purchased field goes to Primary field on the new table, in this example, that is Inventory.
Can I link the field to another table, without making it as primary field(which is shown the most left side on the table)?

Best regards

[Justin_Barrett]

To me this sounds like a job for a rollup. Not knowing the specifics of your base, I’m going to just take a stab.

Say you have an [Emails] table with email addresses, as in your initial example:

And you have an [Inventory] table with items that people can purchase:

On a third table, which I’m calling [Orders] you have two link fields: one pointing to the [Emails] table, the other to the [Inventory] table. On that table, you’ve got something like this after a few orders have been entered:

Back in the [Emails] table, you’ll see a new field that links to the [Orders] table.

Next to that, add an {Items Ordered} rollup field, pointing to the {Item} field from the [Orders] table, and using “values” as the aggregation function:

…which gives you a collection of all items that were ordered by each email:

Does that setup address what you need?

BTW, to add a photo to your post, just drag and drop it into the post as you’re editing it.

[W_Vann_Hall]

It uses a somewhat different approach from the DeDupe Block, but the current version of my deduplication routines includes a section that allows you to make duplicate records children of a parent record. That is, you could make the ‘Lemon’ record for customer aaa@xxx.com a child record of the ‘Apple’ record and then use a lookup or rollup field to track both the apple and lemon purchases. (It also allows for ongoing, incremental deduplication and persistent tracking of false positives — that is, records that trip the alert as a possible duplicate but turn out not to be duplicates — which may or may not be of interest for your use case.)

A post describing the routines can be found here. There’s also a [too-]long video explaining their operation available here. Possibly more helpfully, a full user’s guide to the routines can be found here. You’ll want the section entitled ‘Merger of Authentic Duplicates,’ on pages 20–22. The guide is a little out-of-date, but in your favor: At the time it was written, Airtable did not yet support lookups and rollups in self-linked tables — that is, in a table containing a linked-record field that pointed back at itself. That functionality has since been added to Airtable, which makes merging of duplicates via parent-child links as described in the guide much more useful, so ignore the pessimistic caveat that closes that section.

I just realized I may have buried the lede, here, in that it’s quite likely you could implement some sort of parent-child self-linking to allow you to merge duplicate records while still relying upon the Dedupe Block. If so, that section of the guide might still be of use when it comes time to write code. :winking_face:

[UNION]

Dear Justine and W_Vann, thank you very much for responding my query.

From the topic on the web site, it seems I cannot attach the picture even by drugging the picture file on the message, or by uploading from my device, I am now trying again,

What I want to do is very simple…

I have a record like this,

(Before)

(After)

I already have data like this for nearly 1000 rows, and new rows are constantly coming every day

(Original data exist in Spreadsheet, and I am copying new rows from spreadsheet to Airtable)

I do not want to merge the data manually.

[Email] is a primary data so, it cannot be link to other table,
However, if I create another [Email] field as [Email2], and link to different table,

Emails are unified,

Then if I add Lookup [Purchased} from Table 2

I get what I want, but

I feel I am creating unnecessary data on one Base, and don’t think this is the most smart way.

I wonder if anybody can help me to merge the original data on Table2.

---

As alternative,
I can use dedupe, but it seems only one data ,
With Dedupe, [ Apple/Orange] or [Lemon] needs to be selected as primary,
Un-selected one is deleted after Merge,

The thing I want to do is Add Apple / Orange / Lemon under one data, but it does not seem to be possible.

Hope I could explain better this time with pictures…

Best regards

[Justin_Barrett]

Unfortunately the pictures didn’t come through. From what I can tell, they’re coming in as links from Google, but my hunch is that the permissions aren’t set up properly, or maybe you grabbed the wrong URL. It’s best to have them directly on your desktop, and then drag them into the post editor from there. That always works for me. I haven’t tried mobile image uploads, so I can’t offer any suggestions on how to make it work that way.

[UNION]

Dear Justin,
Thank you for pointing out, the pictures were displayed for me being logged-in on my PC, but I realized the picture links were broken for other people.
I linked photo files again!

Many thanks

[BGWiki]

I’m wondering if this issue ever got resolved. I am also having the problem where the Dedupe tool will not merge the lemon with the apple and orange in one record. Instead you must choose either apple & orange OR lemon to be the preserved “purchase” field for the xxx@yyy.com record. It seems like all the Dedupe does is allow you to choose which entire field you want to preserve in the merged record, like email from record 1 or record 2, purchased from record 1 or record 2. But what is needed it the ability to have purchased from record 1 AND 2 in the newly merged record.