📍 Atlas Database
If you have many relational databases in Notion, using a series of formulas with the grouping feature like this use-case can make your workflow quicker. Here is how I automatically group every database entry in a world Atlas table into several classifications with only the Name property.
Connect Member And Atlas Database
I use two databases. There is an Atlas database that is connected to my Family Tree database. I connect both databases with a Relation property.
The goal is to auto-group locations without using templates, select properties, or navigating to a location page at all. All properties in the Atlas database are filled automatically with formulas, particularly, using the replace() function and regular expressions.
The Location Name Syntax
For every instance a member is connected to a place like place of birth or burial location, every new location entry in the Atlas database will be auto-grouped into parent groups.
With this Name syntax, I can construct a series of formulas that will sort all places into three parent groups: City, Region, and Country. I can also identify what type of location they fall into: Place, Address, City, Region, Country.
The above is the max length and syntax of my Atlas entry names, however, there are shorter variations as well. Here are all possibilities:
Place, Address, City, Region, Country
Address, City, Region, Country
City, Region, Country
Region, Country
Country
How To Identify Location Type
The conditions for this formula require counting how many items there are in a list. Every location type is divided by a comma. To count how many items are in a list string separated by commas, you can use this formula:
length(replaceAll(prop("Name"), "[^,]", "")) + 1
Conditions for formula
Only if name is filled out,
If location has 5 items, it is a "📍 Place".
If location has 4 items, it is an "🏡 Address".
If location has 3 items, it is a "🏘 City".
If location has 2 items, it is a "🗺 Region".
If location has 1 item, it is a "🌎 Country".
Otherwise, leave blank space.
Formula
if(not empty(prop("Name")), if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 5, "📍 Place", if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 4, "🏡 Address", if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 3, "🏘 City", if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 2, "🗺 Region", if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 1, "🌎 Country", ""))))), "")
How To Group Each Location
Next, I want to group each location in its corresponding City, Region, and Country. I want to do this in order to find all locations inside a given parent location like all regions, cities, and places in England or France or the United States. Or maybe I want to narrow my search to all cities and places in Massachusetts.
There are three separate formulas called City, Region, and Country for each location. In order to isolate desired items in a list, this formula is used:
replaceAll(replace(prop("Name"), "(?:[^,]*, ){1}", ""), ",.*", "")
Replace the number between “{..}” to grab numbered item is list.
{0} returns full string.
{1} returns second item.
{2} returns third item.
{3} returns fourth item.
and so on …
Conditions For City Formula
If location has 5 items, extract 3rd item from list.
If location has 4 items, extract 2nd item from list.
Formula
if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 5, replaceAll(replace(prop("Name"), "(?:[^,]*, ){2}", ""), ",.*", ""), if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 4, replaceAll(replace(prop("Name"), "(?:[^,]*, ){1}", ""), ",.*", ""), ""))
Conditions For Region Formula
If location has 5 items, extract 4th item from list.
If location has 4 items, extract 3rd item from list.
If location has 3 items, extract 2nd item from list.
Formula
if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 5, replaceAll(replace(prop("Name"), "(?:[^,]*, ){3}", ""), ",.*", ""), if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 4, replaceAll(replace(prop("Name"), "(?:[^,]*, ){2}", ""), ",.*", ""), if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 3, replaceAll(replace(prop("Name"), "(?:[^,]*, ){1}", ""), ",.*", ""), "")))
Conditions For Country Formula
If location has 5 items, extract 5th item from list.
If location has 4 items, extract 4th item from list.
If location has 3 items, extract 3rd item from list.
If location has 2 items, extract 2nd item from list.
Formula
if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 5, replaceAll(replace(prop("Name"), "(?:[^,]*, ){4}", ""), ",.*", ""), if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 4, replaceAll(replace(prop("Name"), "(?:[^,]*, ){3}", ""), ",.*", ""), if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 3, replaceAll(replace(prop("Name"), "(?:[^,]*, ){2}", ""), ",.*", ""), if(length(replaceAll(prop("Name"), "[^,]", "")) + 1 == 2, replaceAll(replace(prop("Name"), "(?:[^,]*, ){1}", ""), ",.*", ""), ""))))
How To View Each Grouping
I have three database views to group each formula: Countries, Regions and Cities. Each view is a list with a grouping for the corresponding formula.
To sort by group in any database view, navigate to the database menu and find Group. You can group by formulas like Country to tuck all locations in each country neatly inside a toggle.
In the list view, the Type property is also shown. I wanted to sort the toggled lists from Region to Place type. I created a formula called Order to do so. This returns the number of list items, then I sorted the lowest number, or broadest location, to be seen first on the list.
Order Formula
length(replaceAll(prop("Name"), "[^,]", "")) + 1
I also have a group view that looks similar to the above group. The only difference being it is grouped by the Type property.