In this article
File Types
The custom data file that is to be uploaded must be of type .xlsx.
Figure 1 - Extract of an .xlsx data file
The first row in the data file contains the column headers. These column headers will be used as the field names when the data is loaded into the table. The system assesses the type of data contained in the columns - its layout and format - and decides what data type the column should have.
The system will check the contents of the columns, and it will select the first column it finds where the data is unique to each row, to be the key.
Data Types
The following data types are supported for fields in custom data tables:
Open text - Textual data, with the ability to specify a max number of characters. Longer texts will be truncated.
Numeric - Numeric data, either whole (integer) or decimal numbers, with the ability to specify number of decimals. The system will validate that the data uploaded actually is numeric.
Date - If cells are formatted as date from Excel it will accept date format as defined in Excel, but if cells are formatted as text, the following date formats are accepted: "yyyy-M-d","M/d/yyyy","d.M.yyyy". The system will validate that the data uploaded is valid dates of one of those formats (or a valid date value in a cell formatted as date).
Single choice - For categorical data, like for example “country” or “product”, the system will parse the data and find all distinct values and automatically create an answer list based on those, with the original value as “Answer value”, and the value stripped of special characters and blanks as code. As new values are uploaded, new answers will be added.
Parent relation - Parent relation can be used when there is a hierarchical structure within the data, for example for a table with accounts data, where an account may belong to a parent account. The relation field should then contain the id of the parent account, and will validate that that id exists in the data, in the key field of the table.
Reference - A field may reference another table. For example there may be table that holds revenue data per year for each account, that has an accountid field, that references the key of an “accounts” custom data table in the same hub. A reference field allows you to set up such a reference to the key of another custom data table. The system will validate that the key referenced actually exist in that other table.