Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

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

cancel
Showing results for 
Search instead for 
Did you mean: 
UNION
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

15 Comments
JonathanBowen
13 - Mars
13 - Mars

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:

44

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

JB

UNION
5 - Automation Enthusiast
5 - Automation Enthusiast

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
13 - Mars
13 - Mars

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

Screenshot 2019-04-17 at 14.43.40.png

Which could then give you this:

Screenshot 2019-04-17 at 14.44.00.png

Does that work?

JB

UNION
5 - Automation Enthusiast
5 - Automation Enthusiast

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
18 - Pluto
18 - Pluto

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:

21%20AM

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

25%20AM

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:

20%20AM

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

02%20AM

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:

59%20AM

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

23%20AM

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
13 - Mars
13 - Mars

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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)
unnamed

(After)

unnamed%20(1)
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,

unnamed (2).png

Emails are unified,

unnamed%20(3)

Then if I add Lookup [Purchased} from Table 2

unnamed (4).png

I get what I want, but

unnamed%20(5)

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.

unnamed (6).png

Hope I could explain better this time with pictures…

Best regards

Justin_Barrett
18 - Pluto
18 - Pluto

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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.