Tally frequency of database entries by "Category"

I have a feeling this is easier than I’m making it. But here’s the context:

  • In my database I have a list of about 100 contacts that have subscribed to my newsletter
  • They each have a column for ‘Location’ - i.e. “New York”
  • I am trying to visualize (just using text for now) the total number of each location that I have
  • If the location was predefined where they had to choose from a pre-defined list this wouldn’t be difficult
  • However, the users can input any text they want into Location, and more users are being added daily

How can I see a simple breakdown of all the locations, with the frequency each is used in the database?

I am trying to setup an option set linked to a “Locations” data type, and then run a workflow that anytime a new entry is made in the database, it adds the location the user picks to “Locations” and then “+1” to “Frequency.” But, I’m getting stuck.

Anyone have any ideas?

Thanks so much!

Why can’t you just do a search with constraints?

Do a search for Contacts (where contact’s location = “some city”):count
This will auto update everytime a new entry is added to the database

Let’s say there are 10 cities in the database that represent the 100 contacts I have. In that case, I could do a search constraint because I know the 10 cities. But, what if 10 more people sign-up from 10 different cities not already in the database? How do I have a condition setup to bring those cities into view?

Hmm, maybe I just have two side by side lists. One that is filtered down to all the cities.And then create a new one that looks at the database and returns a count of each city? Then sort the two groups by the same field so they’re always aligned?

Gotta be an easier way.

You’ll have to do it in a repeating group of type cities where cities are a data type or an option set. Every time a new user signs up and adds a new city to the list then a new search text box appears

Scratch the option set method. I was thinking countries where the max is 200 (or however many countries there are) but cities have way more so there is no way you would have a list of all the cities in the world. Only downside with the data type option is spelling differences. New york and New York would be two separate database entries unless you’re saving them using the “:lowercase” expression. This doesn’t help typos though

Hmm, I don’t think those options will work.