I have a table that has multiple lookup fields to another table. I need to be able to sum the number of unique values in each lookup but keep failing to get it to work.
Example is I have a lookup called “Promotions” and then I want the next column to says # of Unique Promotions.
Please help!
+17Welcome back to the Airtable community forums!
You can use a rollup with the ARRAYUNIQUE(values) to return the number of unique values. Encase that value in the ARRAYJOIN() function to specify a unique character that will never appear in any of the values.
ARRAYJOIN(ARRAYUNIQUE(values), ", ")
Then use a formula field to calculate the number of values in the rollup, based on the unique character that you picked for the rollup.
IF({Rollup Field},
LEN({Rollup Field}) - LEN(SUBSTITUTE({Rollup Field}, ",", "")) + 1
)
Welcome back to the Airtable community forums!
You can use a rollup with the ARRAYUNIQUE(values) to return the number of unique values. Encase that value in the ARRAYJOIN() function to specify a unique character that will never appear in any of the values.
ARRAYJOIN(ARRAYUNIQUE(values), ", ")
Then use a formula field to calculate the number of values in the rollup, based on the unique character that you picked for the rollup.
IF({Rollup Field},
LEN({Rollup Field}) - LEN(SUBSTITUTE({Rollup Field}, ",", "")) + 1
)
I tried that and I am getting somewhere on some columns but columns that have much longer text and various unique characters for each lookup entry the resulting count formula doesn’t display accurate numbers
+17I tried that and I am getting somewhere on some columns but columns that have much longer text and various unique characters for each lookup entry the resulting count formula doesn’t display accurate numbers
Are you including a unique character in the ARRAYJOIN that does not exist in any of the lookup values? In my example, I include a comma because that is a common character for separating items in a list. However, if any of your lookup values will contain a comma, the system will not work. Also, if any of your lookups have blank values, that will also cause problems. In that case you should make the rollup a conditional rollup to include only records that have a value to lookup.
If this information isn’t enough, can you please include screen shots with your sample data and formulas?
Are you including a unique character in the ARRAYJOIN that does not exist in any of the lookup values? In my example, I include a comma because that is a common character for separating items in a list. However, if any of your lookup values will contain a comma, the system will not work. Also, if any of your lookups have blank values, that will also cause problems. In that case you should make the rollup a conditional rollup to include only records that have a value to lookup.
If this information isn’t enough, can you please include screen shots with your sample data and formulas?
I’d rather not post my data online is there a way to contact via email?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
