Table-to-Table Import | INTERMEDIATE
Quick Base allows users to create Table-to-Table Imports to move data from one table to another. A Table-to-Table Import is an automation, so once it is set up, there is no action required from the user; the automation will run based on the trigger chosen in the automation. If you are not familiar with Automations check out this short blog post from Quick Base demonstrating the basics. Below, the three most common use cases for Table-to-Table Imports will be covered.
- Aggregate Data from Multiple Applications – users, especially managers, likely use multiple applications within a Quick Base realm. This architecture is great for segregating data and customizing apps for specific uses, but it can slow review processes because managers cannot compare records from multiple apps side-by-side. By creating a new app and Table-to-Table Imports, you can aggregate information from multiple apps into the new, centralized app.
BEST PRACTICE: In this use case, you will want to set up the Table-to-Table Imports as a merge import. Review the Merge vs. Copy section for more information.
- Limit Information Displayed – as your apps have grown and become more complex, you have probably had to limit the information displayed to certain user groups. In addition to form rules, app-level and field-level permissions, you can create a Table-to-Table Import to specify exactly which fields are imported. This ensures there is no risk of displaying sensitive information to particular user groups that have access to the destination table of the Table-to-Table Import. This is especially useful when displaying information to third parties like customers or suppliers.
BEST PRACTICE: Similar to the aggregating data use case, in the limiting information use case, you will want to set up the Table-to-Table Imports as a merge import. Review the Merge vs. Copy section for more information.
- Archiving Information – over time, apps accumulate a lot of records, some of which can be unnecessary for day-to-day operations. For example, it is probably not necessary to maintain invoices from more than a year or two ago in your active app, but at the same time, you do not want to erase this information permanently. A Table-to-Table Import is a perfect solution to move this old data to an archiving app, so that you can remove the records from your active app and improve performance.
BEST PRACTICE: In this use case, you can set up the Table-to-Table Import as a copy merge. Review the Merge vs. Copy section for more information.
Merge vs. Copy
The most important decision to make when creating a Table-to-Table Import is the Import Type. In a Table-to-Table Import, records can be merged or copied.
✓ A Copy Table-to-Table Import is the more simple option; it will transfer records from the source table to the destination table and create new records in the source table every time the import runs. For example, if there are 20 records in the source table and the import is scheduled to run every day, then after two days, there will be 40 records in the destination table. This functionality is useful if you want to see how records progress over time or archiving information (once the information is transferred to the destination table, the records in the source table will be removed).
✓ A Merge Table-to-Table Import is slightly more complex than Copy Table-to-Table Import; it will take the records from the source table and merge or create new records in the destination table. For example, if there are 20 records in the source table and the import is scheduled to run daily, then every day, the 20 records in the destination table will be updated to match the 20 records from the source table. On the 3rd day, if there are 10 additional records added to the source table, then, when the automation runs, the 20 original records will be updated and the 10 new records will be added to the destination table. This is accomplished using the merge field (see How to Create a Table-to-Table Import below for more information).
How to Create a Table-to-Table Import
Create the destination table and its fields in a new or existing app. If you are creating a Merge Table-to-Table Import, you must create a Merge Field in the source table. This field should be type formula-text; best practice is to use the formula: “Table Name”&[Record ID#] – by including record ID in the formula, it ensures each record’s Merge Field is unique. In the destination table, make certain to create a Source ID field; this field should be type text and must be unique. In step 9, you will map the Merge Field from the source table to the Source ID field in the destination table.
On the new table homepage, click ‘More’ then ‘Import/Export’
Choose the ‘Import into a table from another table’ radio button.
- Click ‘Import from Another Table’ then click ‘CREATE A NEW IMPORT’
- Name the Import
- Choose the Source Table
- Choose the Import Type: Copy or Merge
- Choose if all records should be imported or if only records meeting certain criteria should be imported in the Matching section
- Map fields from the Source Table to the Destination Table
- Save the new Import
- Navigate to Automations (settings > automations) and create a new one
- Set up the trigger and then add an action (Table-to-Table Import)
BEST PRACTICE: One shortfall of Table-to-Table Imports is they are unable to transfer file attachments from one app to another.
If you want to learn more about customizing a role’s access, check out the article: Displaying Information for Certain Roles.
- Author: TJ Lawless (email@example.com)
- Date Submitted: 2/14/2019