Missing Time Tracking Records



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.



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

On-Demand customers should contact our Support team with this request because direct access to SQL Databases of On-Demand instances is not allowed due to security constraints.

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:
    	ixInterval, ixPerson, ti.ixBug, bug.sTitle, 
    	dtStart, dtEnd, fDeleted
      FROM dbo.TimeInterval ti
      LEFT JOIN dbo.Bug bug
        ON ti.ixBug = bug.ixBug
    	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. 

Warning: Directly updating the fDeleted flag to 1 in the resultset retrieved with the above SELECT statement is not recommended because the conflict of the overlapping timesheet records will not be resolved, thus resulting in corrupted timesheet data and EBS estimations.


Back to the top

Updating Timesheet Data

NOTE: when choosing to update the database records, please consider the number of impacted users, because:

  • Manipulating time tracking records in the database needs to be executed carefully, to avoid corrupting the existing timesheet data.
  • If the deleted timesheet records need to be retrieved from previous backups, it adds further complexity to the task.

If only one user is impacted, is quicker and less error-prone if the user just re-adds his timesheet data manually using the UI.

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