Dataset Requirements

Randall Berg -

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):

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.

 

 

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: support@brightgauge.com

Have more questions? Submit a request

Comments

Powered by Zendesk