How can I loop though records uploaded from a CSV file and perform an API_doQuery for each record in the loop? This is in javascript.

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Here is my scenario. I have a table of candidate rates. There may be multiple candidate rate records as rates are based on effective dates. A candidate may have a future effective date. The users manually upload time card data from a csv file. The candidate rates and time card tables are related so I need to stamp the proper candidate rates record id onto the imported time cards related record id field. The idea is this:

1. Run API_ImportFromCSV to load all the data to be uploaded
2. For each row in the imported array, run API_DoQuery to find the correct rates record and get the rec id from the rates record
3. Build the row to be written to the time card table, sending the record id from the rates table to the related record id column on the time card table (this part not yet written in the code below.

In the code below, I notice that the query on the rates table does not occur once per cs row. My investigation lead me to believe that this is happening because  the query is processed asynchronously. Given that, how can I get the related record id for the imported records?

I am a javascript/jquery newbie, so thanks to Dan Diebolt's examples for getting me this far.

(function(){

alert("you are in");

//The app
var dbid = "appid";

//This is the time card table
var dbidTable = "timecardtableid";

//This is the rates table
var dbidxTable = "ratestableid";

//App token
var myapptoken = "apptokenhere";

$.ajaxSetup({data: {apptoken: myapptoken}}, {async:false}); 

  var csvData;
  var promiseD3 = $.getScript("https://cdnjs.cloudflare.com/ajax/libs/d3/3.4.13/d3.min.js");

 var markup = '';
  markup += '<table>';
  markup += '  <tr>';
  markup += '    <td><label for=QBU_File><b>File</b></label></td>';
  markup += '    <td><input id=QBU_File name=QBU_File type=file></td>';
  markup += '  </tr>';
  markup += '</table>';

   var $mydialog = $("<div>").html(markup).dialog({
    title: "Select Time Card CSV file to Import",
    modal: true,
    autoOpen: false,
    width: 400,
    resizable: false,

    buttons: {
      Proceed: function() {
        $.post(dbidTable,{
         act: "API_ImportFromCSV",
          records_csv: csvData,
          clist: "171.28.29.30.31.32.89.41"
        }).then(function(xml) {
          document.location.href = dbidTable  + "?a=td";
        });

        $(this).dialog("close");
      },

      Cancel: function() {
        $(this).dialog("close");
      }
    },

    open: function(event, ui) {
      $mydialog.show();
      $mydialog.html(markup); 

      $("#QBU_File").on("change", function(e) {
        var file = e.target.files[0];
        var reader = new FileReader();
        reader.onload = function(e) {
          var contents = e.target.result; 

          $.when(promiseD3).then(function(argsD3) {
                         alert("D3 was promised");
                         var data = d3.csv.parseRows(contents);

                         //For each row in the CSV file, query the rates table for the item id and find the latest record who's effective date is not greater than the invoice date                           _.each(data, function(item) {                                                                                             varInvDate = item[1];                                                                                                  varItemID = item[6];                                                              

                                     processRecords(varItemID, varInvDate);                                                         });

                           var csv = [];
                           var index = 0;
                   });
        }

        reader.readAsText(file);
      });
    },

    close: function(event, ui) {
    }

   });

  $mydialog.dialog("open");

   function processRecords(vitem, vdate)  {

                alert(vitem);
                alert(vdate);

//This is a query on the rates table. We want to get the max effective date for the current itemID as compared to the current invoice date.

//So we query for records with an effective date on or before the invoice date. The sort is descending on effective date, so the first record in the result set will be the on we want.

 //We need to take the Record ID from this first record and stamp it on the Related Record Id field on the time card table, which we are importing into.

                 var promiseMaxEffDate = $.get(dbidxTable, {                                                           act: "API_DoQuery",
                 query: "{'30'.EQ.'" + vitem+ "'} AND {'45'.OBF.'" + vdate+ "'}",                                    clist: "45.3",
                  slist: "45",
                  includeRids: "1",
                 options: "num-1.sortorder-D"                                                                 });                                                           

                                               $.when(promiseMaxEffDate).then(function(argMaxEffDate) {                                                   alert("promiseMaxEffDate");                                                                              //Gets reference record id of the rates record for the itmeID/InvoiceDate
          //var refid = parseInt($("Record ID #", argMaxEffDate[0]).text() || "0", 10);               //alert("THEREFID " + refid);
    });

 }



alert("you are out");

//Based on code from Dan Diebolt - phone number (734-985-0721)- email address (dandiebolt@yahoo.com) .



})();
Photo of Robert

Robert

  • 214 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Neal

Neal

  • 70 Points
I have created Spreadsheet Screenshot Prototype .

Neal

NealPatil @ gmx.com
Photo of Robert

Robert

  • 214 Points 100 badge 2x thumb
Neal, That is an accurate representation of the issue I am dealing with.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,924 Points 20k badge 2x thumb
TIP: Don't post raw code in the forum - it just makes it hard to read.

Run your JavaScript code through JSBeautifier:

JS Beautifier (use two spaces for indent)
http://jsbeautifier.org/

Then post it into a source code sharing service such as pastebin.com or pastie.org with JavaScript highlighting:

http://pastebin.com/MnHwL4bq