Working with tables

Tables store records of data in rows and columns. In specific cases, tables also store data and metadata that support other features in Diligent One. Once you create a Collection and Analysis to contain a table, create an appropriate table type and either import data or distribute the associated questionnaire to collect responses.

Types of tables

There are several types of tables in the Results app. Each type is suited to particular types of tasks and data, so always choose the type that most closely matches your needs.

• Data Analytic stores data imported from Analytics or a supported file format. This type is ideal for managing and automating remediation on records, gathering more information about records, and storing data you can use to create visualizations and reports.

• Survey stores responses to a questionnaire that are distributed through email or an anonymous link.

• Event Report stores responses to a questionnaire distributed as an anonymous link.

• Projects These tables store data and metadata that support features in Diligent One's Projects app. They look similar to standard tables, but have certain features or limitations based on that table's purpose. Diligent One generates these on its own, as needed, and you may not be able to alter or delete them through the Results app. For example:

  • Results uses these tables to store control performance or certification questionnaire responses for active projects. These tables are automatically created when you enable control performance or certifications in the Projects app. You can identify them by the View linked project hyperlink in the collection this table belongs to.

• System In specific cases, tables have a different type. These tables typically store data and metadata that support other features in Diligent One and may have certain features or limitations based on that table's purpose. Diligent One generates these tables on its own, as needed, and you may not be able to alter or delete them through the Results app. For example:

  • Reference tables store data from another system, populated by a robot, which you can view and use in Diligent One.

Table size limitations

Each table contains a maximum of:

• 100,000 records
• 500 columns

  Note

  You can import up to 100 columns at a time. More columns can then be added using linked questionnaires or exports from Analytics that use a primary key. The combined number of data columns and questionnaire columns cannot exceed 500. Metadata columns are excluded from this limit.

• 256 characters per field name, including blank characters (does not apply to individual data fields)

Before you start

Each table must be part of an analysis and collection. Before you start working with tables, make sure these parent data structures exist.

• Create a collection
• Create an analysis

Create a table

Depending on your company's workflow, you can create tables from the Results app, or in the case of data analytic tables, you can also create them from Analytics.

Create a table from Results

1. Open the Results app.

2. Navigate to the collection and analysis where you want this table to exist.

3. Select Add Table > TableType. Depending on which Diligent One product you are using, the New Table button might be Add survey or Add event report, instead.

4. Enter the table details:

   • In the Name field, enter a new name.

   • Optional. In the Description text box, enter any additional information about table.

5. Complete the fields specific to the table type:

   Table type	Field	Description
   Data Analytic	Script name (optional)	The name of the script, or process, used to generate the result set. No physical association exists between the script name in this field and the processing of the script in Analytics.
   Event Report	Questionnaire	The linked questionnaire that is used to collect responses that are stored in the table. You can select an existing questionnaire or click Create New to create a new questionnaire. For more information, see Gathering responses from questionnaires.
   Survey

6. Click Create.

   • If you are creating a Data Analytic, you are redirected to the Import Data page where you can begin the process of importing data. For more information, see Importing and exporting data.

   • If you are creating an Event Report or Survey, you are redirected to the Collection where you can distribute the questionnaire to begin collecting responses. For more information, see Distributing questionnaires

Create a table outside of Results

You can create a Data Analytic from Analytics using the EXPORT command. See Exporting exceptions to Diligent One.

Change a table's settings

1. Open the Results app.

2. Navigate to the collection and analysis that contains the table you want to update.

3. For that table, click and click Settings.

4. To update the table details:

   • In the Name field, enter a new name.

   • In the Description text box, enter any additional information about table.

5. Update any fields specific to the table type:

   Table type	Field	Description
   Data Analytics and some system tables (control performance and certifications)	Script name (optional)	The name of the script, or process, used to generate the result set. No physical association exists between the script name in this field and the processing of the script in Analytics.
   	Primary key	The field that uniquely identifies records. For more information, see Specifying a primary key.
   	Email and questionnaire record information	The fields recipients can see in questionnaires and questionnaire assignment email notifications. You can select any imported data, Results metadata fields, and questionnaire fields. You must select at least one field. For more information, see Customizing questionnaire emails and Sending questionnaires from records. Note If this is a Certifications table, recipients of sequential certifications always see previous responses, even if you turn questionnaire fields off here.
   Event Reports	Manage Questionnaire	Settings for the linked questionnaire that is used to collect responses that are stored in the table. For more information, see Gathering responses from questionnaires.
   Surveys

