Help

Re: Script Request: Delete records from Tables

5618 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Dimitri_Zakharo
6 - Interface Innovator
6 - Interface Innovator

Hi. I have no scripting experience so hoping someone can help. Is there a way to use the scripting block to delete all records from certain tables? I can do this manually, but I have to do this from 8 tables out of 30 in a base that I duplicate regularly. If there was just one button I can click to do this at once, that would be amazing. Bonus if it first asked to confirm that I really want to do this, so someone doesn’t accidentally click it.

20 Replies 20
Peter_Versaci
4 - Data Explorer
4 - Data Explorer

@Jeremy_Oglesby Great work used part of this for my solution. But got stuck trying to filter records based on cell data. how would you filter records before deleting them.

Hi @Peter_Versaci – glad it was helpful!

I answered a similar question for someone else recently. See here:

Russell_Bishop
4 - Data Explorer
4 - Data Explorer

This was really helpful @Jeremy_Oglesby - thanks!

I was going to merge this into another script that populates a table, but as you’ve abstracted it so nicely it feels more sensible (and useful) to keep it separate. Cheers!

Peter_Borg
6 - Interface Innovator
6 - Interface Innovator

Hi @Jeremy_Oglesby I think I understand your code to delete records, but I’m obviously missing something fundamental. My script does not recognise the text “batchAnd”. Does this need to be predefined somewhere? Or am I missing something?

Here’s my code followed by the error message. I’m trying to delete all the records in mediatable that are linked to a particular film, where the name of the link column in film table is “Media”.

let existing = await film.getCellValue("Media");
let recordsToDelete = existing.map(record => record.id);
let recordsDeleted = await batchAnd('Delete', mediatable, recordsToDelete);

Screen Shot 2020-06-13 at 5.13.37 pm

Hi @Peter_Borg,

batchAnd() is a convenience function I wrote for performing record creation, updates, or deletes on more than 50 records at a time.

In order to call it from your script, you have to copy and paste the entire function into your script, because it doesn’t exist in Airtable’s Scripting Block API.

You’ll find the entire async function near the bottom of my script above, but here is a link to where I’ve posted it on its own in the forums, along with an explanation — just copy and paste the entire function into your script (I usually place it at the very bottom):

Scripting Block: A (hopefully) Helpful Batching Function

Peter_Borg
6 - Interface Innovator
6 - Interface Innovator

Thanks @Jeremy_Oglesby, all makes sense now.

Caroline_Riteno
6 - Interface Innovator
6 - Interface Innovator

Hi! I’m new to scripting. I’ve played around and searched around for about a half hour; but i’d like to add the option to choose a view before deleting. Could you help a gal out? I’m referring to your top script :slightly_smiling_face:

Here’s the script I put together to solve my problem:

let table = base.getTable("Tasks");

