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

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

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

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…

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

Which could then give you this:

Does that work?

JB

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

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.

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:

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

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.

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

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.

Hi, I received reply from Airtable customer service. According to them,
Dedupe is only able to marge whole of data A or whole of data B, we are not able to mix and match from data A or B.
I think we are not asking too much, so I switched this subject as ‘Prouduct Suggesetions’,
I wonder if I could get support from lots of people who had same experience.
:thinking:

Agree it so simple to code ! And so natural for a merging tool !

Kind of annoying that this simple update to the Merge App hasn’t been made. Time we poked Airtable devs for this feature. The description of the app says “Identify duplicate records and delete or merge them” - but this is absolutely not the case as there is no ability to merge two records with differing “Look Up” fields into one “Look Up” field. The user can choose which record field they want to keep, but that is not the same as a merge.

+1 on all of the above – another simple yet critical feature that is making my use of the tool very frustrating.

Hi,

I had similar scenario and even tried to write my own ‘bulk deduper’ script to merge data in hundreds of duplicates, in table containing thousands of records. Result was far from what I’ve expected.

Instead, I can recommend following: use second method from here , add more lookups in temporary table (don’t forget you may use built-in lookup conditional filter).