Sum number of records by user in a different table

[LaurynV]

Hello AirTable community! I am trying to create a separate table that takes just the created by name, their email, and the number of approved records they have (in this case, approved is referring to any record in the original table that receives a check mark) from the create table, and populate just those values in the notify table. The values are populated in there through an automation (if record has a check mark in the create table, put these specific values into the notify table)

The issue I'm running into is that in the notify table, if a user has multiple records approved, then their name will appear multiple times. I would prefer it to be their name, their email, and then a column with a summed up numbers of how many records they've had approved ( want it to be a real time moving number). I cannot figure it out for some reason. Please help, thank you!!

Current Output

Created By Name	Email	Number of Records
Spongebob SquarePants	ssquarepants@bikinibottom.com	1
Spongebob SquarePants	ssquarepants@bikinibottom.com	1
Patrick Star	pstar@bikinibottom.com	1
Patrick Star	pstar@bikinibottom.com	1
Patrick Star	pstar@bikinibottom.com	1
Mr. Krabs	mkrabs@bikinibottom.com	1

Desired Output

Created By Name	Email	Number of Records
Spongebob SquarePants	ssquarepants@bikinibottom.com	2
Patrick Star	pstar@bikinibottom.com	3
Mr. Krabs	mkrabs@bikinibottom.com	1

[Ron_Daniel]

@LaurynV ,

I would create a table of approver's names, and then link them to your first table. That way, the number of records could easily be counted. See screencaps, let me know if you have additional questions.

[LaurynV]

Thank you Ron! Are you setting up the Things table with an automation? I can't seem to grasp how to properly use the linked fields even after watching multiple videos. I'm assuming that field can't be edited by an automation, but would like for the values to autopopulate here.

[LaurynV]

 for example I have it set up similar to you, but I;m not getting any values populating in the created by and created by email fields. Am I doing something wrong or missing a step?

[Ron_Daniel]

Nope, fortunately no automations! Everything else happens by itself, as the Approvers table is linked to the Things table. So, any Approver that gets added to a Thing's record will automatically get that thing (and consequently, their Thing Count) showing up over in their Approver table.

Linked and Lookups can take a bit for most people to wrap their head around. If you'd like to set up a time for us to chat about it (free), please feel free: https://rondaniel.com/airtable/

[LaurynV]

Got it, Im unfortunately still struggling with grasping this. It would be really helpful to chat more about this, thank you for the offer!

[TheTimeSavingCo]

Try updating your automation so that it pastes the 'Created by name' value into the linked field to the Notify table instead, should do what you need:

To get the email, you'd create a rollup field with 'ARRAYUNIQUE', and for the number of records you'd use a Count field 

Here it is running:

Link to base

When you paste things into a linked field, Airtable looks for a record in that linked table that has the same value as the pasted in value.  If it finds one it'll link the record, and if it can't find anything it'll create the record in the linked table.  Caveats apply, but for the purposes of what you're trying to do I think you should be good to go!

[LaurynV]

Thank you so much Adam! The visuals are really helpful. I implemented some of the ideas you gave above and got a bit closer to my endpoint and the automation is actually running which is great! But I'm still running into issues where the automation is really slow to run, it's spitting out the wrong name even though I have it pulling Created By Name, and then for some reason I get the name to be a unique value in my version of your notify column ( it still shows user by individual row for some reason). Do you have any ideas on why that may be happening?

[TheTimeSavingCo]

Hmm, hard to say without seeing how the automation's set up, really.  Could you DM me an invite link to your base so I could take a look for you?

[LaurynV]

Understandable -- actually is it possible to set up an intro call with you to briefly discuss please? If possible, that would be more beneficial. If not, then we can discuss it more in DMs. Thanks 🙂