Unique default value in boolean (check box) column

Hi all.

I’ve been hacking around this evening and found a solution you might want to use.

During my years programming a lot of different things I sometimes faced the requirement that you should be able to set a default value in a table for only one row at a time.
For example: You have a master data table where you want to mark a row as default value but there can only be one default row. Until now SeaTable does not support this special column feature so here is one solution. (new feature? :))

But be aware: This only runs on servers with python support since you cannot run javascript with automations (maybe some time in the future?). The second thing is you have wait a little bit after you checked another row since the script has to start in the background, check the columns you defined and change the table values. So, to make this script work properly you have to wait a bit before you change it again!

What do you have to setup?

The table could look like this:

You have to add the code as python script:

from seatable_api import Base, context

# set server url
server_url = context.server_url or ''
# set base api token
api_token = context.api_token or ''
base = Base(api_token, server_url)
base.auth()

# set the row you want to be checked
columns = [ 
  { 'columnName': 'test', 'expectedValue': True, 'resetValue': False },
]
# the current table is dynamic and can be used in every table
# or change it to a specific table name
tableName = None




### don't edit anything blow this line! ###
# join all column names for select
columnSelect = ','.join([f'`{column["columnName"]}`' for column in columns])
# join all column names for where
columnWhere = [f'`{column["columnName"]}` = {column["expectedValue"]}' for column in columns]
columnWhere = ' or '.join(columnWhere)
# the current table is dynamic or change defined above
tableName = tableName or context.current_table

# build the sql and get the rows
sql = f'select _id, {columnSelect} from `{tableName}` where {columnWhere}'
rows = base.query(sql)
curRow = context.current_row

# check all rows and count the values that match expectedValue
batchRows = []
targetColumn = None
for row in rows:
    for column in columns:
        if row[column['columnName']] != column['expectedValue']:
            continue
        if 'counter' in column:
            column['counter'] += 1
        else:
            column['counter'] = 1

# get the row with more than 1 expectedValue
changedColumn = [column for column in columns if column['counter'] > 1]
changedColumn = changedColumn[0] if len(changedColumn) > 0 else None

if changedColumn == None:
    print('There is no row to reset the value for!')
    print('Did you define the column?')
    print('Did you set an automation rule for the column?')
    print('Supported column types are only boolen at the moment!')
    print('Please check!')
    exit()

# reset all values except the changed row
for row in rows:
    if row['_id'] == curRow['_id']:
        continue
    batchRows.append({
        'row_id': row['_id'],
        'row': {
            changedColumn['columnName']: changedColumn['resetValue']
        }
    })

# update the table
base.batch_update_rows(tableName, rows_data=batchRows)

Change the variable “columns” in the script and add a new row for each column you want to observe.
The script is table independent, therefore you only have to define the column name. But you can still change the variable “tableName = None” to “Default value” if want to make it available in one table.
And yes, if you have two tables with the same column name you only have to define the column once! But you have to add another automation rule of course.

# example for specific table
tableName = "Default value"

# column names
columns = [ 
  { 'columnName': 'test', 'expectedValue': True, 'resetValue': False },
  #{ 'columnName': 'test', 'expectedValue': True, 'resetValue': False },
  # ...
]

Then add an automation rule that focuses on the column “test” wich holds the default value checkbox. At the moment it only supports boolean (check box) column types. Save it!

Now, if you want to change the default row just click the box in another row, wait a few seconds and the old value should be reset to false (unchecked). This is much easier than searching for the one selected row you want to uncheck :slight_smile:

Have fun! :slight_smile:

2 Likes