Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 28, 2021 09:12 AM
Hi,
I’m wondering how to count all the unique values in a field/column? In my simplified example below, I’ve filtered for orders shipped in the spring, and I want to know how many unique fruits are in the linked field, “Content (from Fruit table),” so that I know how many unique types of fruit I shipped all spring.
Thanks for any help you can give.
May 28, 2021 11:03 AM
So if I am understanding your needs correctly, you have a linked record field in your Orders table that is called Content (from Fruit table).
And what you need to know is, “for all the records in the Orders table during a certain period (e.g. Spring), how many unique Fruits were in the Content (from Fruit table)
field?”
Assuming that is what you need, first, make another table, and make a linked records field in that table that points to the Orders table. Link all the Orders records from the time period you’re interested in to a single record in that new table.
Then, you need to make two rollup fields, one in your Orders table, and one in the new table.
The one in your Orders table should roll up the title field values from the Content (from Fruit table)
field, and just have the word values
as the formula.
The one in your new table should roll up the field values from that new rollup field in the Orders table, and use ARRAYUNIQUE(values)
as the formula.
This should result in what you need.
Jun 01, 2021 02:04 PM
Thanks Katerie,
You’ve accurately restated the problem I’m trying to solve.
I created another table (called “Rollup Table”) with just one record and a linked records field (called “Link to Orders”) pointing to the Orders table. I’m stumbling on your instruction to “Link all the Orders records from the time period you’re interested in to a single record in that new table” (and apologies if I’m being dense).
If I click the :heavy_plus_sign: in the Link to Orders field of the table’s only record, I’m presented with all the records in the Orders table. How do I select all the Orders records that I need—other than manually? (Which isn’t practical because there are hundreds that I need.)
Thanks
Jun 02, 2021 09:58 AM
Instead of linking all the orders from the Rollup Table, do it from the Orders table instead.
Make a Grid View in the Orders table that is filtered to shows the orders you need to get the information about (e.g. all the ones for the springtime). Configure that View so that one of the columns shown is the linked record field that points to your Rollup Table. Link the first record in your new Grid View to the appropriate record in the Rollup Table. Then copy the value of that linked record field and paste it into the same cell for all the other records in your View. This should give you what you need.
Jun 06, 2021 01:37 PM
Hi Katerie,
There’s still something that I’m missing, so I’ve paired your instructions with the result that I got below. in my test database Thanks for your patience w/ me.
Make a Grid View in the Orders table that is filtered to shows the orders you need to get the information about (e.g. all the ones for the springtime). Configure that View so that one of the columns shown is the linked record field that points to your Rollup Table.
Order table
New rollup table
Link the first record in your new Grid View to the appropriate record in the Rollup Table. [I wasn’t sure what you meant by “link the first record.” Seems like you can only link tables?]
Then copy the value of that linked record field and paste it into the same cell for all the other records in your View.
Then, you need to make two rollup fields , one in your Orders table, and one in the new table.
The one in your Orders table should roll up the title field values from the Content (from Fruit table)
field, and just have the word values
as the formula.
The one in your new table should roll up the field values from that new rollup field in the Orders table, and use ARRAYUNIQUE(values)
as the formula.