Struggling with this one. Cannot figure it out.
Objective:
Search rows in a database inside Notion. Look at properties that contain non-numerical data (such as a select property). Count the amount of times each option within the select property shows up in the database. Return the sum as a number in another database.
Example:
Rows 1-100 all have different options selected for the “Insurance” property. I want to count hundreds of rows daily and count how many times each Insurance option shows up. So when it submits to another database it will say in the Geico column, 12, for example. And State Farm, 8. And so on and so forth.
Tried doing length(map(map as that works great for numerical, but not for text.
Any help would be awesome.
Hi Bryson,
Did you created an array with an array aggregator before doing your calculation ?
I manage to count the number of differents select options in a db by first creating an array with the bundles from the search module.
The exact setup depends if you only want to update a single line in the second database or multiple.
If you only want to update a single line (one column for each kind of select options) :
- Select properties values in the aggregator
- Use this formula in you create item module to count only specific items in your array {{length(map(2.array; “properties_value”; “properties_value.Tags.name”; “1. Lunch”))}}
Tags being the name of your select property and “1. Lunch” an option you want to count.
however, If you want to create (or update) a line per select option, then you can select the “group by” option in the aggregator (toggle the advanced options to access it) and chose the name of you select option:
{{first(map(toArray(1.properties_value.Tags); “value”; “key”; “name”))}}
(I am a bit baffled by this formula, there is probably a much simpler way)
It will gives you one bundle with an array for each of your options, and then you can just count the lenght of the array.
Hope this helps,
Cheers,
Sylvain.
2 Likes
Sylvain, this is exactly what I needed. Thank you!! I was very close. I’m still learning the syntax of this stuff.
My goal was to take multiple rows and count how many times something showed up, which wa syour first solution. I tried it and it worked immediately. Also thank you for showing the screenshots. Very helpful to see everything exactly as it’s laid out.
THANK YOU!
