Dataset Requirements

Follow

This article details all Datasets requirements.

Columns with Unique ID's 
For service tickets, this can be the ticket number, and for time entries, this can be the time entry id, but for other datasets, there might not be a unique id, so here are a few tips:

  The column, 'id', needs to be a unique number per row, so for service tickets you can duplicate the ticket number:

sr_service_recid as 'id',

sr_service_recid as 'ticket_number'

For time entry information, use the time entry id, not the ticket number (as there are multiple time entries per ticket).

If there is no unique number, your first option is to create a unique identifier by combining multiple fields (the fields within the parenthesis will change based on dataset):

MS-SQL

concat(s.ticketnbr,s.company_name,s.time) as 'id'

mySQL

concat(c.name,c.computerid,c.date) as 'id'

PostgreSQL

concat(field1,field2,field3) AS 'id'

If that doesn't work, then there are unique identifier functions in the SQL database that can work as well, but use these as a last resort since they can slow down dataset syncing and put a load on your SQL database:

MS-SQL

NEWID() as 'id'

mySQL

UUID() as 'id'

PostgreSQL

  random() AS 'id'

No 'Order By'
For the time being, do not use the "order by" clause at the end of SQL statements for MS-SQL databases unless you're using a TOP command.

No Spaces in Field Names

When setting a field name, the dataset builder should automatically replace a space with an underscore, but you can do it yourself to prevent any possible issues.

No Stored Procedures or Declarable Functions
Our custom dataset creator does not support stored procedures or declarable functions such as '@'. This is an unfortunate result of supporting multiple database types.

If you have any questions, please contact Support by selecting Help > Open a Ticket from the top menu bar.