6. Optional. Select Auto publish this table's data as a dataset to the Activity Center to continually publish the most up-to-date data for use in dashboard creation. If you want to pause automatic publishing, unselect this option.

Note

Once a dataset is created, Report Admins in the Activity Center app will have access to all of the records of the table. The Results app table will be published automatically at a regular interval which is set at every 24 hours by default.

7. Click Save.

Publish the Results app data to the Activity Center app

You can publish Results data as datasets to the Activity Center app where it can be leveraged by users with Report Admin access to create custom dashboards and optionally publish to Boards site.

1. Open the Results app.
2. Navigate to and select the collection that contains the data you want to publish to the Activity Center app. This opens the table.
3. For that table, click and select Publish to Activity Center. The Publish to Activity Center dialog box opens.
4. Click Publish. This dismisses the dialog box and the dataset becomes available in the Activity Center app.
Note

Depending on the size of the dataset, it may take some time for it to appear in the Activity Center app. Once the dataset is created/updated, Report Admins have access to all the records of the published table in the Activity Center app.

Important

The dataset in the Activity Center app is not updated automatically when changes are made in the corresponding Results table. You will need to publish the dataset to the Activity Center app again to push changes into the dataset for up-to-date reporting.

5. Optional. If you have Report Admin access, you can create and share custom dashboards from the Activity Center app that can be connected with Boards sites. See Managing dashboards in the Activity Center app and Diligent One: Connecting Activity Center dashboards with Boards sites .

Publish Results app data to the Activity Center app automatically

A Results Admin can ensure data freshness by enabling automatic publishing of Results app datasets. This way, you don't need to manually publish new data that needs to be shared with the Activity Center app for up-to-date dashboard creation.

1. Open the Results app.
2. Navigate to the collection and analysis that contains the table you want to update.
3. For that table, click and click Settings.
4. Select Auto publish this table's data as a dataset to the Activity Center to continually publish the most up-to-date data for use in dashboard creation.
Note

Once a dataset is created, Report Admins in the Activity Center app will have access to all of the records of the table. The Results app table will be published automatically at a regular interval which is set at every 24 hours by default.

5. Click Save.

Pause automatic publishing of Results app data to the Activity Center app

A Results Admin can pause the automatic publishing of Results app data to the Activity Center app at any point.

1. Open the Results app.
2. Navigate to the collection and analysis that contains the table you want to update.
3. For that table, click and click Settings.
4. Unselect Auto publish this table's data as a dataset to the Activity Center to pause automatic publishing.
5. Click Save.

Delete a table

When you delete a table, all associated triggers and records are permanently deleted.

1. Open the Results app.
2. Navigate to the collection and analysis that contains the table you want to delete.
3. For that table, click and click Settings.
4. At the bottom of the page, do one of the following:
   • To delete all records from the table, click Delete records.
     Note

     This option removes all records from the table and all data columns imported from another source. It does not remove:

     • Diligent One-generated columns like metadata or columns from linked questionnaires
     • Settings for that table
     • Interpretations and visualizations (you will not be able to open any interpretations until the data needed to generate the interpretation is available)
     • Metrics
     • Triggers
   • To delete all records and the table, click Delete everything.
5. Click Delete in the confirmation dialog box.

Show or hide table IDs

Occasionally, you need to know a table's ID so you can interact with it using automation, like when you are using Diligent One's API, writing a script, or setting up a robot. You can display these IDs next to each table's name on any collection in Results.

1. Open the Results app.
2. Navigate to the collection that contains the table with an ID that you need.
3. Near the top of the page, click Show table IDs to toggle them on or off. When this is on, each table's ID appears next to its name.