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
- Connect to your database using SSMS
- Run the following select to identify the
ixPersonID of the user:
SELECT ixPerson, sFullName, sEmail FROM dbo.Person
- Run the following select to search for the timesheet records of the given user for a given period:
Explanation of the parameters in the WHERE statement (the filtering of the table records):
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
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.
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.