Red Gregory

View Original

How I Construct Dynamic Grouping In Notion

See this content in the original post

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.

  1. {0} returns full string.

  2. {1} returns second item.

  3. {2} returns third item.

  4. {3} returns fourth item.

  5. 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.

Further Reading

See this gallery in the original post