Before you start

To use Adhoc Export Format to export timesheets you'll need to have:

System Administrator or Location Manager level access to Deputy.
approved timesheets for your employees
some familiarity with JSON

Exporting your timesheets

1. On the Timesheets tab select Export Timesheets.

2. Use the Location and date selector to select the workplace and date range you would like to export approved timesheets for.

3. Select the employees' timesheets you would like to export.

4. Click Export Selected Items.

Web_template_-_2022-12-07T104430.736.png

5. Select Deputy Adhoc Programmable Export Format from the dropdown menu.

6. Enter your JSON commands in the field and name your export field

7. Click Export.

Web_template_-_2022-12-07T113415.455.png

JSON Command formats

{cmd : "echo_string" , string:"The string literal" } - output this string
{cmd : "run_script" , script: , params:} - run this script (e.g. generic,myob,group etc) with given params
{cmd: "get_raw_timesheets"} - returns the raw timesheets that are getting exported. Please note, it won't have the pay returns or period loading
{cmd : "get_pay_returns"} - for given timesheets, get their payreturns. It will be grouped by employee, rate name and total hours + dollars (columns EmployeeId, EmployeeName, EmployeeExportCode, RateName , RateExportCode , TotalHours, TotalCost)
{cmd : "columnunize" } - lastResult is columned into array. Header is used as column
{cmd : "group_rows" , keep_columns: , on_column: , operator: } - in lastResult, this will group each of the row keeping the keep_columns and applying operator on on_column
{cmd : "print_rows" , [line_sep: string] , [column_sep: string] , [column_enclosure:""] , columns : index of columnunize } - from lastresult
{cmd : "print_header" , [line_sep: string] , [column_sep: string] , [column_enclosure:""] , columns : [names of columns] } - prints to output
{cmd : "filter_rows" , column: value: } - lastResult will be filtered for column having value x
{cmd : "merge" , varname: mergecolumn: matchcolumn: } - lastResult will have this mergecolumn of varname merged into. On matching of varname.matchcolumn=varname.matchcolumn
{cmd : "merge_once" , varname: mergecolumn: matchcolumn: } - same as merge but it will only add it to the first occurrence
{cmd : "save_last_result" , varname: } - lastResult will saved into this varname
{cmd : "restore_last_result" , varname: } - lastResult will be restored to this value. Saved originally save_last_result
{cmd : "add_column" , value: , column: } - add a column into the lastResult with value
{cmd : "duplicate_column" , column: , new_column: } - add a column by duplicating column into the lastResult with value
{cmd : "apply_math" , column: , op: value: } - apply math operator to column with value. And save it in the same column. Value can be a column name as well (may be dangerous!)
{cmd : "apply_compare" , column: , op: value: } - apply compare operator to column with value. And save it in the same column. Value can be a column name as well (may be dangerous!)
{cmd : "replace_column" , column: , from: , to: } - in lastResult, replace given column index, for FROM to TO
{cmd : "sprintf_column" , column: , format:} - in lastResult, apply sprintf to column
{cmd : "apply_array" , column: , function: , args:} - in lastResult, apply array functions to the column (https://www.deputy.com/api-doc/Dexml_Scripts/Native_Functions -> Array). Function can be various php array functions. Args is an array of arguments.
{cmd : "apply_string" , column: , function: , args:} - in lastResult, apply string functions to the column (https://www.deputy.com/api-doc/Dexml_Scripts/Native_Functions -> String). Function can be various php string functions. Args is an array of arguments.
{cmd : "date_format_column" , column: , format:, companyid:(optional)} - in lastResult, apply date format to column, optionally supply location ID or __Field__ with Location ID to use that Location's time zone when printing date
{cmd : "apply_dexml" , dexml_proc: , column: , param : } - send the line to a dexml procedure, also apply param param, returned value will be in column
{cmd : "apply_rest" , url: , post: , pick_value: , column: } - make a deputy_rest call to url, post is a object here. It will get stringified and then __Field Names__ will be searched and replaced

Examples

Employee name, Timesheet Total Hours and Cost per employee

[ 
   { 
      "cmd":"get_raw_timesheets"
   },
   { 
      "cmd":"group_rows",
      "keep_columns":[ 
         "Employee"
      ],
      "on_column":[ 
         "Cost",
         "TotalTime"
      ],
      "operator":"+"
   },
   { 
      "cmd":"apply_rest",
      "url":"resource/Employee/QUERY",
      "post":{ 
         "search":{ 
            "esearchl":{ 
               "field":"Id",
               "type":"eq",
               "data":"__Employee__"
            }
         }
      },
      "pick_value":"DisplayName",
      "column":"DisplayName"
   },
   { 
      "cmd":"print_header",
      "columns":[ 
         "Employee Name",
         "TotalTime",
         "TotalCost"
      ],
      "column_sep":",",
      "column_enclosure":""
   },
   { 
      "cmd":"print_rows",
      "columns":[ 
         "DisplayName",
         "TotalTime",
         "Cost"
      ],
      "column_sep":",",
      "column_enclosure":""
   }
]

Allow Custom Fields to be Attached with Exports

[
  {
    "cmd": "get_raw_timesheets"
  },
  {  
       "cmd":"date_format_column",
       "column":"Date",
       "format":"Y-m-d"
    },
    {  
       "cmd":"date_format_column",
       "column":"StartTimeLocalized",
       "format":"H:i:s"
    },
    {  
       "cmd":"date_format_column",
       "column":"EndTimeLocalized",
       "format":"H:i:s"
    },
    {  
       "cmd":"date_format_column",
       "column":"Mealbreak",
       "format":"H:i:s"
    },
  {
    "cmd": "apply_rest",
    "url": "resource/Employee/QUERY",
    "post": {
      "search": {
        "esearchl": {
          "field": "Id",
          "type": "eq",
          "data": "__Employee__"
        }
      }
    },
    "pick_value": "CustomFieldData",
    "column": "CustomFieldData"
  },
    {  
       "cmd":"apply_rest",
       "url":"resource/Employee/QUERY",
       "post":{  
          "search":{  
             "esearchl":{  
                "field":"Id",
                "type":"eq",
                "data":"__Employee__"
             }
          }
       },
       "pick_value":"Contact",
       "column":"ContactId"
    },
    {  
       "cmd":"apply_rest",
       "url":"resource/Employee/QUERY",
       "post":{  
          "search":{  
             "esearchl":{  
                "field":"Id",
                "type":"eq",
                "data":"__Employee__"
             }
          }
       },
       "pick_value":"FirstName",
       "column":"FirstName"
    },
    {  
       "cmd":"apply_rest",
       "url":"resource/Employee/QUERY",
       "post":{  
          "search":{  
             "esearchl":{  
                "field":"Id",
                "type":"eq",
                "data":"__Employee__"
             }
          }
       },
       "pick_value":"LastName",
       "column":"LastName"
    },
    {  
       "cmd":"apply_rest",
       "url":"resource/Employee/QUERY",
       "post":{  
          "search":{  
             "esearchl":{  
                "field":"Id",
                "type":"eq",
                "data":"__Employee__"
             }
          }
       },
       "pick_value":"DisplayName",
       "column":"Display Name"
    },
    {  
       "cmd":"apply_rest",
       "url":"resource/Contact/QUERY",
       "post":{  
          "search":{  
             "esearchl":{  
                "field":"Id",
                "type":"eq",
                "data":"__ContactId__"
             }
          }
       },
       "pick_value":"Email1",
       "column":"Email"
    },
  {
    "cmd": "apply_rest",
    "url": "resource/CustomFieldData/QUERY",
    "post": {
      "search": {
        "esearchl": {
          "field": "Id",
          "type": "eq",
          "data": "__CustomFieldData__"
        }
      }
    },
    "pick_value": "CustomFieldAPI1",
    "column": "Sample API Field 1"
  },
  {
    "cmd": "apply_rest",
    "url": "resource/CustomFieldData/QUERY",
    "post": {
      "search": {
        "esearchl": {
          "field": "Id",
          "type": "eq",
          "data": "__CustomFieldData__"
        }
      }
    },
    "pick_value": "CustomFieldAPI2",
    "column": "Sample API Field 2"
  },
  {
    "cmd": "print_header",
    "columns": [
      "Id",
      "Employee",
      "Email",
      "Date",
      "StartTime",
      "EndTime",
      "Mealbreak",
      "TotalTime",
      "FirstName",
      "LastName",
      "Display Name",
      "Sample API Field 1",
      "Sample API Field 2"
    ],
    "column_sep": ",",
    "column_enclosure": ""
  },
  {
    "cmd": "print_rows",
    "columns": [
      "Id",
      "Employee",
      "Email",
      "Date",
      "StartTimeLocalized",
      "EndTimeLocalized",
      "Mealbreak",
      "TotalTime",
      "FirstName",
      "LastName",
      "Display Name",
      "Sample API Field 1",
      "Sample API Field 2"
    ],
    "column_sep": ",",
    "column_enclosure": ""
  }
]

Adding Email Address of the Employee with the CSV export

[  
    {  
       "cmd":"run_script",
       "script":"deputec_generic_tab_delimited",
       "params":{  
          "f":1
       }
    },
    {  
       "cmd":"columnunize"
    },
    {  
       "cmd":"duplicate_column",
       "new_column":"ISODate",
       "column":"Date"
    },
    {  
       "cmd":"date_format_column",
       "column":"ISODate",
       "format":"Y-m-d"
    },
    {  
       "cmd":"apply_rest",
       "url":"resource/Employee/QUERY",
       "post":{  
          "search":{  
             "esearchl":{  
                "field":"DisplayName",
                "type":"eq",
                "data":"__Employee Name__"
             }
          }
       },
       "pick_value":"Contact",
       "column":"ContactId"
    },
    {  
       "cmd":"apply_rest",
       "url":"resource/Contact/QUERY",
       "post":{  
          "search":{  
             "esearchl":{  
                "field":"Id",
                "type":"eq",
                "data":"__ContactId__"
             }
          }
       },
       "pick_value":"Email1",
       "column":"Email"
    },
    {  
       "cmd":"print_header",
       "columns":[  
          "Employee Export Code",
          "Employee Name",
          "Date",
          "Start",
          "End",
          "Mealbreak",
          "Total Hours",
          "Total Cost",
          "Employee Comment",
          "Area Export Code",
          "Area Name",
          "Location Code",
          "Location Name",
          "Email"
       ],
       "column_sep":",",
       "column_enclosure":""},{"cmd":"print_rows",
       "columns":[  
          "Employee Export Code",
          "Employee Name",
          "Date",
          "Start",
          "End",
          "Mealbreak",
          "Total Hours",
          "Total Cost",
          "Employee Comment",
          "Area Export Code",
          "Area Name",
          "Location Code",
          "Location Name",
          "Email"
       ],
       "column_sep":",",
       "column_enclosure":""      
    }
 ]

Extract a pay code called expense and adding it along with overtime hours over 40 hours

[  
   {  
      "cmd":"get_pay_returns"
   },
   {  
      "cmd":"filter_rows",
      "column":"RateName",
      "value":"Expenses"
   },
   {  
      "cmd":"duplicate_column",
      "column":"EmployeeExportCode",
      "new_column":"File #"
   },
   {  
      "cmd":"duplicate_column",
      "column":"TotalCost",
      "new_column":"Expenses"
   },
   {  
      "cmd":"save_last_result",
      "varname":"Expenses"
   },
   {  
      "cmd":"run_script",
      "script":"deputec_adp_us",
      "params":{  
         "arrAdditionalDetails":{  
            "values":"mark40"
         }
      }
   },
   {  
      "cmd":"columnunize"
   },
   {  
      "cmd":"merge",
      "varname":"Expenses",
      "mergecolumn":"Expenses",
      "matchcolumn":"File #"
   },
   {  
      "cmd":"add_column",
      "value":"0",
      "column":"Zero"
   },
   {  
      "cmd":"add_column",
      "value":"0",
      "column":"Holiday"
   },
   {  
      "cmd":"apply_math",
      "column":"First Name",
      "value":" ",
      "op":"&"
   },
   {  
      "cmd":"apply_math",
      "column":"First Name",
      "value":"Last Name",
      "op":"&"
   },
   {  
      "cmd":"duplicate_column",
      "column":"Rate 1",
      "new_column":"OTRate"
   },
   {  
      "cmd":"apply_math",
      "column":"OTRate",
      "op":"*",
      "value":"1.5"
   },
   {  
      "cmd":"print_header",
      "columns":[  
         "Employee ID",
         "Employee Name",
         "Dept Code",
         "Rate",
         "Regular Hours",
         "OT Hours",
         "OT Rate",
         "Other",
         "Exps",
         "Holiday",
         "Holiday Rate"
      ],
      "column_sep":",",
      "column_enclosure":"      "
   },
   {  
      "cmd":"print_rows",
      "columns":[  
         "File #",
         "First Name",
         "Home Dept",
         "Rate 1",
         "Reg Hours",
         "O/T Hours",
         "OTRate",
         "Zero",
         "Expenses",
         "Zero",
         "Rate 1"
      ],
      "column_sep":",",
      "column_enclosure":"     "
   }
]

Show scheduled hours along with total worked hours

[  
    {  
       "cmd":"run_script",
       "script":"deputec_generic_tab_delimited",
       "params":{  
          "f":1
       }
    },
    {  
       "cmd":"columnunize"
    },
    {  
       "cmd":"duplicate_column",
       "new_column":"ISODate",
       "column":"Date"
    },
    {  
       "cmd":"date_format_column",
       "column":"ISODate",
       "format":"Y-m-d"
    },
    {  
       "cmd":"apply_rest",
       "url":"resource/Roster/QUERY",
       "post":{  
          "aggr":{  
             "TotalTime":"sum"
          },
          "search":{  
             "Date":"__ISODate__",
             "esearchl":{  
                "field":"DisplayName",
                "type":"eq",
                "data":"__Employee Name__",
                "join":"EmployeeObject"
             }
          }
       },
       "pick_value":"TotalTime_sum",
       "column":"Scheduled Hours"
    },
    {  
       "cmd":"duplicate_column",
       "new_column":"OvertimeTBC",
       "column":"Total Hours"
    },
    {  
       "cmd":"apply_math",
       "op":"-",
       "value":"Scheduled Hours",
       "column":"OvertimeTBC"
    },
    {  
       "cmd":"duplicate_column",
       "new_column":"Overtime",
       "column":"OvertimeTBC"
    },
    {  
       "cmd":"apply_compare",
       "op":"gt",
       "value":"0",
       "column":"Overtime"
    },
    {  
       "cmd":"apply_math",
       "op":"*",
       "value":"OvertimeTBC",
       "column":"Overtime"
    },
    {  
       "cmd":"print_header",
       "columns":[  
          "Employee Name",
          "Date",
          "Start",
          "End",
          "Mealbreak",
          "Scheduled Hours",
          "Worked Hours",
          "Location Name",
          "Leave"
       ],
       "column_sep":",",
       "column_enclosure":"      "},{"      cmd":"print_rows",
       "columns":[  
          "Employee Name",
          "Date",
          "Start",
          "End",
          "Mealbreak",
          "Scheduled Hours",
          "Total Hours",
          "Location Name",
          "Leave"
       ],
       "column_sep":",",
       "column_enclosure":"      "
    }
 ]

Timesheets with location and actual meal break clock in/out times

[{
	"cmd": "run_script",
	"script": "deputec_generic_tab_delimited",
	"params": {
		"f": 1
	}
}, {
	"cmd": "columnunize"
}, {
	"cmd": "apply_rest",
	"url": "resource/Company/QUERY",
	"post": {
		"search": {
			"esearchl": {
				"field": "CompanyName",
				"type": "eq",
				"data": "__Location Name__"
			}
		}
	},
	"pick_value": "Id",
	"column": "Company Id"
}, {
	"cmd": "duplicate_column",
	"new_column": "ISODate",
	"column": "Date"
}, {
	"cmd": "date_format_column",
	"column": "ISODate",
	"format": "Y-m-d",
	"companyid": "__Company Id__"
}, {
	"cmd": "duplicate_column",
	"new_column": "StartDateTime",
	"column": "Date"
}, {
	"cmd": "duplicate_column",
	"new_column": "EndDateTime",
	"column": "Date"
}, {
	"cmd": "duplicate_column",
	"new_column": "StartTime",
	"column": "Start"
}, {
	"cmd": "duplicate_column",
	"new_column": "EndTime",
	"column": "End"
}, {
	"cmd": "date_format_column",
	"column": "StartTime",
	"format": "H:i:s",
	"companyid": "__Company Id__"
}, {
	"cmd": "date_format_column",
	"column": "EndTime",
	"format": "H:i:s",
	"companyid": "__Company Id__"
}, {
	"cmd": "apply_math",
	"column": "StartDateTime",
	"op": "&",
	"value": " "
}, {
	"cmd": "apply_math",
	"column": "StartDateTime",
	"op": "&",
	"value": "StartTime"
}, {
	"cmd": "apply_math",
	"column": "EndDateTime",
	"op": "&",
	"value": " "
}, {
	"cmd": "apply_math",
	"column": "EndDateTime",
	"op": "&",
	"value": "EndTime"
}, {
	"cmd": "date_format_column",
	"column": "StartDateTime",
	"format": "U",
	"companyid": "__Company Id__"
}, {
	"cmd": "date_format_column",
	"column": "EndDateTime",
	"format": "U",
	"companyid": "__Company Id__"
}, {
	"cmd": "apply_rest",
	"url": "resource/Employee/QUERY",
	"post": {
		"search": {
			"esearchl": {
				"field": "DisplayName",
				"type": "eq",
				"data": "__Employee Name__"
			}
		}
	},
	"pick_value": "Id",
	"column": "Employee Id"
}, {
	"cmd": "apply_rest",
	"url": "resource/Timesheet/QUERY",
	"post": {
		"search": {
			"Employee": "__Employee Id__",
			"StartTime": "__StartDateTime__",
			"EndTime": "__EndDateTime__"
		}
	},
	"pick_value": "MealbreakSlots",
	"column": "MealbreakSlots"
}, {
	"cmd": "duplicate_column",
	"new_column": "Mealbreak Start",
	"column": "MealbreakSlots"
}, {
	"cmd": "duplicate_column",
	"new_column": "Mealbreak End",
	"column": "MealbreakSlots"
}, {
	"cmd": "apply_array",
	"function": "array_keys",
	"column": "Mealbreak Start"
}, {
	"cmd": "apply_array",
	"function": "array_keys",
	"column": "Mealbreak End"
}, {
	"cmd": "apply_array",
	"function": "array_slice",
	"column": "Mealbreak Start",
	"args": [0, 1]
}, {
	"cmd": "apply_array",
	"function": "array_slice",
	"column": "Mealbreak End",
	"args": [1, 2]
}, {
	"cmd": "apply_array",
	"function": "implode",
	"column": "Mealbreak Start",
	"args": [","]
}, {
	"cmd": "apply_array",
	"function": "implode",
	"column": "Mealbreak End",
	"args": [","]
}, {
	"cmd": "date_format_column",
	"column": "Mealbreak Start",
	"format": "H:i"
}, {
	"cmd": "date_format_column",
	"column": "Mealbreak End",
	"format": "H:i"
}, {
	"cmd": "duplicate_column",
	"new_column": "MealbreakHrs",
	"column": "Mealbreak"
}, {
	"cmd": "duplicate_column",
	"new_column": "MealbreakMins",
	"column": "Mealbreak"
}, {
	"cmd": "date_format_column",
	"column": "MealbreakHrs",
	"format": "H"
}, {
	"cmd": "date_format_column",
	"column": "MealbreakMins",
	"format": "i"
}, {
	"cmd": "add_column",
	"value": 0,
	"column": "MealbreakInMins"
}, {
	"cmd": "apply_math",
	"column": "MealbreakInMins",
	"op": "+",
	"value": "MealbreakMins"
}, {
	"cmd": "apply_math",
	"column": "MealbreakHrs",
	"op": "*",
	"value": 60
}, {
	"cmd": "apply_math",
	"column": "MealbreakInMins",
	"op": "+",
	"value": "MealbreakHrs"
}, {
	"cmd": "print_header",
	"columns": ["First Name", "Last Name", "Employee Export Code", "Location Name", "Date", "Time In", "Mealbreak Start", "Mealbreak End", "Time Out", "Mealbreak Length", "Leave Type"],
	"column_sep": ",",
	"column_enclosure": ""
}, {
	"cmd": "print_rows ",
	"columns": ["Firstname", "Lastname", "Employee Export Code", "Location Name", "Date", "Start", "Mealbreak Start", "Mealbreak End", "End", "MealbreakInMins", "Leave"],
	"column_sep": ",",
	"column_enclosure": "\""
}]

Timesheets with longitude and latitude of clock in/out

[{
"cmd": "get_raw_timesheets"
},
{
"cmd": "date_format_column",
"column": "Date",
"format": "D d-M-y"
},
{
"cmd": "date_format_column",
"column": "StartTime",
"format": "H:i"
},
{
"cmd": "date_format_column",
"column": "EndTime",
"format": "H:i"
},
{
"cmd": "apply_rest",
"url": "resource/Employee/QUERY",
"post": {
"search": {
"esearchl": {
"field": "Id",
"type": "eq",
"data": "__Employee__"
}
}
},
"pick_value": "DisplayName",
"column": "DisplayName"
},
{
"cmd": "apply_rest",
"url": "resource/OperationalUnit/QUERY",
"post": {
"search": {
"esearchl": {
"field": "Id",
"type": "eq",
"data": "__OperationalUnit__"
}
}
},
"pick_value": "OperationalUnitName",
"column": "Area"
},
{
"cmd": "apply_rest",
"url": "resource/Geo/QUERY",
"post": {
"search": {
"esearch1": {
"field": "RecId",
"type": "eq",
"data": "__Id__"
},
"esearch2": {
"field": "Orm",
"type": "eq",
"data": "DeputecTimesheet"
}
},
"sort": {
"Id": "asc"
},
"max": 1
},
"pick_value": "Longitude",
"column": "Longitude-Clock In"
},
{
"cmd": "apply_rest",
"url": "resource/Geo/QUERY",
"post": {
"search": {
"esearch1": {
"field": "RecId",
"type": "eq",
"data": "__Id__"
},
"esearch2": {
"field": "Orm",
"type": "eq",
"data": "DeputecTimesheet"
}
},
"sort": {
"Id": "asc"
},
"max": 1
},
"pick_value": "Latitude",
"column": "Latitude-Clock In"
},
{
"cmd": "apply_rest",
"url": "resource/Geo/QUERY",
"post": {
"search": {
"esearch1": {
"field": "RecId",
"type": "eq",
"data": "__Id__"
},
"esearch2": {
"field": "Orm",
"type": "eq",
"data": "DeputecTimesheet"
}
},
"sort": {
"Id": "desc"
},
"max": 1
},
"pick_value": "Longitude",
"column": "Longitude-Clock Out"
},
{
"cmd": "apply_rest",
"url": "resource/Geo/QUERY",
"post": {
"search": {
"esearch1": {
"field": "RecId",
"type": "eq",
"data": "__Id__"
},
"esearch2": {
"field": "Orm",
"type": "eq",
"data": "DeputecTimesheet"
}
},
"sort": {
"Id": "desc"
},
"max": 1
},
"pick_value": "Latitude",
"column": "Latitude-Clock Out"
},
{
"cmd": "print_header",
"columns": [
"Employee Name",
"Date",
"Start Time",
"End Time",
"Timesheet Id",
"Area name",
"Clock In Longitude",
"Clock In Latitude",
"Clock Out Longitude",
"Clock Out Latitude"
],
"column_sep": ",",
"column_enclosure": ""
},
{
"cmd": "print_rows",
"columns": [
"DisplayName",
"Date",
"StartTime",
"EndTime",
"Id",
"Area",
"Longitude-Clock In",
"Latitude-Clock In",
"Longitude-Clock Out",
"Latitude-Clock Out"
],
"column_sep": ",",
"column_enclosure": ""
}
]