Dynamic link formulas

[mmoossen]

Hi,

actually I have a quite simple use case.
I have a table person with name, date-of-birth, age (formula)
And i have a table “age group”, with a name and a min age. for instance:
senior 60
adult 18
teen 14
child 0
and I want a link formula in person to automatically connect the person with his current age group.
this can be done very easily in spreadsheets with the function XLOOKUP.

From here I got now some idea how to implement that with scripts:

Can a filter in Link Formula / formula use a value from the current record (line)?

Hi, a question that drives me mad, but possibly I’m just not seeing the obvious. Assume this: I have 2 tables, linked togethe* r: A => B Both tables contain a number column (“Number A”, “Number B”) Let’s say that one line in A is linked to 20 record in B Now I want to filter those links in A to create a sum, based on the value in “Number B”: Using a constant value (let’s use 3) is no problem: I set a filter in a Link Formula column in A with the filter criterium “Number B > 3”. Or, as a f…

or does any body have any idea how to achieve this without scripts?
this should not be so difficult.

many thanks
Michael

[cdb]

Please use this script and then post the output here.

Then explain what you already tried to achieve your goal.

[mmoossen]

it looks like my description is not very clear.

Here I go again:
Table Person:

Table Age Group:

And what I need is to automatically link the persons with their age group.

[cdb]

I see two options:

Formula and button (manual)

Add a new formula column to your “Table Person” table and check:

• if smaller 14 → Child
• if smaller 18 → Teen
• if smaller 60 → Adult
• else → Senior
  Then you should have the same value in this column like the name in “Table Age Group”.

Now you can add a button and use the function “add link”.

Formula and automation

The same logic is possible with an automation instead of a button but this would require an enterprise subscription.

[mmoossen]

Hi, many thanks for the ideas.
but both means to hard-code the values in the formula.
I want something that takes the values of the age group table.
In this simplified use case it may be even feasible to hard code the values in the formula.
In my real-life use case, that is not the case.

Any further idea?

Many thanks
Michael

[cdb]

Then I would suggest that you create a python script that runs via automation every time a specific status is reached.

This requires that you upgrade to SeaTable Enterprise.

[bennhatton]

Hi Michael,

Here is a python script that should do the trick (don’t forget to adapt the tables’ and columns’ names even if I based the script on your screenshots):

from seatable_api import Base, context
base = Base(context.api_token, context.server_url)
base.auth()

# Members table's name and columns' names
members_table_name = 'Person'
members_age_group_column_name = 'Age Group'
members_age_column_name = 'Age'

# Age group table's name and columns' names
age_groups_table_name = 'Age group'
age_groups_min_limit_column_name = 'Min Age'
  
age_groups = base.list_rows(age_groups_table_name)
members = base.list_rows(members_table_name)
members_age_group = base.get_column_by_name(members_table_name, members_age_group_column_name);
source_ids = []
target_ids = {}
for member in members :
    for age_limit in age_groups :
        if member[members_age_column_name]>=age_limit[age_groups_min_limit_column_name] and len(member[members_age_group_column_name])==0 :
            print(member['Name'] + " : " + age_limit['Name'])
            member[members_age_group_column_name].append(age_limit['_id'])
            source_ids.append(member['_id'])
            target_ids[member['_id']] = [age_limit['_id']]
base.batch_update_links(members_age_group['data']['link_id'], members_table_name, age_groups_table_name, source_ids, target_ids);

As Christoph said, you can only automate the launching of the script with an automation, which needs the SeaTable Enterprise subscription, but for now you can just launch the script when you need.

I thought of something not using a script for what you need, but couldn’t find anything for now… If I figure it out, I’ll let you know.

Bests,
Benjamin

[mmoossen]

Hi,
many thanks for the answers and in special the script.
Since the values in the referred table are quite stable,
this is indeed a good solution.
Many thanks
Michael