Field name on Activities (sys tasks) table

[Joyce]

Hi,

which field on h_sys_tasks table are the columns seen on 'Activities view' linked with?

I am after columns 'Assigned to', and  'Status' .

I am trying to create a  widget for count of open/active task by team (assigned to a team).

Thanks.

 

Regards,

JOyce

 

[Guest Ehsan]

Hi @Joyce,

The column "h_sys_tasks.h_assigned_to" stores the assigned-to detail of an Activity. The column "h_sys_tasks.h_state" stores the status of an Activity. Statuses include:

• 1 for assigned
• 10 for cancelled
• 4 for completed

You can find more information on reporting on Activities, in the following post.

Hope this helps.

Ehsan

 

[Joyce]

Thanks @Ehsan. The value i have on 'Assign to' from  running select query on database direct is something like 

urn:sys:group:Instance/company/department/team/. I am interested on filtering by Team.  So if I say:  h_sys_tasks.h_assigned_to like '%department%' I am not getting any data returned, but sql error. Thanks.   Regards, Joyce

[Guest Ehsan]

@Joyce,

That's strange.

The following statement works for me.

• SELECT * FROM h_sys_tasks WHERE h_assigned_to LIKE '%SecondLineSupport%'.

You can also use the Reports feature (Admin Tool > "Hornbill Service Manager" Tile > "Reports" Tile > Create a Report and select "SQL Schema Designer" option under "Reporting Using" field).

Below, I've added a filter to report on h_sys_tasks -> Assigned To field. Please refer to this as an example.

 

[Joyce]

Need to do it as, a  widget, as part of a daily dashboard.