Forum Discussion

Qrew Member

4 years ago

Delete records while adding the records via import from CSV

Hi I am importing some date from csv through import from file functionality. while adding the records in this attached step, is there any functionality which i can implement in such a way...i shoul...

Data import and export

MarkShnier__You

Qrew Champion

One approach that I just thought of would be to import the CSV Into a temporary table in your app and then push a button to run a save table to table import from the temporary table into your real table. The save table to table import would have filters which would allow you to control which records past the final hurdle to get into your real data.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Qrew Trainee

4 years ago

Hey Mark,
I've had varying degrees of success with this temporary table approach, usually less so when dealing with larger data sets. I've been using automations to try and mark records back and forth so that reactions can drive whether records are copied over.
For example (each step is a separate automation):
Step 1: data drops into a temp table (this step is a Pipeline/MS Flow step)
Step 2: the target table reacts to this and marks any "legacy records" already living in the target table by using a formula field, that concatenates 3 other fields and comparing to a field in the target's key. The target table uses this concatenated field as it's key.
Step 3: the temp table will then react and mark records that match in the target table, then delete itself. (This step fails on data sets larger than 1000)
Step 4: run a table to table import from temp to target on a schedule after all of those records have finished marking and deleting themselves. These existing records should be unique. (This step fails on data sets larger than 1000)
Step 5: Uncheck all Legacy checkboxes in the target table on a schedule. (This step fails on data sets larger than 1000)

Maybe I'm making this too complicated, but since this works on data sets smaller than 1000, it feels like I'm missing a simple way to make it all work. In reading your response, I'd love to understand how to "push a button to run a save table to table import from the temporary table into your real table". Since my attempt at automating this task is not working well, maybe a manual approach with actual working steps like yours would be a better place for me to work from.

Any help is greatly appreciated!

Marc

------------------------------
Marc Barry
------------------------------

MarkShnier__You
Qrew Champion
4 years ago
If are simply asking how to create a formula URL button which will run a saved table to table copy and refresh the page you were on it would be

var text URL = URLRoot() & "db/" & [_DBID of the target table from Advanced Properties] & "?act=API_RunImport&ID=10;

"javascript:" &
"$.get('" &
$URL &
"',function(){" &
"location.reload(true);" &
"});"
& "void(0);"

n the code above you need to enter the ID for the saved T2T import. In my case it was 10. You see that in the URL when you edit the saved T2T import.

Typically I make a table called like Admin Import and make a single record, which will be [Record ID#]= 1.

Then I can use that to hold my button and to make relationships to count, for example how many records on the scratchpad and to put buttons to lead the user to the Import /Export page.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
- MichaelTamoush
  Qrew Captain
  4 years ago
  Woah. So your code above runs a table to table import?! I've been using code to check a box, and have that trigger an automation to run the import. This is so much better.
  
  ------------------------------
  Mike Tamoush
  ------------------------------
  - AustinK
    Qrew Commander
    4 years ago
    You can do the same thing with webhooks and Pipelines too but you are then limited to refreshing every hour at maximum. Better than an automation which is only daily, or triggered in some other way like you did with the checkbox. I used the Pipelines method to trigger multiple T2T imports that need to happen at once, it works pretty well.
- MichaelTamoush
  Qrew Captain
  4 years ago
  Mark,
  
  I tried this code to run a T2T import without success. I found one missing quotation mark after the '10' in the variable text, and the URL button seems to think and then refresh the page, but the import does not run. I went in to edit the import and my number is actually 10 as well. Is there any other common errors? Do I need to add an app token to run a T2T?
  
  ------------------------------
  Mike Tamoush
  ------------------------------
  - MarkShnier__You
    Qrew Champion
    4 years ago
    If you have the need for app tokens enabled in advanced settings for the App, then for sure you need one as you need an App token for any update API (Add Edit Delete).
    
    That javascript code snippet hides the error message. Guessing that you need an app token does not fix the problem then the generic way to debug these is to do this.
    
    var text URL = URLRoot() & "db/" & [_DBID of the target table from Advanced Properties] & "?act=API_RunImport&ID=10;
    
    $URL
    
    //"javascript:" &
    //"$.get('" &
    //$URL &
    //"',function(){" &
    //"location.reload(true);" &
    //"});"
    //& "void(0);"
    
    So add in $URL and comment out the javascript)
    
    That will run the URL and any messages of success or failure will blurt out on the screen. You get a blurb that says no error then you go back to the original code.
    
    If you have the need for application tokens enabled, then you need to do this.var text URL = URLRoot() & "db/" & [_DBID of the target table from Advanced Properties] & "?act=API_RunImport&ID=10
    
    & "&apptoken=cxcxcxcxcxcxcxxcxcxcx";
    
    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------