Working with the Deputy API

In this post, I am going to show an example of how to pull Timesheets from Deputy using their API. The specific call I will use is a Post to QUERY (documentation can be found here). The code shown is written in C# and the app created for this use is a Microsoft .NET Console application (using the standard .NET Framework not .NET Core).

The basic steps of the console application are:

  • Perform an Aggregation Query against Deputy for the Timesheets within a Date Range to find the total number of Timesheets needing to be processed.
  • Using a loop, call Deputy to get the individual Timesheets (500 at a time)
  • Process each Timesheet returned, comparing the data to Wolfpack

The body of the post to Deputy to perform the aggregation query is shown below. We are looking for Timesheets that have a Date field greater than or equal to July 1, 2019 and less than July 10, 2019.

                            { "search" : { "s1" : { "field": "Date" , "type" : "ge" , "data": "2019-07-01" },
                            "s2" : { "field": "Date" , "type" : "lt" , "data": "2019-07-10" } },
                            "aggr" : { "Id" : "count" } }

The interesting thing is the response (not shown due to confidentiality) will return a JSON object. In this case, that JSON object will have a structure like a Timesheet. I expected ONLY the aggregation column to come back. That is not how it works. So, how do you access the aggregation information?

The final field in the JSON reponse as part of the JSON object will contain the aggregation. The aggregation field is specifically named. For example, in the above query, the aggregation is a Count of the Timesheet Id field. This aggregation will occur in the return response as "Id_count".

Although I did not test it, one would expect that any additional aggregations would show similar naming conventions, say I was trying to perform a sum of a Timesheet query's Cost field. That might come back as "Cost_sum".

Once we have identified the total number of Timesheets that we will retrieve and process, the next step is to use the Deputy API to start pulling Timesheets. The query below pulls the Timesheets from Deputy using the same date range. In addition, the "sort" JSON defines that data will be returned in order by Timesheet Id and the "start" JSON defines how many records to skip before retrieving Timesheets.

                            { "search" : { "s1" : { "field": "Date" , "type" : "ge" , "data": "2019-07-01" },
                            "s2" : { "field": "Date" , "type" : "lt" , "data": "2019-07-05" } },
                            "sort" : { "Id" : "asc" },
                            "start" : {SkipRecs} }

Why do we need "start"?

We need "start" because the Deputy API limits query retrievals to 500 objects.

Unless you are certain that you will retrieve <500 records in your Deputy API query, you will need to structure your code to keep track of where you are in the indexed set of records so that you query for the next set.

Finally, the C# code shown below represents part of the class I built to generalize the Deputy API calls. It relies on both the Newtonsoft and RestSharp .NET Framework libraries to accomplish its' task.

                            public string PostDeputy(string apiCall, string jsonQry)
                            var client = GetClient(apiCall);
                            var request = GetRequest(Method.POST);
                            request.RequestFormat = DataFormat.Json;
                            IRestResponse response = client.Execute(request);
                            return response.Content;

                            private RestClient GetClient(string apiCall)
                            string apiUrl = this.apiurl + apiCall;
                            var client = new RestClient(apiUrl);
                            return client;

                            private RestRequest GetRequest(RestSharp.Method restCallType)
                            var request = new RestRequest(restCallType);
                            request.AddHeader("cache-control", "no-cache");
                            request.AddHeader("Connection", "keep-alive");
                            request.AddHeader("accept-encoding", "gzip, deflate");
                            request.AddHeader("Host", host);
                            request.AddHeader("Cache-Control", "no-cache");
                            request.AddHeader("Accept", "*/*");
                            request.AddHeader("Authorization", accesstoken);
                            return request;

Hopefully, this post has provided some insight on how to deal with the Deputy API using Microsoft's .NET Framework and C#. Although I haven't provided the entire code, the above should help move your code in a positive direction.