Skip to main content
Solved

Counting unique dates of submission // pull data from Pivot Table app back into table

B

I teach a class where students have to share information in a daily form. They get credit for the number of days they submit a form, not for the total number of forms submitted. The form feeds a Daily Submissions table, which is linked to a Students table.

Because I didn’t see something equivalent to Sheets/Excel COUNTUNIQUE in Airtable, I created a Pivot Table app in the Daily Submissions to calculate Days Shared.


I’m trying to figure out whether I can pull the number of Days Shared from the Pivot Table app back into a field corresponding to that student in a table of students, so I can share a view of table that includes Days Shared.

If that’s not possible, is there a way to count the unique days in a table without a pivot table? There is a Lookup field in the Students table that collects all the days, including duplicates, but I don’t know how to dedupe that.

Or do I need to do create an interface that displays the Pivot Table app data?

Thanks for your help!

Best answer by Andy_Lin1

So instead of a lookup, you’ll want to use a rollup field with ARRAYUNIQUE(values). This will dedupe the list. By default, the array will be joined with (what looks like ) , , but we want something unique for the next step, so we’ll use ARRAYJOIN with an emoji, such as 🧾. This gives the final formula of:
ARRAYJOIN(ARRAYUNIQUE(values),"🧾")

Once we have that field set up, we’re going to use a formula field and two Excel tricks.
Excel trick #1: To count the number of items in a list, count the commas (instead of the items), and then add one (to account for the first item that doesn’t have a comma before it). In this case, we’re going to be using the emoji as a separator instead of a comma. There are other methods, but this works within the limitations of arrays and formulas in Airtable.

Excel trick #2: To count the instances of a character within a single string, substitute that character with nothing and comparing the length of the new string against the original.

Here’s what that looks like:

IF(
	{Unique Quiz Dates},
	1 +
	LEN({Unique Quiz Dates})
	- LEN(
		SUBSTITUTE({Unique Quiz Dates}, "🧾", "")
	),
	0
)

Here’s a base demonstrating the rollup and formula fields:

View original
Did this topic help you find an answer to your question?

2 replies

A
  • Andy_Lin1
  • Inspiring
  • 183 replies
  • Answer
  • February 15, 2022

So instead of a lookup, you’ll want to use a rollup field with ARRAYUNIQUE(values). This will dedupe the list. By default, the array will be joined with (what looks like ) , , but we want something unique for the next step, so we’ll use ARRAYJOIN with an emoji, such as 🧾. This gives the final formula of:
ARRAYJOIN(ARRAYUNIQUE(values),"🧾")

Once we have that field set up, we’re going to use a formula field and two Excel tricks.
Excel trick #1: To count the number of items in a list, count the commas (instead of the items), and then add one (to account for the first item that doesn’t have a comma before it). In this case, we’re going to be using the emoji as a separator instead of a comma. There are other methods, but this works within the limitations of arrays and formulas in Airtable.

Excel trick #2: To count the instances of a character within a single string, substitute that character with nothing and comparing the length of the new string against the original.

Here’s what that looks like:

IF(
	{Unique Quiz Dates},
	1 +
	LEN({Unique Quiz Dates})
	- LEN(
		SUBSTITUTE({Unique Quiz Dates}, "🧾", "")
	),
	0
)

Here’s a base demonstrating the rollup and formula fields:

B
J
2 people like this
  • B
    Blake_Eskin
  • J
    Justin_Barrett
B
  • Blake_Eskin
  • Author
  • New Participant
  • 3 replies
  • February 17, 2022
Andy_Lin1 wrote:

So instead of a lookup, you’ll want to use a rollup field with ARRAYUNIQUE(values). This will dedupe the list. By default, the array will be joined with (what looks like ) , , but we want something unique for the next step, so we’ll use ARRAYJOIN with an emoji, such as 🧾. This gives the final formula of:
ARRAYJOIN(ARRAYUNIQUE(values),"🧾")

Once we have that field set up, we’re going to use a formula field and two Excel tricks.
Excel trick #1: To count the number of items in a list, count the commas (instead of the items), and then add one (to account for the first item that doesn’t have a comma before it). In this case, we’re going to be using the emoji as a separator instead of a comma. There are other methods, but this works within the limitations of arrays and formulas in Airtable.

Excel trick #2: To count the instances of a character within a single string, substitute that character with nothing and comparing the length of the new string against the original.

Here’s what that looks like:

IF(
	{Unique Quiz Dates},
	1 +
	LEN({Unique Quiz Dates})
	- LEN(
		SUBSTITUTE({Unique Quiz Dates}, "🧾", "")
	),
	0
)

Here’s a base demonstrating the rollup and formula fields:


Wow @Andy_Lin1 thank you so much for your help and the example base, that did it! And I love the Excel counting tricks.

I’ve still got some timezone debugging to do, but thrilled to have your help in solving this.

Reply


Most helpful members this week

Terms & Conditions