Published on October 30, 2021.

I have several NetSuite clients that are using its timesheet management & time tracking capabilities. One of those clients provides highly specialized repair and maintenance services to companies in the building technologies industry. The client employees hundreds of technicians across the Australian continent.

Early this summer, I developed a custom Web portal for this client. They're using it to dispatch technicians to customer locations, to track parts usage, and to track the time spent on each job. Additionally, technicians that serve as supervisors use the portal to review and approve timesheets submitted by the employees that report to them.

A few weeks ago, my client requested an enhancement to the portal. They wanted to give technicians better visibility into their timesheets, so that they can check on approval statuses, see the details behind each timesheet, and more.

In this post, I'll introduce you to the tables that are used by NetSuite's timesheet management function, and show a few of the queries that I developed to fulfill this client's requests.

But first, here's a short animation that shows how the queries are being used in the portal.

Click the animation to view it in a larger window.

The Timesheet Table

Let's start by examining the primary table that NetSuite uses for timesheet management. Not surprisingly, it's a table named Timesheet.

SELECT TOP 1
	*
FROM 
	Timesheet

Here's what that table looks like.

Now let's clean that query up a bit, so that only a few of the required columns are returned. I'll also join to the employee table so that we can get some values from it as well - especially the employee's supervisor.

SELECT TOP 1
	Timesheet.StartDate,
	Employee.LastName,
	Employee.FirstName,
	BUILTIN.DF( Employee.Supervisor ) AS Supervisor,
	BUILTIN.DF( Timesheet.Subsidiary ) AS Subsidiary,
	Timesheet.TotalHours,
	Timesheet.SubmittedHours,
	BUILTIN.DF( Timesheet.ApprovalStatus ) AS ApprovalStatus
FROM
	Timesheet
	INNER JOIN Employee ON
		( Employee.ID = Timesheet.Employee )

As you can see, the join to the employee table is very easy. Also, I'm using the BUILTIN.DF function to map the numeric values of a few fields to their text values.

Here's the result of that query.

Timesheet Approval Statuses

		
SELECT
	*
FROM
	TimeLineApprovalStatus		

And here are the results.

Timesheet Details

The details behind each timesheet are stored in a table named TimeBill. It's in this table that you'll find information such as the date that work was done, the customer that work was done for, the number of hours that were worked, and more.

To provide technicians with a way to "drill down into" a timesheet to see the details, I used the following query.

SELECT
	TimeBill.Trandate,
	BUILTIN.DF( TimeBill.Customer ) AS Customer,		
	BUILTIN.DF( TimeBill.Item ) AS ServiceProvided,
	TimeBill.Hours,	
	TimeBill.Memo,
	TimeBill.SupervisorApproval
FROM
	Timesheet
	INNER JOIN TimeBill ON
		( TimeBill.Timesheet = Timesheet.ID )
WHERE
	( Timesheet.ID = ? )
	AND ( Timesheet.Employee = ? )

Notice that I'm joining from the Timesheet to the TimeBill table, and then filtering on both the Timesheet.ID and Timesheet.Employee columns. The reason that I'm doing that is because in the portal, when an employee selects a timesheet that they want to see the details for, the ID of the selected Timesheet is passed as a URL parameter. It would be very easy for an employee to change the value of the ID in the URL, and then see the the timesheet associated with another employee. Therefore, I'm also adding a filter on the Timesheet.Employee. The source of that value comes from the Web application's session state, so it's a value that employees cannot manipulate.

Also, notice that I'm using query parameters to specify the timesheet ID and employee ID. I'll write more about query parameters, including why they're important and why you should be using them, in a future post.

Here's an example of the results of that query.

Summarizing Time by Customer

Suppose that we wanted to summarize the timesheet details by customer. Here's a simple query to do that.

SELECT
	BUILTIN.DF( TimeBill.Customer ) AS Customer,		
	SUM( TimeBill.Hours ) AS Hours
FROM
	Timesheet
	INNER JOIN TimeBill ON
		( TimeBill.Timesheet = Timesheet.ID )
WHERE
	( Timesheet.ID = ? )
	AND ( Timesheet.Employee = ? )
GROUP BY
	BUILTIN.DF( TimeBill.customer )

The results look like this.

Wrapping Up

As you can see, you can do a lot with NetSuite's time tracking-related tables. You can modify the queries that I've shown in this post to provide reports to managers, to customers, and more. And just as I'm using them, you can provide users of external applications with this data as well.

If you have any questions about the queries that I've shared in this post, please let me know.