Looking up a user based on a placeholder

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

Hi, all,

We have an app that gets the data dumped in from an Excel spreadsheet that's an output of orders from another system. In that system, we have different shorthand codes for the users, for example, there might be a something like this:

Excel shorthand: JDOE1 (this is just text)

Quickbase username: jdoe (active for linking, ownership, etc.)

How would I create a field that would automatically do a lookup of the shorthand (JDOE) and return the the appropriate Quickbase user so that it could be used to assign ownership.

It seems like this would be a case of tables and relationships (one shorthand to one Quickbase username to many orders), but I can't for the life of me figure out how to work that relationship.

Is the dump from Excel table 1 and then there's another table that is the shorthands (again) and the corresponding username? If so, how do you hard code usernames?

Photo of Larbi

Larbi

  • 20 Points

Posted 5 years ago

  • 0
  • 1
Photo of Larbi

Larbi

  • 20 Points
Hm...I think I can convert the imported Excel field to a user field, but that creates placeholders.

Is there any way, after I've converted the placeholders to users once that it will always do that? For example, JDOE1 to jdoe for any future data dump.
No problem.

Make a table called Users with two fields.  One will be your text field like JDOE1. The other will be a user field.

Make the key field of the table the text field (your shorthand field)

Hand key your unique users.

Then do a relationship where 1user is related to many detail records based in the shorthand field and then do a lookup of the User field down to the details table.

Now each detail record will have a proper User field to use at you wish.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
As a general rule of thumb you should transform your data before importing it into another system. When you delay cleaning up your data before passing it to the next system in the pipeline you will pay the price of dealing with unnecessary complexity. Your priority should be to cleanup the data within the system that exported that data if at all possible. The next best method would be to clean the data up in Excel or through the import tool itself.

There is no need to create relationships or new tables. This tiny form will allow you to select a CSV file and transform it in your browser before doing the import to QuickBase:

Select, Transform and Import ~ User Defined Page ~ ImportCSV.html Page

https://haversineconsulting.quickbase.com/db/biwyyk7k9?a=dbpage&pagename=ImportCSV.html

I created this sample data you can download and run through the ImportCSV.html page above:

Sample CSV Sata

https://haversineconsulting.quickbase.com/db/biwyyk7k9?a=dbpage&pagename=data.csv

The original CSV data I used as a sample includes column headers and looks like this:

Handle,Phone,Email

JDOE1,(800) 555-1212,john@doe.com

JDOE2,(888) 555-1212,jane@doe.com

BDOE1,(808) 555-1212,baby1@doe.com

BDOE2,(818) 555-1212,baby2@doe.com



Prior to actually importing the data into the QuickBase table the Handle column will be transformed to a Name using this mapping:

var mapHandleToName = {

JDOE1: "John Doe",

JDOE2: "Jane Doe",

BDOE1: "Bobby Doe",

BDOE2: "Brenda Doe"

}

Here is the short script that does the importing:
Pastie Database

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=260

This script uses the HTML5 File API to read the contents of the selected file and process it before calling API_ImportFromCSV. Previously files could only be sent direct to the server unmodified. This all changes with the HTML5 File API as you can now read the contents of the selected file and after parsing the data do whatever you want to the results.

Notes:

(1) I used the d3 library for parsing the CSV and the Underscore (FWIW, QuickBase uses Underscore) library to iterate through the parse results:

d3.js

http://d3js.org/

Underscore.js

http://underscorejs.org/

(2) Although I hardcoded the mapHandleToName data within the script itself, it is a trivial matter to extract the same data from a (a) user defined page (eg mapHandleToName.js), (b) from a QuickBase utility table (without creating a relationship), or (c) any other data source including a second CSV file you maintain locally.

(3) The application has been set up to allow you to import the sample data or modifications of it so help yourself.
Photo of Larbi

Larbi

  • 20 Points
Dan, thank you--this is amazing, though a bit like bringing a bazooka to a knife fight. I may need some annotation of the code since I'm fairly new to manipulating QuickBase. Please let me come back to you with some questions. Thank you very much again!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
You are welcome but according to tvtropes the correct idiom is:

Never Bring A Knife To A Gun FIght
http://tvtropes.org/pmwiki/pmwiki.php/Main/NeverBringAKnifeToAGunFight
Photo of Larbi

