This topic looks at lookups, queries, and steps in a general way, with an eye to performance.
Lookup — Looks for an item based on search criteria and returns a single result.
- Pro: Easier to setup and better performance
- Con: In Quickbase, you can only look up based off the record ID, even if it is not the key field and it can only return one result.
- In general: You can sometimes can search by other ‘id’ like values, such as in google drive. Use a look up whenever possible to improve performance.
Query — Searches all records in the selected account and returns a list of results.
- Pro: Can search based off any field, and can return multiple results.
- Con: More resource intensive and you may need to use a loop or jinja to return results.
- In general: Think of queries like a report — you want to keep it to returning ONLY the records and fields that you need — no extra stuff.
Step — A step is a sequence of instructions that can iterate over a group of records and do a thing for each one.
- Steps are an opportunity to increase the efficiency of your pipelines.
- Try to avoid using a query in a loop. It's better to perform the query first, then loop over the results. This will improve the performance of your pipeline and any app that is connected to it.
Types of steps:
- Blue (Action) steps — returns a single item.
- Green (Query) steps — returns a list of data. For small amounts of data the output of the Green step can be processed as jinja as well. You can iterate multiple times over a list from a single Green step.
Note: In lists, because of the way we paginate, we only store 100 values you can access. If you try to look up 200 records and loop it over in jinja: you're only going to see the first 100.
Tips with steps
- The step construct is the preferred way of processing lists. The step construct dereferences the list to an individual item - the same letter pipe outside of the step is a list, while in the list is the current loop target item.
- If your step is 100 or less, it will be now done sequentially, over 100 and it will be done in parallel.
- Each step iteration produces a step target item audit - this can be helpful to track the step iterations.
- Filtering in the Search pipes and how it is applied after data gets to Pipelines from the remote system
- You can nest queries and steps, but they may experience performance issues.
- You can check for a blank list, by checking for the list size using jinja and by using an IF pipeline construct. You can also put a condition after a list to see if it was blank.
- A Query step uses pagination and there are audit records produced for each page of data.
- The final audit of the Query result showing the total items returned.
- When you filter in the pipeline activity page you can show just the Query step and get idea on the amount of data processed
Bulk data processing
Prepare Bulk Record Import and Commit Upsert are intended to only be run once outside the context of the loop where you are adding records. Commit pushes all records that have been added during the pipeline run into the Quickbase table. For example:
Step D above identifies the destination table where new records would be added, in other words, specifies the destination table for the data and select the fields that your records will update.