let view = table.getView(“ :curly_loop: Closed”);
let query = await view.selectRecordsAsync();
let records = query.records;
{

let recordsToDelete = records.map(record => record.id);
let recordsDeleted = await batchAnd('Delete', table, recordsToDelete);

};
// ********************
async function batchAnd(action, table, records) {
let recordsActedOn = records.length;
switch (action) {
case ‘Update’:
while (records.length > 0) {
await table.updateRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;

  case 'Create':
      while (records.length > 0) {
          await table.createRecordsAsync(records.slice(0, 50));
          records = records.slice(50);
      };
      break;
  case 'Delete':
      while (records.length > 0) {
          await table.deleteRecordsAsync(records.slice(0, 50));
          records = records.slice(50);
      }
      break;
  default:
      output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
      recordsActedOn = null;

}
return recordsActedOn;
}

Hi Jeremy! I am working on importing and managing data of device details on the cloud with API. I am a beginner and don’t have experience with coding. I was wondering if you can help me because my team is struggling with the data management of our clients while waiting on developing new tools (released in 5 weeks). Old tool is broken at the moment…

We have airtable that can download (to airtable) and upload (to the server) info for the devices (4 fields) on airtable sheet. Using a script for that. I also saw this script of yours that can delete all data from airtable sheet, but is it possible to use this script with the modification that deletes corresponding data on the server using api?

For example:

  1. I download a list of devices with info on 4 fields. (name, code, mac address, NFC number)
  2. Copy selected devices or rows in airtable (not all, just ones that I want to delete) to the new airtable sheet.
  3. Click the button in the new sheet to use scripts to delete all corresponding devices for the server using api.

I would really appreciate your help or forward me to the place where I can find similar scripts. Thank you.

EDIT:

So far I have this script for updating records but it asks me to select a specific record or row in the table, I would like to not ask me, just replace/update all records in the table to server - checking them and update them or delete them.

 let auth_headers = new Headers();
auth_headers.append('Accept',"application/json, text/plain, */*");
auth_headers.append('Content-Type',"application/json, text/plain, */*");

let UpdateYesNo = await input.buttonsAsync('Are you sure you want to update this record?', [
    {label: 'Yes', variant: 'primary'},
    {label: 'Cancel'},
]);

if (UpdateYesNo === 'Yes'){
    let tableId = cursor.activeTableId;
    let table = base.getTable(tableId);
    let record = await input.recordAsync('', table);
    output.clear()
    if (table.name === 'LOCATIONS-DOWNLOAD'){
        await updateLocation(record);
    }else if(table.name === 'LOCATORS-DOWNLOAD'){
        await updateLocator(record);
    }else if(table.name === 'TAGS-DOWNLOAD'){
        await updateTag(record);
    }else if(table.name === 'ASSETS-DOWNLOAD'){
        await updateAsset(record);
    };
}else{
    output.markdown('_Canceled_')
}



// _________________________________________________________________________________________
// FUNCTION FOR LOGIN TO THE WEBSITE
async function login(){  
    output.markdown('Please wait...')
    let table = base.getTable('CREDENTIALS');
    let records = await table.selectRecordsAsync();
    let record = records.getRecord(records.recordIds[0]);
    let username = record.getCellValue("Username");   
    let password = record.getCellValue("Password");
    let login_url = 'https://api.servername.com/login';
    let creds = {"username": username, "password": password};
    let apiResponse = await fetch(login_url, {method:"POST", body:JSON.stringify(creds)});
    let user_data = await apiResponse.json();
    if (apiResponse.status === 200){
        let token = user_data['Token'];
        auth_headers.append('token',token); 
        return true        
    }else{
        output.markdown(`* ${user_data['message']}*`);
        return false
    }
}


async function updateLocation(record){
    let login_status = await login()
    if (login_status){
        let url = "https://api.servername2.com/location/"
        let location_type
        let payload = {}
        if (record.getCellValue('Location Type').name === 'Active'){location_type = 0}else{location_type =1}
        payload['LocationId'] = record.getCellValue('ID');
        payload['LocationName'] = record.getCellValue('Location Name')
        payload['LocationCode'] = record.getCellValue('Location Code')
        payload['LocationType'] = location_type
        let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers, body: JSON.stringify(payload)});
        let responsText = await apiResponse.text()
        let feedback =  responsText.replace(/["':{}]/g, "").replace('message','');
        if (feedback === 'OK'){
            output.clear()
            output.markdown('Record updated successfuly.')
        }else{
            output.markdown(feedback)
        }
    }
};

async function updateLocator(record){
    let login_status = await login()
    if (login_status){
        let url = "https://api.servername2.com/locator/" 
        let payload = {}
        let locator_type
        if (record.getCellValue('Type').name === 'Wi-Fi'){locator_type = 3}else if(record.getCellValue('Type').name === 'Mobile'){locator_type =1}else{locator_type=2} 
        payload['LocatorName'] = record.getCellValue('Locator Name');
        payload['LocatorId'] = record.getCellValue('MAC')
        payload['LocatorType'] = locator_type
        payload['LocatorSerialNumber'] = record.getCellValue('Serial Number')
        payload['LocatorImsi'] = record.getCellValue('Locator IMSI')       
        let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers, body: JSON.stringify(payload)});
        let responsText = await apiResponse.text()
        let feedback =  responsText.replace(/["':{}]/g, "").replace('message','');
        if (feedback === 'OK'){
            output.clear()
            output.markdown('Record deleted successfuly.')
        }else{
            output.markdown(feedback)
        }
    }
};

async function updateTag(record){
    let login_status = await login()
    if (login_status){
        let url = "https://api.servername2.com/tag/" 
        let tag_type
        if (record.getCellValue('Type').name === 'Mobile'){tag_type = 1}else {tag_type=2}                    
        let payload =  {"TagId": record.getCellValue('MAC'), "NFCId": record.getCellValue('NFC'), "TagType": tag_type};
        let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers});
        let responsText = await apiResponse.text()
        let feedback =  responsText.replace(/["':{}]/g, "").replace('message','');
        if (feedback === 'OK'){
            output.clear()
            output.markdown('Record deleted successfuly.')
        }else{
            output.markdown(feedback)
        }
    }
};

async function updateAsset(record){
    let login_status = await login()
    if (login_status){
        let url = "https://api.servername2.com/asset/" + record.getCellValue('Asset ID')
        let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers});
        let responsText = await apiResponse.text()
        let feedback =  responsText.replace(/["':{}]/g, "").replace('message','');
        if (feedback === 'OK'){
            output.clear()
            output.markdown('Record deleted successfuly.')
        }else{
            output.markdown(feedback)
        }
    }
};

Hello years later! Is there any way to run this script through Zapier like on a schedule?