Larbi

  • 20 Points
I know the idiom, but you brought the big guns to what I thought might have been a relatively simple question--great solution--hence the inversion of the idiom. Thank  you again!


Here's where I'll need some annotations, questions marked by ***:

*** What do these lines do:

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script&gt;
    <script src="biwyyk7k9?a=dbpage&pagename=d3.v2.js"></script>
    <script src="biwyyk7k9?a=dbpage&pagename=underscore.js"></script>

    <script>
      $(function(){  

***Can you add multiple var functions within one file, say HandleToName and maybe a CountryToRegion, etc, simply by adding multiple var lines?

        var mapHandleToName = {
          JDOE1: "John Doe",
          JDOE2: "Jane Doe",
          BDOE1: "Bobby Doe",
          BDOE2: "Brenda Doe"
        }

*** What are these character strings below? The biwyyk7mt is mentioned in the script source above, but the other I'm not too clear about.

        var dbid = "biwyyk7mt";
        var apptoken = "c34cjy6bebay2zcir2nvzdky9wf3";
        var clist = "6.7.8";

*** I'll probably need to work with the tool a bit to understand how the var maps to the output you list here
    
            var csv_data2 = _.map(csv_data, function(row) {
              return [mapHandleToName[row.Handle], row.Phone, row.Email];
            });

            var csv= _.each(csv_data2, function(row) {
              return row.join(",");
            }).join("\n");


Since I'm referencing your code, please let me add your details here: The code in this page is copyright by Dan Diebolt and may not be used without including my phone number (734-985-0721) and email address (dandiebolt@yahoo.com) in the footer of where you may want to use it.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Here's where I'll need some annotations, questions marked by ***:

*** What do these lines do:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>;
<script src="biwyyk7k9?a=dbpage&pagename=d3.v2.js"></script>
<script src="biwyyk7k9?a=dbpage&pagename=underscore.js"></script>

These three lines add the jQuery, d3 and Underscrore library to the user defined page. Although jQuery and Underscrore are used by QuickBase and included on QuickBase authored pages, you have to explicitly add them to your user defined page because it is a blank slate. The following code is the start of the code that makes use of the above scripts and processes the selected CSV file:

<script>
$(function(){

***Can you add multiple var functions within one file, say HandleToName and maybe a CountryToRegion, etc, simply by adding multiple var lines?

var mapHandleToName = {
JDOE1: "John Doe",
JDOE2: "Jane Doe",
BDOE1: "Bobby Doe",
BDOE2: "Brenda Doe"
}


Yes you can have multiple mappings. After reading the selected file and parsing the CSV you can perform any manner of calculation or logic you desire. The real innovation is that prior to the HTML5 File API you could not read the file's contents - now you can.

*** What are these character strings below? The biwyyk7mt is mentioned in the script source above, but the other I'm not too clear about.

var dbid = "biwyyk7mt";
var apptoken = "c34cjy6bebay2zcir2nvzdky9wf3";
var clist = "6.7.8";

These are simple attempts to parameterize the code so it can be modified quickly for your application. The dbid identifies the table you are importing into while the clist identifies the field ids that receive the imported data. The apptoken along with the statement below identifies the apptoken for making API calls and sets up all future AJAX calls to include it by default:

$.ajaxSetup({data: {apptoken: apptoken}});

This is a standard trick to use so that you don't have to append the apptoken to every API call you make.
*** I'll probably need to work with the tool a bit to understand how the var maps to the output you list here


var csv_data2 = _.map(csv_data, function(row) {
return [mapHandleToName[row.Handle], row.Phone, row.Email];
});

var csv= _.each(csv_data2, function(row) {
return row.join(",");
}).join("\n");

This code uses Underscore (the single character "_" is the Underscore object) library to process the data in the CSV file. This statement uses the d3 csv parser to parse the file:

var csv_data = d3.csv.parse(reader.result);

The first fragment of code you cite process each row of data and maps the first field from handle to name. The second fragment of code you cite builds up a second CSV blob of data that is sent to the server as the correct data to import.

FWIW its worth, I am only using the csv parsing method from the d3 library. The real beauty of d3 is the visualizations you can generate (with QuickBase data of course).

https://github.com/mbostock/d3/wiki/Gallery