Datasets require the following:
Column 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 timeentryid, 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):
concat(s.ticketnbr,s.company_name,s.time) as 'id'
concat(c.name,c.computerid,c.date) as 'id'
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:
NEWID() as 'id'
UUID() as 'id'
PostgreSQLrandom() 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.
I hope this information helps! Of course if you have any questions, please do not hesitate to drop us a line at the BrightGauge Support desk: firstname.lastname@example.org