Consolidated Count from another table by date field

[Oscar_Shine]

Here is what I am trying to do.

Table 1 has two fields (date, # widgets)
Table 2 has two fields (date, total widgets)

Table 1 has 4 records. Here they are:
1/1/2020, 2
1/1/2020, 5
1/7/2020, 1
1/7/2020, 1

I want Table 2 to sum up all the widgets by date. So I would want Table 2 to show…

1/1/2020, 7
1/7/1010, 2

How can I do this? I would manually add the date in Table 2 and the Total Widgets field would automatically add up the # Widgets field from Table 1 based on the date.

Is it possible to do this? If so… how?

Thanks so much!

[ScottWorld]

You can’t automatically do that in Table 2. You would have to manually link the records, like you said.

However, you could automatically do it in Table 1. Just group your records by date, and then the summary bar will appear above each group.

So, in your case, it sounds like you could just eliminate Table 2 altogether.

[Oscar_Shine]

Hey Scott, thanks for the reply. …Man… that’s a serious bummer and something that should be a basic feature for a relational database.

I already have Table 1 grouped, but would like to summarize and calculate the compiled data in Table 2. hmmm… Looks like I may have to figure out another solution.

Thanks again!

[ScottWorld]

You can link your records from Table 1 to Table 2, and then do what you want in Table 2. But you’ll need to link your records first.

[Oscar_Shine]

Yeah. I tried that, but for the life of me could not make it do the sumations.

If I do link the records don’t you have to link the records you want and it doesn’t automatically link newly added records in Table 1?

[ScottWorld]

That is correct. Unless you setup an automation with something like Integromat or JavaScript, you have to manually link records in Airtable.

After you’ve linked your records in the way that you’d like, you could use lookup and/or rollup fields to get the data you want.

[JonathanBowen]

You can use an Airtable script to do the summarising for you. I wrote a post about this scenario here:

Airscript – 12 Jun 20

Adding it all up

Using “reduce” to group your data by something

[gwynn_kruger]

Using @JonathanBowen’s blog post as a starting point you can have a script that’s something like:

let table1 = base.getTable('Table 1');
let table1Query = await table1.selectRecordsAsync();

let totalsArray = table1Query.records.reduce((accumulator, record) => {
    let date = record.getCellValue('Date');
    let widgets = record.getCellValue('Widgets');
    let found = accumulator.find(item => item['Date'] == date);
    if (!found) {
        accumulator.push({'Date': date, 'Total Widgets': widgets})
    }
    else {
        found['Total Widgets'] = found['Total Widgets'] + widgets
    }
    return accumulator;
    }, []);

let table2 = base.getTable('Table 2');
let dateField = table2.getField('Date');
let table2Query = await table2.selectRecordsAsync();

for (let table2Record of table2Query.records) {
	let date = table2Record.getCellValue('Date');
	var foundIndex = totalsArray.findIndex(item => item['Date'] == date);
	if (foundIndex >= 0) {
		// Update dates already in table 2 with new count
		await table2.updateRecordAsync(table2Record, totalsArray[foundIndex]);
		
		// remove the item from the array
		totalsArray.splice(foundIndex, 1);
	}
	else {
		// zero  out any dates that are in table 2 but not table 1.
		await table2.updateRecordAsync(table2Record, {'Date': date, 'Total Widgets': 0});
	}
}

// Add new dates to table 2 with counts
for (let totalRecord of totalsArray) {
	await table2.createRecordAsync(totalRecord);
}

[Oscar_Shine]

Thank you so much guys, you’re the best! Really appreciate the help on this one. I’ll implement using the script. Great job!