Delete Fields in a Table

[Nathan_Feemster]

I’m trying out the new scripting block to try and make a utility for our base that will delete records based on a field and then delete the field itself. Is this possible?

Use case:

1. We use a table for semester data and then link it to all of the management tables. Each semester we make a new table and have to archive and update the linked field in all of the management tables. Semester data overlaps, so we can’t use the same table.

2. We audit our fields and sometimes find that we no longer use the field and we want to archive all old records that used that field.

Also, is it possible to export the filtered results to a CSV from a script? I would love to have this as part of the flow.

I’m a beginner at code but, here is my attempt from looking at a bunch of examples:

//Script to delete all records associated with a specific field and then delete the field.
//6-3-2020

output.markdown('# Batch Delete Records and Field');
output.markdown('#### Before use, download CSV of sorted records');

// get the table and field
let sourceTable = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;
let sourceField = await input.fieldAsync("Pick the field to sort by:", sourceTable.id);
let sourceFieldName = sourceField.name;

// get records and sort for field
let query   = await sourceTable.selectRecordsAsync();
let filteredRecords = query.records.filter( record => {
    let recsToArchive = record.getCellValue(sourceFieldName);
    return recsToArchive !== null
});

let recordCount = filteredRecords.length;

// show list of records to archive
output.markdown("### Selected Records for Deletion");
output.table(filteredRecords);

// Ask to delete Records
let deleteRecs = await input.buttonsAsync('Delete ' + recordCount +  ' records?', [
    {label: 'Yes', variant: 'danger'},
    {label: 'Cancel'},
]);

// Delete records
if (deleteRecs === 'Yes'){
    output.markdown('### Deleteing files...');
              await sourceTable.deleteRecordsAsync(filteredRecords);
              output.markdown(recordCount + " records deleted");
}

// Ask to delete Field
let deleteFields = await    input.buttonsAsync( "Delete " + sourceField.name + "?", [
    {label: 'Yes', variant: 'danger'},
    {label: 'Cancel'},
]);

// Delete Field
if (deleteFields === 'Yes'){
    output.markdown('### Deleting Field...');
    ;
}

[kuovonne · Accepted solution]

You can delete records that have a value for a given field. It looks like you are on the right track there.

You cannot export from scripting block directly to a csv file. As far as I can tell, scripting block does not have access to the local file system. You can produce csv results and then sent them to a 3rd party service which will then save it, or you can copy/paste from the scripting block output screen. If you only do this to one table per semester, it may be easier to export the records manually from the grid view before running the script.

You cannot delete the field itself. That requires a change in the table structure, and scripting block cannot do that now.

[Bill_French]

Nathan_Feemster:

… is it possible to export the filtered results to a CSV from a script?

Sure, generate the CSV file in the block and push it into a Table. Now you can download it, copy it, etc.

[Nathan_Feemster]

Thank you! I wasn’t sure if the table structure was editable from the script block. This answers my question.

[Nathan_Feemster]

Thank you! @Bill.French. My ideal script would quickly sort by a field, download those records as a CSV, delete them from the table, and then delete the field itself. From @kuovonne 's answer though, I don’t think this is possible in the way I’m thinking about it.

[Bill_French]

Nathan_Feemster:

and then delete the field itself

Correct, deleting fields is not in the cards, but this seems like an odd requirement. I understand the legacy box canyon you find yourself in, but from experience, the need to delete fields is a sign that your data architecture has flaws that are forcing you to follow this pathway.

[kuovonne]

Nathan_Feemster:

My ideal script would quickly sort by a field, download those records as a CSV, delete them from the table, and then delete the field itself.

How long does this process currently take you? It is hard to tell from your description if you need to do this to only one field per semester, or if you have to do this to hundreds of fields at a time.

If you have to do this to hundreds of different fields per semester, there might be a different workflow where script automation can help.

Bill.French:

the need to delete fields is a sign that your data architecture has flaws that are forcing you to follow this pathway.

I agree with Bill that needing to delete fields is a sign that a different base design could be helpful. Are you deleting records in order to stay within the record limits of the free plan, or for some other reason?

Since you are able to delete fields, you have creator permissions on the base and Airtable itself will allow a base redesign. Of course, you would also need to make sure that you also have permission from everyone else involved, which could be a difficult process.