Hi everyone,
our base designers are constantly challenged to keep track of their bases: Which columns are used for what, which are just temporary, can some be cleaned up, should any not be renamed because some API calls rely on them?
Using the “Column Description” is not only (in my opinion) reserved for the end user, but also does not provide a good overview. Therefore, I have created the following script, which compiles some relevant meta data into an extra table in the same base. You can use additional columns to annotate, mark, sort etc. Re-run to merge changes in the base design. More important info in the source code.
Forgive my not-so-good Javascript skills, and the fact that this hasn’t been tested much. If you like it, use it. If you improve or correct it (and / or create a Python version), post your version here.
const tables = base.getTables();
// IMPORTANT preparations BEFORE you run this Script
// 1. Create am empty table to hold the data, name it like this constant OR change the constant
const dname = 'Tabledoc';
const dtable = base.getTableByName(dname);
// 2. Create the following columns in any order in that table, name them like these constants OR change the constants
const column_name = 'Column Name' // Text - visible Column name
const table_name = 'Table Name' // Text - visible Column name
const tid = 'tid' // Text - internal Table ID
const cid = 'cid' // Text - internal Column ID
const type = 'Type' // Single select - internal column type as given by Seatable
const column_description = 'Column Description' // Text - visible column description
const valid = 'Valid' // Checkbox - Marker, unchecked = columns has been deleted since last run
const table_order = 'Table Order' // Number - Tab / Table order for nice sorting
const column_order = 'Column Order' // Number - Column order in tables for nice sorting
// 3. Create a view, name it like this constant OR change the constant
const view_name = 'DO NOT CHANGE VIEW DISPLAY';
const drows = base.getRows(dname, view_name);
// 4. Protect all those columns from above so nobody edits them - only the script will fill them
// 5. Make sure that view %view_name% alsways shows all records and all the columns above
// 6. Add columns and views to help you stay organized
// 7. Re-Run the script from time to time - your additional columns should stay untouched
const dmap = new Map();
drows.forEach(function(row){
if( row[tid] && row[cid] ){
dmap.set(row[tid] + "-" + row[cid], row._id);
}
});
let tcounter = 1;
tables.forEach(function(table){
const tname = table.name;
const table_id = table._id;
let counter = 1;
if(dname != tname){ // Do not include this table
output.text('Analyzing Table / ID: ' + tname + ' / ' + table_id);
table.columns.forEach(function(column){
const description = (column.description) ? column.description : "";
if(dmap.get(table_id + '-' + column.key)){ // Existing - Update
base.modifyRow(dtable, dmap.get(table_id + "-" + column.key),
{[table_name]: tname,
[column_name]: column.name,
[type]: column.type,
[column_description]: description,
[valid]: true,
[table_order]: tcounter,
[column_order]: counter});
dmap.delete(table_id + '-' + column.key);
} else { // New - Create
base.addRow(dtable,
{[tid]: table_id,
[cid]: column.key,
[table_name]: tname,
[column_name]: column.name,
[type]: column.type,
[column_description]: description,
[valid]: true,
[table_order]: tcounter,
[column_order]: counter});
}
counter = counter + 1;
});
}
tcounter = tcounter + 1;
});
// Deleted - Mark
dmap.forEach(function(row){
base.modifyRow(dtable, row, {[valid]: false});
});
