API ImportFromCSV | ADVANCED
API_ImportFromCSV is a powerful developers tool and one of my personal favorites to use via Webhooks and custom scripting. This API call would be synonymous with mass importing, or sending multiple records to add or update in Quick Base at one time via the API. The use cases and examples for using an import will be the same relative to API_AddRecord or API_EditRecord, the difference being that you can use ImportFromCSV to do multiple adds, multiple edits, or both, all at once.
So for example, if I were setting up a project and wanted a pre-defined lists of tasks to be generated with it, let’s say 15 tasks, one option would be to string together 15 AddRecord calls together to accomplish your goal. Or, create all 15 at once using ImportFromCSV.
A simple example of a basic import call might be forecasting out a calendar year when a new project is created. A new project is created, and I want a financial forecast created in a ‘Forecasts’ module, for Months 1-12.
If we’re utilizing Webhooks in this example – then what I’m hoping to have happen is that when a new record is created, I automatically create 12 forecast records so I can plan my spend. Our example POST then, might look something like below:
<qdbapi> <usertoken>example</usertoken> <records_csv> <![CDATA[PROJECT #,MONTH 1000,1 1000,2 1000,3 1000,4 1000,5 1000,6 1000,7 1000,8 1000,9 1000,10 1000,11 1000,12 ]]></records_csv> <clist>6.7</clist> <skipfirst>1</skipfirst> </qdbapi>
To break down the above – let’s looks at the the individual tags themselves
Project #, Month represent our Column headers
Each line after that,
represent each new ‘row’ in our data, still organized in the same column format
Note, that each new row is on a new line, and each data point is separated by a comma. Again, the name importFromCSV means you are structuring your data like a CSV file, where the data is structured and formatted by having each new line interpreted by a line break, and each data point separated by a delimiter, in this case a comma (CSV -> Comma-Separated Values).
If you had additional data – say an ‘owner’ of this forecast, such as the PM assigned to the project, then you would add another value to each line after the # like so:
Project #, Month, Owner
For each additional field you want to write to, add another period and then the pertinent field ID. It’s important to note that the number of fields present in your clist should match the number of columns in your data set.
BEST PRACTICE: One thing not discussed here is the usage of Record ID# or Key fields. ImportFromCSV has the ability to both add and update at the same time. If you pass in an existing Record ID# for example, that record will be updated. If you do not have a value entered for Record ID# when you make the API call, then Quick Base will generate a new record from the info provided. You can send rows of data where some have RIDs and some do not, and Quick Base will take the appropriate action.
When evaluating when to use ImportFromCSV – the biggest driver in my experience is not knowing how many records might be created. In the example above – you could get away with setting up a QB Automation and chain 15 Add requests together. But what if you need 18 this time? Or 30? The unknown is where this import call comes in handy. You can utilize custom scripting, creative formulas etc. to account for that variability and achieve your desired result.
For an example of how to use formulas to achieve dynamic record creation, I suggest taking a look at Formulas + Webhooks = Dynamic Record Creation to explore further.
- Author: Chayce Duncan (firstname.lastname@example.org)
- Date Submitted: 1/15/2019