Hi Maria, I’ve put something together for you here that should do what you’re looking for.
I’ve attached the script below as well
const settings = input.config({
title: "Get Base Views",
description:
"",
items: [
input.config.table("metadata_table", {
label: "Metadata table",
description: "Table to write info into",
}),
input.config.field("table_id_field", {
parentTable: "metadata_table",
label: "Table ID Field",
description: "Field to write the table id into",
}),
input.config.field("table_name_field", {
parentTable: "metadata_table",
label: "Table Name Field",
description: "Field to write the table name into",
}),
input.config.field("view_id_field", {
parentTable: "metadata_table",
label: "View ID Field",
description: "Field to write the view id into",
}),
input.config.field("view_name_field", {
parentTable: "metadata_table",
label: "View Name Field",
description: "Field to write the view name into",
}),
],
});
// we can create a unique "key" for each field in the base which is the combination of table and view id
// this let's us search the existing table to see if these records exist and if they do, update the existing entry
// otherwise, create a new one
function buildRecordKey(table_id, view_id) {
return `${table_id}-${view_id}`;
}
// load records out of the table and build a map
// of existing records using their key
const results = await settings.metadata_table.selectRecordsAsync();
var existing_map = {};
for (let r of results.records) {
let key = buildRecordKey(
r.getCellValue(settings.table_id_field),
r.getCellValue(settings.view_id_field)
);
existing_map[key] = r.id;
}
// filter out the metadata table from our search
const tables = base.tables.filter((t) => {
return t.id !== settings.metadata_table.id;
});
// create two arrays to hold records to update versus records to create
var updates = [];
var creates = [];
// iterate through each table
for (let t of tables) {
let views = t.views;
for (let v of views) {
let key = buildRecordKey(t.id, v.id);
let payload = {
fields: {
[settings.table_id_field.name]: t.id,
[settings.table_name_field.name]: t.name,
[settings.view_id_field.name]: v.id,
[settings.view_name_field.name]: v.name,
},
};
if (existing_map[key] !== undefined) {
let existing_id = existing_map[key];
updates.push({
id: existing_id,
...payload
});
} else {
creates.push(payload);
}
}
}
// write data
output.markdown(`Updating ${updates.length} records`);
while (updates.length > 0) {
await settings.metadata_table.updateRecordsAsync(updates.slice(0, 50));
updates = updates.slice(50);
}
output.markdown(`Creating ${creates.length} records`);
while (creates.length > 0) {
await settings.metadata_table.createRecordsAsync(creates.slice(0, 50));
creates = creates.slice(50);
}
output.markdown("**Done**");
