Deleting tables of Data through Automation?

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
Photo of Ashley Asante

Ashley Asante

  • 696 Points 500 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
What's your question?
Photo of Ashley Asante

Ashley Asante

  • 696 Points 500 badge 2x thumb
I'm sorry, I thought i had typed that in. I'm need to find a way to delete multiple tables of data easily. I bring new data in from a different platform daily, and I have to go table by table deleting data. Automations do not work as, there are over 1000 records. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Later today I can post some easy native code for s formula URL button which will purge a whole series of tables with a button click. But I am away from the computer right now so it will have to wait a few hours. Later today I can post some easy need of code which will purge a whole series of tables with the button click.

You are correct, that automations can only act on a maximum of 1000 records, and they were also way too slow so either way automations will not solve your problem.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
The API to purge all records is this one

https://help.quickbase.com/api-guide/purgerecords.html

I have an app in the Exhange call URL formuals for Dummies which explains a starting guide to making API buttons.

As per my previous post, at the present time and Automation cannot delete all the records in a table with over 1000 records, and even if you did have a table with under 1,000 records, if a user was waiting for it to process, they would be a lot older when it finally finished.  We, they would feel a lot older as its like watching paint dry.

But is easy to make a formula URL button.

var text URLONE = URLRoot() & "db/" & [_DBID_Table name one] & "?act=API_PurgeRecords";

var text URLTWO = URLRoot() & "db/" & [_DBID_Table name two] & "?act=API_PurgeRecords";

var text URLTHREE = URLRoot() & "db/" & [_DBID_Table name THREE] & "?act=API_PurgeRecords";

var text URLFOUR = urlroot() & "db/" & dbid() & "?a=td";


$URLONE 
& "&rdr=" & URLEncode($URLTWO)
& URLEncode("&rdr=" & URLEncode($URLTHREE))
& URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))

The button can be located on any of the tables to be purged, as a  for ula URL field, and if you choose to, after building the button, if you remove the link name, it will expose that code.  You can paste the into a URL button on the dashboard.

Note tat after you run an API, you need to redirect the user to land somewhere or else the system will spew back an unfriendly message on the screen saying that the last step in the purge was successful.  So my URLFOUR will land the user on the table home page of the table where the button is built.

Here is code for up to 10.
$URLONE 
& "&rdr=" & URLEncode($URLOne)
& URLEncode("&rdr=" & URLEncode($URLTwo))
& URLEncode(URLEncode("&rdr=" & URLEncode($URLThree)))
& URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFour))))
& URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFive)))))
& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSix))))))
& URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLSeven)))))))
& URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLEight))))))))
& URLENcode(URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLNine)))))))))
& URLENcode(URLENcode(URLENcode(URLENcode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLTen))))))))))


Note, I would not recommend going past 10, as the URL gets ridiculously long ands will start to exceed the limits of what the Browser can handle, especially IE has a limit which you might hit.







Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,004 Points 20k badge 2x thumb
Use script similar to this:
var apptoken = 'cer56xc7ch49v55zbh4b67fwpu';
$.ajaxSetup({data: {apptoken}});

var dbids = ['bn5qk6g8x', 'bn5qk6ynt'];

Promise.all(dbids.map(dbid => {
    Promise.resolveXXX($.get(`${dbid}?act=API_PurgeRecords`));
  })
).then(response => {
  console.log('Tables Purged');
  alert('Tables Purged');
  $.jGrowl('Tables Purged', {life: 5000, theme: 'jGrowl-green'});
  // further processing
});
Just supply your apptoken, and array of dbids. If the purging of the tables is part of a pipeline of processing you can provide appropriate feedback after the purge and continue any further processing within the then() method.

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=684

Notes:

(1) This code uses both (a) jQuery AJAX promises and (b) ES6 promises to overcome a chaining bug in jQuery ver 1.71 which QuickBase uses.

(2) This code uses (a) fat arrow functions, (b) object property shortcuts and (c) backticks. Get used to it!

(3) I have intentionally put a typo in the code (ie XXX) so you will have to intentionally remove it before running the code and automatically deleting all your table data.