Discussions

Surprising Use For API_ImportFromCSV with mergeFiledID

  • 1.  Surprising Use For API_ImportFromCSV with mergeFiledID

    Top
    Contributor
    Posted 12-02-2018 16:52
    I just discovered an extremely useful application of calling API_ImportFromCSV with the new mergeFieldID parameter that doesn't even involve importing any data. It is so useful that I thought I would pass the method along.

    Let's start with a simple table consisting of the 24 letters of the Greek alphabet where the field [Letter] is fid=6:
    1 Alpha
    2 Beta
    3 Gamma
    4 Delta
    5 Epsilon
    6 Zeta
    7 Eta
    8 Theta
    9 Iota
    10 Kappa
    11 Lambda
    12 Mu
    13 Nu
    14 Xi
    15 Omicron
    16 Pi
    17 Rho
    18 Sigma
    19 Tau
    20 Upsilon
    21 Phi
    22 Chi
    23 Psi
    24 Omega
    Further assume you have a list of field values on interest for this [Letter] field:
    var values = ["Beta", "Delta", "Epsilon", "Omega"];
    var fid = "6";
    Now the goal is to determine the corresponding [Record ID#]s (ie rids) for the given field values. I am only using four field values but the technique I will show you can be applied (1)  to much larger sets of field values and (2) to a table with a large number of records. The only restriction is that the field has to have the Unique attribute set:




    Here is a demo you can test with:

    Get Rids Given Field Values ~ List All Records
    https://haversineconsulting.quickbase.com/db/bn7z5v77q?a=td

    Here is an abbreviated version of the code emphasizing the input field values and the corresponding output rids:

    var values = ["Beta", "Delta", "Epsilon", "Omega"];
    var fid = "6";

    getRids(dbidRecords, values, fid)
      .then(function(rids) {
        console.log(rids);
      });

    // console output:
    // ["2", "4", "5", "24"]

    You can paste the full code listed in the Pastie into the console of the application:



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

    Notes:

    (1) The reason this technique is so useful is that we now effectively have a way to "query" a table for the rids associated a list of field values in one API call. It is a little odd that this behavior is delivered by the method API_ImportFromCSV but the alternative is to write a series of API calls that is overly complicated.

    (2) If you examine the XML response you will see that no records are actually added or modified as a result of the API_ImportFromCSV method:
    <qdbapi>
      <action>API_ImportFromCSV</action>
      <errcode>0</errcode>
      <errtext>No error</errtext>
      <num_recs_input>4</num_recs_input>
      <num_recs_added>0</num_recs_added>
      <num_recs_updated>0</num_recs_updated>
      <num_recs_unchanged>4</num_recs_unchanged>
      <rids>
        <rid update_id="1543691100364">2</rid>
        <rid update_id="1543679767130">4</rid>
        <rid update_id="1543679767130">5</rid>
        <rid update_id="1543679767130">24</rid>
      </rids>
    </qdbapi>