Overview

When missing Time Tracking records the question is:

• Was the timesheet data deleted from FogBugz by a user using the UI? In this case, the records are still in the database, just marked as deleted. OR
• Is the data completely missing from the database? For example because:
  • it was never added by the user, or
  • there was a FogBugz error in handling time tracking or timesheet records.

 

---

Solution

When deleting time tracking records from the UI, FogBugz does not actually delete them from the database, just marks them as deleted and excludes them from timesheets.

If the user can confirm (better: provide proofs) that he did add the Time Tracking records, the next step would be to check the timesheet records available in the FogBugz database.

For On-Demand customers, before requesting deep diving into your database, you could do a quick check with our support team whether we are aware of similar issues with other customers.

 

Checking the Database Records

The easiest way to verify or alter database records is always by using the FogBugz API. However, our Time Tracking API endpoint does not return records marked as deleted, and currently, there are no plans of changing this.

Thus, the only option to check whether the time tracking records are available is to check in the database the TimeInterval table.

1. Connect to your database using SSMS
2. Run the following select to identify the ixPerson ID of the user:

   SELECT ixPerson, sFullName, sEmail FROM dbo.Person

3. Run the following select to search for the timesheet records of the given user for a given period:

   SELECT 
           ixInterval, ixPerson, ti.ixBug, bug.sTitle, 
           dtStart, dtEnd, fDeleted
     FROM dbo.TimeInterval ti
     LEFT JOIN dbo.Bug bug
       ON ti.ixBug = bug.ixBug
     WHERE 
           ixPerson = 4 -- the ixPerson ID of the user
           AND fDeleted = 1 -- optional. if you would like to see only the records marked as deleted
           AND dtStart >= '2021-01-01 00:00:00' -- optional. intervals with start dates after this date
           AND dtEnd <= '2021-02-01 00:00:00' -- optional. intervals with end dates before this date

   Explanation of the parameters in the WHERE statement (the filtering of the table records):
   • ixPerson - the ixPerson ID of the user that we identified at the previous step
   • fDeleted - optional; if you would like to see only the records marked as deleted
   • dtStart - optional; filters for intervals with start dates after this date
   • dtEnd - optional; filters for intervals with end dates before this date. 

 

Back to the top

---

Updating Timesheet Data

In general, it is recommended to update Timesheet data using the UI, because the UI has a built-in mechanism to check for overlapping time intervals and help the user correct these overlappings by providing recommendations.

The proposed method for updating timesheet data is:

• use the data retrieved with the SELECT statement used in the previous chapter, and
• update each user's timesheet data manually, using the FogBugz UI. If there are overlapping periods (data conflict), override the UI's recommendation if necessary.

 

If you would like to have a script to take care of this for you, because multiple users and multiple timesheet records are impacted, then you will need our Professional Services team's help in providing such a script. Before contacting them, please contact our Success Team for a quote for this work.

 

Back to the top