Get value from Combo Box

[acasper]

New to Lansweeper and need to create a report for tickets that show Assigned Agent, County Department (that end user selected), and Time Worked from the notes.

Everything I need is working except for the County Department. I cannot figure out how to get the value that's selected from the combo box when a user submits a ticket.

Select Top 1000000 htblticket.ticketid,
htblticketcustomfield.fieldid,
htblcustomfieldvalues.value,
htblcustomfields.name
From htblticket
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Inner Join htblcustomfields On htblticketcustomfield.fieldid =
htblcustomfields.fieldid
Inner Join htblcustomfieldvalues On htblcustomfields.fieldid =
htblcustomfieldvalues.fieldid
Where htblcustomfields.name = 'County Department'

[maltenburger]

Thanks for posting this! I had a similar problem and you just saved me a lot of time.

[acasper]

After much learning and trial and error I got my report.

Ticket/CreationDate/LastUpdated/Type/State/Priority/Source/User/AssignedAgent/UserLastNote/Subject/TimeWorked/Department

The department is a ticket custom field that has a dropdown of all our departments.


Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblusers2.name As UserLastNote,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) %
60))) + ' minutes' As TimeWorked,
htblticketcustomfield.data As Department
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
htblusers2.userid
Inner Join (Select Top 1000000 htblnotes.ticketid,
Sum(htblnotes.timeworked) As MinutesWorked
From htblnotes
Group By htblnotes.ticketid) WorkTime On WorkTime.ticketid =
htblticket.ticketid
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Inner Join htblcustomfields On htblticketcustomfield.fieldid =
htblcustomfields.fieldid
Where htblcustomfields.name = 'County Department' And htblticket.spam <> 'True'
Order By WorkTime.MinutesWorked Desc,
htblticket.ticketid