Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Displaying # of duplicates in one table to another table

Solved
Jump to Solution
971 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Melanie_Lai
4 - Data Explorer
4 - Data Explorer

Is there a way to count the number of times a duplicate name appears in one table and display that number in another table? For example, Minnie Mouse’s name appears 3 times in the “Billing” table and I want to output the number 3 under a column for Minnie Mouse in the “Child” table.

The way my data is set up is I have 2 tables, one for “Child” and another for “Billing”.
airtable1

The “Billing” table keeps track of all the client with the months we billed them for, their name will appear multiple times corresponding to the month he/she was billed. This duplication is how our biller kept track of the data, by having a Jan, Feb, Mar, etc tabs in excel and the client’s name being inputted in the corresponding month tab. The “Child” table has a different set of data that can’t be combined with “Billing”
airtable2

I’ve been trying to understand other answered inquiries that was posted here, but my knowledge of setting up the logic statements for formulas is very elementary

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Melanie_Lai! :grinning_face_with_big_eyes: I think you could get what you want with a slight change to your base design.

Instead of having the person’s name as the primary field in your [Billing] table, I suggest using a link field named {Child} that lets you choose a record from your [Child] table. To make the primary field unique (which it should be), you could turn it into a formula that combines the child’s name with the month or date of the interaction. For example, if you record a date for the interaction, the formula could be something like this:

IF(AND(Child, Date), Child & " - " & DATETIME_FORMAT(Date, "L"))

The results of all of these changes would look something like this:

Screen Shot 2020-07-25 at 9.03.54 AM

You can then add a count field in the [Child] table to count the number of linked records, which will tell you how many interactions you had with that child.

Screen Shot 2020-07-25 at 9.04.53 AM

Screen Shot 2020-07-25 at 9.05.21 AM

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Melanie_Lai! :grinning_face_with_big_eyes: I think you could get what you want with a slight change to your base design.

Instead of having the person’s name as the primary field in your [Billing] table, I suggest using a link field named {Child} that lets you choose a record from your [Child] table. To make the primary field unique (which it should be), you could turn it into a formula that combines the child’s name with the month or date of the interaction. For example, if you record a date for the interaction, the formula could be something like this:

IF(AND(Child, Date), Child & " - " & DATETIME_FORMAT(Date, "L"))

The results of all of these changes would look something like this:

Screen Shot 2020-07-25 at 9.03.54 AM

You can then add a count field in the [Child] table to count the number of linked records, which will tell you how many interactions you had with that child.

Screen Shot 2020-07-25 at 9.04.53 AM

Screen Shot 2020-07-25 at 9.05.21 AM

Your solution fixed my problem! Thank you so much @Justin_Barrett
I greatly appreciate your help :thumbs_up: