Importing in pipelines
To import data using Pipelines, see using copy records in the Quickbase channel and if you need to transform the data or manually enter values, see Bulk Record Sets.
If you've created several apps for your data, you probably have good reasons for storing that data separately. At some point, however, you may want to gather data in one place to track or compare records across multiple apps. For example, you may want to see progress on separate projects (each stored in its own app) all at once.
The Import from Table feature lets you move data across apps. You can view data stored in multiple apps in one location by collecting data from many separate apps and feeding it into one app. You can pick the specific fields and records that you want to extract and consolidate them in one or more tables. Then you can view and sort the results however you wish.
Note: You cannot import file attachments. If you map a source file attachment field to a text field, the text with the field name copies, but files don't.
What you can do with this feature
There are two types of imports: merge and copy.
A merge import takes records from one or many apps and merges them with existing records. This kind of import lets you import the same information over and over again, because Quickbase will never duplicate a record during a merge. Use a merge import to do things like:
-
Simplify apps for end users. Create a new apps and import only the tables, fields and records that you want clients to see.
-
Create a centralized reporting app. Gather records from separate sources and merge them together for comparison and tracking.
Note: You can use a merge import repeatedly, so that records your destination app match the latest updates in the source app, however, a merge import won't delete records that have been deleted in the source table.
A copy import moves records "as is" into your new app. When you copy records, Quickbase appends, or adds, each imported record to the destination app. Copy won't replace or update existing records, even if they match exactly. So, you end up with duplicate records in the new app. Select copy in the following scenarios:
-
Archive records. Transfer records out of your working app and into a dedicated archive app, allowing you to remove records from your working app.
-
Audit changes. Track activity within an app to see a history of changes over time.You could track how due dates have changed for a certain project. You can create a new app to hold copies of your records.
If you want to create a merge import, follow all the steps below.
If you're creating a copy import, skip ahead to the section called Create the destination app.
With both types of imports, you can also automatically link child records to parent records. To do so, import into a lookup field that has been marked unique on the parent table. Learn more in Import into lookup fields to link related data.
Prepare source tables for import
In a merge import example, you want to merge task records from all your project apps together in one table. You want to do this every week so you can keep up with progress in different departments. You can set up an import and repeat it each week. But how do you ensure that the records you imported last week are replaced with their updated versions this week?
In order for a merge like this to work, Quickbase needs a way to match records in the source table with corresponding records in the destination table. You make this possible by setting up special fields in both tables that hold unique matching values. This is called a merge field.
Get started by creating a new field in the source table. Keep in mind that you'll eventually import this field's values into the destination table (which may include data from multiple tables). So, you want records in this field to be unique (like those in a key field) and also to indicate what source table they came from. Meet both requirements at once by filling this field with a combination of the table name and key field value (usually Record ID#). By doing this, no two records will have duplicate values in the merge field of your destination table and you'll always know where each record originated.
To prepare each of the source tables:
-
Open the source app.
-
Add a field to hold the new record identifier you create.
-
Add a field to the source table.
-
Name it something like "ExportID," then under field type, select Formula - Text.
-
-
Create a formula for the new field.
-
Click the name of the new field to display the field's properties.
-
In the Formula box, type the formula that creates a combination of table name and key field value.
For example, "TableName-" & [Record ID#]. For example, if your table is Tasks name this "Tasks-" & [Record ID#].
-
Then click Save.
Note: If you get an error message, check your formula to make sure the field name matches exactly and that you don't leave out any characters. (Read more about using formulas.)
-
-
Repeat the steps above for each table that you want to import into the app you're about to create.
Create the destination app
Create the destination app, including the destination table(s) into which you'll import the source data. While creating the new table, add fields that correspond with fields you want to import from your other apps. These field names can match those in the source tables or they can be different. Later, when you set your import preferences, you can map which source field's data feeds into which destination field or just let Quickbase match up fields that have identical names.
If you create a merge import and you follow the steps in the preceding section, Prepare source tables for import, you need to perform one more step before you configure the import. You must create a new field to hold the merge key values from your source tables.
To create a new field to hold the merge key values from your source tables:
-
In the destination table, add a field (text type), then name it, for example, SourceID.
-
Select the Must be unique checkbox to make the new field a unique field.
The new field must be unique; it cannot contain duplicate values.
The first time you merge records during an import, Quickbase populates this field with the merge key values from the source table. During any merge after that, Quickbase matches these values to update matching records in your new application.
Import to the destination table
Begin the import by telling Quickbase where to get the source data and exactly what data you want. Once you configure all your preferences, you can import the data to populate your new app. If you repeat the same import often to see updated information on a regular basis, you can save the import settings. Doing so speeds future imports and ensures that they are identical.
To import to the destination table:
-
Open the Quickbase app into which you want to paste the data.
-
If your app has multiple tables, select the table into which you want to import data.
-
Select Import/Export from the More dropdown.
-
Select Import into a table from another table.
-
Click Import from another table.
The Import from Table screen displays.
-
If you have multiple tables in your new app, click the tab containing the name of the destination table.
-
Click Create a new import.
-
Click Choose a source table.
Quickbase displays a list of app to which you have access and that belong to the same billing account as the destination app.
-
Select the radio button to the left of the app containing the source data, then click OK.
A list of tables in that app appears.
-
Select the radio button to the left of the table containing the source data, then click OK.
Quickbase displays additional import setting options.
-
In the Import type section, select one of the following:
-
Click Copy records, then skip ahead to Step 13 to duplicate the source records within your new table.
-
Click Merge in to update existing records in the destination table.
Additional setting controls appear.
Note: For more information about which controls to choose, see What you can do with this feature.
-
Set the merge key field.
Here you'll tell Quickbase to take the values from the new combination field you created in the source tables (see the first section, Prepare source tables for import) and merge them together into your new app's merge key field.
-
Select the formula field you created in the source table (which combined table name and Record ID#) from the Source Field list on the right.
-
Select the merge key field you created in your destination table (see Create the destination table section above). from the Merge Key Field list.
Note: The only fields available in this list are unique fields. For more information, see Create Destination Table.
-
-
In the Matching section, choose the criteria that the data should match to import records:
-
Click Import all to import all records from the new table.
-
Click Import records that meet the following criteria to only import records that meet certain criteria. Criteria drop downs appear that you can use to set the parameters that you want.
-
-
In the Field Mapping section you map where to put the data Quickbase gets from each field in your source table. Select one of the following options:
-
Click Default Mapping if your destination table has field names that exactly match those in your source table. The number of fields from this table that match the source fields that appear to the right of this setting
-
Click Custom Mapping if your field names don't match, or you want to import data from a source field into a specific destination field with a different name.
A table appears that lets you map source fields and destination fields one by one. All fields in the destination table are automatically listed on the left.
Under the Source Field column on the right, select the source field you want to import data from, to populate the corresponding destination field.
Note: You cannot import file attachments. If you map a source file attachment field to a text field, the text with the field name copies, but files don't.
-
-
Click Import to populate your new table or click Save & Import if you want to repeat this import again.
Quickbase saves your settings and imports the data at the same time. Then, the next time you select the Import from Table function to populate this destination table, you don't need to configure the import options again. Instead, you can run the saved import.