How count unique values across multiple relational levels?

User Talk
1

Hello,

I am facing a limitation in SeaTable Cloud when trying to count unique values across multiple relational levels.

Simplified database structure:

  • Dates

  • Parcels

  • Owners

Relations:

  • One Date is linked to multiple Parcels

  • Each Parcel is linked to an Owner

  • In the Dates table, I use a Lookup to display owners via parcels

Goal:

For each Date, I want to count the number of distinct owners linked to that date.

Question:

Is there any native solution (formula, advanced rollup, automation without adding a new table, or another approach) to count unique values across multiple relational levels ?

Here some screenshots :

Parcels table

image
image687×180 10 KB

Dates Table with lookup formulas :

image
image1087×184 16.2 KB

Thank you in advance for your help.

2

Hi @fleprince,
Here are the two solutions I found for your issue: I definitely prefer the second one, but it as some strict restrictions… I agree anyway that, whereas the lookup function allows for one nested link level, the rollup function doesn’t which is a pity in your case.

  1. You can create a simple Python script that you’ll be able to run from an automation or a button. See this example :
from seatable_api import Base, context

base = Base(context.api_token,context.server_url)
base.auth()

DISTINCT_OWNERS_COL = 'Adhésion - Propriétaires'
RESULT_COL = 'Adhésion - Nb propriétaires'

row = context.current_row

base.update_row(context.current_table, row['_id'],{RESULT_COL: len(row.get(DISTINCT_OWNERS_COL).split(', '))})

In this script, I defined two variables for both needed columns:

  • DISTINCT_OWNERS_COL the column that contains the list of distinct owners (Link formula/Lookup with the “do not show duplicates” option checked) (in my case, the column is named Adhésion - Propriétaires)
  • RESULT_COL the column where I want to write the result (number-type column, in my case Adhésion - Nb propriétaires)
    Each time you run the script for a specific line, it will count the number of commas ‘,’ in the list of the distinct owners
  1. If, as it’s the case on your screenshot, every owner has an identifier (N°fonctier in your case) with the exact same number of characters, it’s even easier:
  • there are 7 characters in every N°foncier shown (1 letter + 1 space + 5 numbers), so each item in the list will be 9 characters (7 characters from the N°foncier + 1 comma + 1 space), except the last one because it is not followed by a comma and a space
  • then, you can use a simple formula by adding the two “missing characters” to be sure that every item (even the last one) is exactly 9 characters. The number of distinct owners is then (len({Adhésion - Propriétaires})+2)/9

Hope this helps!

Bests,
Benjamin