Help

Create a script to show all views in all tables in a base

1503 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Maria
6 - Interface Innovator
6 - Interface Innovator

Hi I am using the metadata script to show fields and tables in a base. I would like to create one for all the views in a base. Does anyone know how to adapt the script or have something I could use please?

Thanks, Maria

6 Replies 6

I don’t know about the script, but On2Air: Schemas is an app that shows you all of that and more:

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**");

Thank you Adam!!! - Amazing just what I want :grinning: . Do you know how to add view description in to the script? I also have the table metadata set up and have been trying to add table description to that but no luck so far!

Thanks Scott for your reply. I have looked at that software but don’t have budget for extra adds on.

Hi Maria, glad it helped! I’ve updated the script to get the table description as well.

I tried to figure out how to get the view description, and I think we’re actually not able to do that via scripting or the API at this point. Could you confirm that with Airtable support perhaps?

Happy to modify my script to add that functionality, just need to know how heh

Hi Adam, thank you for that.  It saved me a lot of time. I am running into a stumbling block.  I would like to have a script that goes through every view checks if a field is visible within that view and maintains a table.  Is that something you might be able to assist me with?