Surprising Use For API_ImportFromCSV with mergeFiledID

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>