Adding and Updating Records with Tables in Bulk

Article Number:040730

If you want to bulk add or update records that contain tables, you can do so by using an Excel workbook file or a CSV file to import data.

This is useful when you want to add or update a large amount of table data. You can avoid troublesome tasks such as creating and editing records one by one, manually creating tables, etc. This is also useful when formulas are used in tables, because data are automatically recalculated at the time of importing a file.

You can bulk add or update records including tables in either of the following ways.

  • Create a file from scratch, and import that file to your app:
    This is useful when you want to create new records in bulk.
  • Export records from your app to a file, edit that file, and then import that file to your app:
    This is useful when you want to update existing records in bulk. You can overwrite existing rows or add new rows as shown below. You can also create new records. Image

Notes on Bulk Adding/Updating Table Data

  • To perform this action, you need to be granted the "Import from file" permission in the Permissions for App setting.
    If you want to export records in the app to a file, edit the file, and then import it back to the app, you also need the "Export to file" permission.
    By default, the "Import from file" and "Export to file" permissions are granted only to the user who created the app.

  • If a table contains an "Attachment" field, you cannot bulk update data in the table.
    If you are to add new records in bulk, you can import data into the fields in the table except for the "Attachment" field in the table.

  • If a table contains a "Lookup" field and the Prohibit duplicate values option is disabled for the field that is specified as Key Field in the setting of the "Lookup" field, you cannot bulk update data in the table.
    If you are to add new records in bulk, you can import data into the fields in the table except for the "Lookup" field in the table.

  • If a table contains a "Lookup" field and settings are configured as described below, you cannot bulk add/update data in the table.
    • A user who carries out the Import from File operation is not granted the "View records" permission in the app that is specified as Datasource App in the setting of the "Lookup" field.
    • A user who carries out the Import from File operation is not granted the "View" permission for the field that is specified as Key Field in the setting of the "Lookup" field.

  • When you import a file, the following fields are re-calculated or updated. Therefore, you do not need to specify them as import targets.
    • Calculated field
    • Text field (If Calculate automatically is enabled)
    • Fields specified in the Field Mappings section in the setting of a "Lookup" field

  • When you import a file, you must specify a column in the Column in File section for all the fields in the table that are displayed in the Field in App column. You must also specify columns for fields whose data is not to be changed so that these fields are also included in the target of file import.

Steps (Export Records from Your App to a File, Edit Them, and Then Import Them)

This section describes the steps to export app records to a file, edit data in the file, and then import it back to the app.

  1. Open the "View" screen of an app.

  2. If necessary, click the filter icon Funnel icon or select a view with preconfigured filter conditions to narrow down the records to be exported to a file.

  3. Click the option icon Image on the upper right of the screen, and then click Export to File.

  4. Specify tables and fields that you want to export, and then click Export.
    Image

    • If you click Add All, all the remaining fields are added to the right side of the fields that are already added.
    • If you click Clear All, all the already added fields are cleared.
    Records are exported to a file.
  5. Open the file, edit the record, and save it.

    • When overwriting the table data:
      Edit the cell that corresponds to the table. Image
    • When adding a row to the table:
      Copy another row in the same record, and leave the first column empty. Then, enter data in the cell that corresponds to the table.   Image
    • When creating a new record including a table:
      Add a row, enter "*" in the first column, and leave the record number empty. Then, enter data in the cell that corresponds to the table.
  6. Click the option icon ・・・ on the upper right of the screen, and then click Import from File. Image

  7. Click Browse and select a file for import.

  8. When you are prompted with Select Data Scope, select First row is header.

  9. Check that the content of the file you are importing is correctly displayed in Preview. If necessary, select different options from the Character Encoding drop-down list.

  10. Associate the columns in the file for import with the fields in the app.

    • You must specify a column in the Column in File section for all the fields in the table that are displayed in the Field in App column. You must also specify columns for fields whose data is not to be changed so that these fields are also included in the target of file import.
    • In the Field in App column, fields in a table are displayed in the following format: "Table name > Field name" Image
  11. If you want to overwrite table data in an existing record, select Key to Bulk Update.
    If the value of the field specified as Key to Bulk Update matches the value of the associated column in the file, the record in the app is overwritten with the data of the row in the file. Image

  12. Click Import on the upper left side of the screen.

If your Import from File operation failed, refer to What to Do If You Failed to Bulk Add/Update Records for the solutions.