I have a question regarding maintenance administration in Seatable. There is a maintenance table containing a maintenance schedule next to the device name. I want a new record to appear whenever maintenance is performed, containing the next maintenance date (according to the period in the schedule). For example: Suppose device 1 has a maintenance interval of 3 months. If the device was purchased on January 1st, maintenance must be performed for the first time on March 31st. Once this is complete, it must be marked and a new record created with the new maintenance date. I have tried some automations but not successfully. Can someone help me?
Hi @Coen,
For such cases, you’ll probably need a script… Here’s a little Python script I wrote:
from datetime import datetime
from dateutil.relativedelta import relativedelta
import datetime
from seatable_api import Base, context
from seatable_api.constants import ColumnTypes
base = Base(context.api_token, context.server_url)
base.auth()
MAINTENANCE_DATE_COLUMN = 'Next maintenance date'
MAINTENANCE_INTERVAL_COLUMN = 'Maintenance interval (months)'
MANUAL_INPUT_COLUMNS = [
ColumnTypes.NUMBER,
ColumnTypes.TEXT,
ColumnTypes.LONG_TEXT,
ColumnTypes.CHECKBOX,
ColumnTypes.DATE,
ColumnTypes.SINGLE_SELECT,
ColumnTypes.MULTIPLE_SELECT,
ColumnTypes.IMAGE,
ColumnTypes.FILE,
ColumnTypes.COLLABORATOR,
ColumnTypes.FORMULA,
ColumnTypes.URL,
ColumnTypes.EMAIL,
ColumnTypes.RATE,
]
TODAY = datetime.date.today()
tables = base.list_tables()
tables_by_id = {}
for table in tables:
tables_by_id[table['_id']] = table['name']
table = [t for t in tables if t['name'] == context.current_table][0] #base.get_table_by_name(context.current_table)
columns = base.list_columns(table['name'])
editable_columns = [c['name'] for c in columns if c['name'] != MAINTENANCE_DATE_COLUMN and c['type'] in [t.value for t in MANUAL_INPUT_COLUMNS]]
link_columns = [c for c in columns if c['name'] != MAINTENANCE_DATE_COLUMN and c['type'] == ColumnTypes.LINK.value]
row_to_copy = context.current_row
new_data = {}
for idx, (key,val) in enumerate(row_to_copy.items()):
if key[0]!= '_' and key in editable_columns:
new_data[key] = val
new_data[MAINTENANCE_DATE_COLUMN] = str(TODAY + relativedelta(months=row_to_copy.get(MAINTENANCE_INTERVAL_COLUMN)))
new_row = base.append_row(table['name'],new_data)
for col in link_columns:
linked_records = base.get_linked_records(table['_id'], col['key'], [{'row_id': row_to_copy['_id'], 'limit': 1000}])
other_table_name = tables_by_id[col['data']['other_table_id']] if tables_by_id[col['data']['other_table_id']] != table['name'] else tables_by_id[col['data']['table_id']]
base.update_link(col['data']['link_id'], table['name'], other_table_name, new_row['_id'], [r['row_id'] for r in linked_records[row_to_copy['_id']]])
To adapt it to your case, you’ll have to set the name of both maintenance date column and maintenance interval columns respectively in variables MAINTENANCE_DATE_COLUMN and MAINTENANCE_INTERVAL_COLUMN at the beginning of the script. The script is quite complex as I tried to make it as generic as possible: it actually duplicates every columns of the initial row and also recreate the links that the initial row might have.
You’ll be able to use this script either by clicking a button from a button column, or from an automation.
If you want to learn more about scripts, read the appropriate help section or have a look at the developer documentation.
Bests,
Benjamin
