Before You Start

Before you get started with Adhoc Export Format, you'll need to have approved timesheets for your employees. You can see how it works here. Please know that to make best use of this export format you will need some familiarity with JSON. If you need a hand please get in touch with us and our team will be happy to help.

Exporting Your Timesheets

Please navigate to the Timesheets tab at the top of your screen and select "Export Time Sheets". Select the date range for the export from the date selector at the top of screen. The system will display all approved timesheets from your selected location(s) and date range.

Please select the employees to export on the left hand side. To quickly select all employees click "Select All". The details will then be displayed on the right hand side. If you need to edit an item, please click on the item in the list to the right and select "View or edit original item". Once you are ready to export, please click "Export Selected Items". You'll see a box asking you to select a payroll application. Please select "Deputy Adhoc Programmable Export Format".

Next, please give the export file a name and enter your JSON commands in the box. If you have any questions, please get in touch with us and we can help.

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_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 occurance
  • {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 timezone 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

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 Mealbreak 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": "\""
}]

Did this answer your question?