Exciting feature for integrators – new mergeFieldId in API_ImportFromCSV

  • 42
  • 4
  • Article
  • Updated 1 week ago

We know that approximately half of all Quick Base traffic comes from our API. This is a staggering statistic that continues to rise. This is partly due to new integration-Platform-as-a-Service (iPaaS) solutions accelerating in the market (such as Workato, Zapier, and others). Many customers are also either themselves, or with a valued QSP, writing custom code that integrates other systems with Quick Base. Our data shows that customers leveraging these integrations are getting even more value out of Quick Base.

We want to ensure that our builders can integrate Quick Base with other solutions as easily as possible. Historically, builders have needed to use workarounds if they weren’t able to match the keys. One alternative was to change the key field on the table, rather than the built-in Record ID#. While powerful, this has the potential to become complicated and burdensome. Knowing that API_ImportFromCSV is used tens of millions of times per week, we knew we needed to make integrations easier. To that end, we are introducing a new feature to this API call. Today, when calling this API, the key field is used to tell Quick Base which records the data being imported should match to. Visually, the flow looks something like the below*.


*This is just a sample representation. Many different workflows are possible.

With our new feature coming with the November release, integrators will be able to pass in any unique field and Quick Base will do the matching. This completely eliminates what is usually the most difficult part of an integration – the “T” (Transform) in “ETL”.

Now, the process will look something like this.


We worked hard on this feature, after identifying it as an important need in the “builder journey”. We expect this to go live with our November release, with updates to the API documentation shortly thereafter. Below, I’ve included a payload request sample as well a sample of a possible error we might return when using this feature. We encourage you try this feature in your integrations. As always, please provide feedback in UserVoice by clicking the orange “Feedback” button from your “My Apps” page.

Sample request with new mergeFieldId



Sample response when the mergeFieldId is not unique


 
Photo of Harrison Hersch (QB)

Harrison Hersch (QB), Quick Base Sr. Product Manager (Platform)

  • 686 Points 500 badge 2x thumb

Posted 1 month ago

  • 42
  • 4
Photo of Gauss M

Gauss M

  • 272 Points 250 badge 2x thumb
This will be a lifesaver if it works as advertise! As you said, the T is the most complicated part of any integration. 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,754 Points 20k badge 2x thumb
This is indeed a welcome and needed feature.

>We expect this to go live with our November release, with updates to the API documentation shortly thereafter. 

Include this missing info in your API documentation:

The QuickBase HTTP API documentation overwhelmingly emphasizes and encourages using XML for both requests and responses. But forming XML requests can be verbose and overly complicated. Luckily you can send your request parameters as (1) URL encoded strings or (2) as formdata and depending on the language you are using there may well be a library or language feature that makes this dead simple. QuickBase will recognize these requests even though they do not use an XML body.

This is best demonstrated by an example. Assuming your CSV data is in the variable csvData and there are variables for apptoken and usertoken the following two code fragments demonstrates you don't need to send an XML body:

Using JavaScript and jQuery library: 
var object = {
  act: "API_ImportFromCSV",
  records_csv: csvData,
  clist: clist, "6.7.8.9.10",
  mergeFieldId: "10"
  apptoken: apptoken,
  usertoken: usertoken
};

$.post(dbid, object);

Using JavaScript and Fetch API:
var formData = new FormData();

formData.append("act", "API_ImportFromCSV");
formData.append("records_csv", csvData);
formData.append("clist", "6.7.8.9.10");
formData.append("mergeFieldId", "10");
formData.append("apptoken", apptoken);
formData.append("usertoken", usertoken);

fetch(dbid, {
  method: "POST",
  body: formData,
  credentials: "include"
});

The point is that you don't need to send an XML body and alternatively QuickBase will recognize parameters passed as a URL encoded string or as formdata. This applies no matter what language or library you are using as it is an inherent feature of how QuickBase processes API  requests.

Additionally, if you see an XML body being specified in some API call you can bust it down to a set of simple attribute/value parameters by removing the <qdbapi> tag altogether and just passing all the other tags as parameters. This procedure works for all API_*, QBI_* and JBI_* methods.

My advice is to NOT send XML bodies as it only makes your code bloated.

Proof That XML Is Extremely Bloated
https://www.zdnet.com/article/proof-that-xml-is-extremely-bloated/
(Edited)
Photo of Sam Jones

Sam Jones, Employee

  • 2,330 Points 2k badge 2x thumb
Personally, I’d just use a Service Worker.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,754 Points 20k badge 2x thumb
I knew you would come around.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,754 Points 20k badge 2x thumb
I took a screenshot so nobody could memory hole this.



Is this only when you import using the API or is this mergefieldID an option when importing interactively too? (Using the Import wizard) My clients prefer the interactive way of importing.
Photo of Harrison Hersch (QB)

Harrison Hersch (QB), Quick Base Sr. Product Manager (Platform)

  • 686 Points 500 badge 2x thumb
Surya - right now just on the API. We consume our own APIs for the UI import so we will be evaluating effort and impact of expanding to that as well.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
There is also the asaved Table to Table Saved Import which would greatly ben3foy from this feature.

If things had that, when we need it to do a manual important we would dump the import into a table which would temporarily hold the import data. There would then be a saved table to table import which could be run by API or manually which would copy these records into the real production table. And of course it would have the feature instead of just merging on the record ID or the Key field, to be able to merge on this unique field.

Then we would be free to delete the data in the temporary import table and start the cycle over again.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,826 Points 50k badge 2x thumb
@QuickBase Girl would appreciate this too. See the post from today.

https://community.quickbase.com/quick...
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 9,214 Points 5k badge 2x thumb
Official Response
I just wanted to let everyone here know that this update is planned to go live with our November release which is scheduled for Sunday November 11, 2018. That is when this new expanded functionality will begin to be available with the update to documentation for the mergeFieldId in API_ImportFromCSV to come trailing the release itself, a firm date on that hasn't been set yet but once it is I will update this thread as well.

You can find all the details of our upcoming November release below:

https://community.quickbase.com/quickbase/topics/quick-base-release-notes-for-november-2018
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,754 Points 20k badge 2x thumb
When do the updated docs go live?
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 9,214 Points 5k badge 2x thumb
Hi Dan,

Thanks I cut myself off when putting the response in and ate part of my post. I meant to indicate the update on the docs will come sometime after the release itself. I don't currently have a firm date on the update to the API guide for ImportFromCSV yet. When I do I'll make sure to update this post above with that information.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,754 Points 20k badge 2x thumb
There are a lot of very interesting use cases for this new mergeFieldID parameter.

Surprising Use For API_ImportFromCSV with mergeFieldID
https://community.quickbase.com/quickbase/topics/surprising-use-for-api-importfromcsv-with-mergefile...