When I joined RedZebra, we were good at supporting customers because there were few requests and few problems. We've grown a lot and managing support requests is not easy when there are all the different configurations etc.

I introduced BugTracker.NET several months ago and got buy-in from the rest of the team to use this tool to log and record everything that comes through from support. We also use it to plan support if we're waiting on someone. Ideally we'd integrate this with Outlook Calendars and resource scheduling so we can plan ahead, but this hits the budget for now.

I wanted a way to add a date to the items, and then be able to see when they're planned in for, and if something is due for today. We already use BugTracker.NET's categories, organisation, statuses and priorities, this just helps us organise that little bit better since we don't have any time to go in and customise it ourselves.

I added a custom field called Planned Date and then modified our Queries (bug lists) to show the planned date, and another column indicating if the job is today. I'd previously joined the two together but mixing datetime and varchar sort wouldn't be easy in this. I'm sure we could write a custom sort, or just modify the codebehind to do this, but I wanted to try and stay within the confines of the editors if I could.

Adding a Custom Field

• In BugTracker.NET, navigate to Admin > Custom Fields
• Click Add New Custom Field
• Planned Date was the name of the field, and I chose datetime as the type. This hint lets BugTracker.NET display the right component. There's a few different options here worth exploring, but for now just choose datetime so we get a calendar control.
• Save

Now when you return to the Add or Edit Bug screens, you'll be presented with the field.

Adding fields in here actually modifies the bugs table definition in the database (by adding and removing columns), so use with caution.

Selecting dates

Edit an item to see your new column.

Custom SQL for Queries

You won't be able to see Planned Date or Today columns unless you add them to your Queries.

In BugTracker.NET navigate to Queries > Add New Query

Enter "planned" as the Description and copy the code below into the SQL box. Hit Create to add the Query and then navigate to Items > Query dropdown list > planned to see your new list.

You can organise by Today or Planned as expected. It should look like the screenshot above.

I used an inline SELECT CASE which is similar to a switch or inline if for dates.

select DISTINCT
isnull(pr_background_color,'#ffffff'),
bg_id [id],
isnull(bu_flag,0) [$FLAG],
CASE WHEN CONVERT(DATETIME, bugs.[Planned Date]) >=
(SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0))
AND CONVERT(DATETIME, bugs.[Planned Date]) <
(SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 1))
THEN 'Today!'
ELSE '' END [Today],
bugs.[Planned Date],
bg_short_desc [desc],
isnull(st_name,'') [status],
isnull(asg.us_username,'') [assigned to],
isnull(ct_name,'') [category],
isnull(pj_name,'') [project],
isnull(og_name,'') [organization],
isnull(pr_name,'') [priority],
rpt.us_username [reported by],
bg_reported_date [reported on],
isnull(lu.us_username,'') [last updated by],
bg_last_updated_date [last updated on]
from bugs
left outer join bug_user on bu_bug = bg_id and bu_user = $ME
left outer join users rpt on rpt.us_id = bg_reported_user
left outer join users lu on lu.us_id = bg_last_updated_user
left outer join users asg on asg.us_id = bg_assigned_to_user
left outer join projects on pj_id = bg_project
left outer join orgs on og_id = bg_org
left outer join categories on ct_id = bg_category
left outer join priorities on pr_id = bg_priority
left outer join statuses on st_id = bg_status
where bg_status not in (5,6)
order by bg_id desc