You can import Time+Expense items from a Spreadsheet in csv format and optionally automatically create reports and submit them. You can also send or forward the Time+Expense file via email (e.g. Uber trip reports). Check for import details
Import T+E Items
In the Welcome Tab, click on Spreadsheet / Import icon
- Select the CSV file to be imported
as User/Resource is derived if not part of the import file.
You can use the import also for updating (correcting) existing items as long as they are not approved.
To view your imported records, you may have to adjust the default T&E Date in the Timesheet or Expense tab.
Resource, Date, Hours Charlie Chatter, 10/12/2016, 3 Paul Platform, 2016-10-12, 3.1 Sue Standard, 12.10.2016, 3.2
Note that the sequence of the columns does not matter, the header is case insensitive and could also be the column names "resource__c, te_date__c, hours__c"
Before importing, you can select Import Preview to view and modify the records would be imported. Here, you can also select values for missing or incorrect file input. The Import Details section lists for every row how the values were derived.
Note that the preview is very resource intensive, and if errors occur, you should reduce the size of the import file to 100-200 records.
Create T+E Reports
You can also create and submit T+E Report(s) automatically if the import is successful.
- Select "Create T+E Report"
- optionally select "by Project"
- optionally select "Submit Report for approval"
- choose to "Delete records on Report creation failure" - deletes the imported records to allow to fix the issue and re-import.
If successful, you can click on the T+E Report names to view the records.
Note that if you import T+E Items for others, you need to have the r/w sharing access for T+E Item and Report for that user or Modify All access.
When you submit the report, you can automatically approve the report, e.g. based on the following criteria:
- T+E Report: Report Type = I (for import)
- Match on parts of the file name (T+E Report field Import Source)
- Some other criteria to prevent accidental approval when "normal" users use this functionality, e.g. Submitter
Hints and Tips
All reference entities (e.g. project, tax, account) must already exist
- Import creates new T&E items if the T&E Item column is empty (or not included)
- If a T&E report is referenced it must have a status of 'New' or 'Not Approved'
- Add attachments via email or in the T&E items related list after import
- The file format must be .csv
- If the record owner is not defined in the file, it will be the user who imported the records. If you import the OwnerId, the Resource is derived and vice versa. It is more efficient to import the Resource.
- If using Field Labels, they must be enclosed in quotes if they contain spaces. (e.g. "Fin Account")
- Do not import the x* and d* columns - they are used for timesheet display only
- The Project column accepts the following:
- Project Code or Project Name, e.g. myProject
- Project Code/Name and Project Line Name/WBS separated by colon and a space ": ", e.g. myProject: myLine
- For Projects, the line status can be Completed (as there may be delays).
- If an external reference is used, it must be unique. If a record with that external reference exists, that record is updated (unless approved)
The Record Owner is derived:
- ownerid - if specified
- resource__c - if there is a user defined on the record
- the importing user
Check the Import Details section - it lists how the file was processed (e.g. all columns found/matched) and errors
File name: imp.csv File size: 109 Using Handler: Base Parameter: Parsed: lines=4 rows=3 Columns: accorto__resource__c, accorto__te_date__c, accorto__hours__c, Row #1: resource=Charlie Chatter date=10/12/2016 hours=3 ...
Check the "Columns" line for missing, not mapped columns
The csv file must have the label or name of the column in the first line; The columns (which represent the fields) and the order of the columns in the file are user defined (i.e. arbitrary). For advanced use (overwriting header line, stop line) see the general Import description (use the Preview to validate the settings).
|Column Name||Field Label||Value|
|account__c||Account||Customer Account Name|
|contact__c||Contact||Last Name or Phone|
|description__c||Description||- any text -|
|expendituretype__c||Expenditure Type||Expenditure Type Name|
|external_id__c||External Reference||- any text - unique!|
|financial_account__c||Fin Account||Financial Account Code|
|financial_organization__c||Fin Org||Financial Organization Name|
|hours__c||Hours||Hours (decimal) number|
|minutes__c||Minutes||Alternative to importing Hours|
|isinvoiced__c||Billable||Y, N, true, false|
|iswaiting__c||Waiting for Details||Y, N, true, false|
|ownerid||Owner Id||Full username or email address|
|project__c||Project||name or code or combined (see above)|
|project_phase__c||Project Phase||phase name or WBS|
|project_line__c||Project Line||line name or WBS|
|reason__c||Reason||- any text -|
|receipt_amount__c||Receipt Amount||(decimal) number|
|receipt_quantity__c||Receipt Quantity||(decimal) number (e.g. mileage)|
|reimbursable__c||Receipt Reimbursable||'Yes' (default) , 'No'|
|resource__c||Resource||Resource Name or Code|
|tax_amount__c||Tax Amount||(decimal) number|
|tax__c||Tax|| Tax Name|
|te_date__c||T&E Date or Date||Date formatted: mm/dd/yyyy or dd.MM.yyyy|
or yyyy-MM-dd HH:mm:ss in local time
|te_report__c||T+E Report||existing T+E Report Name|
|name||T+E Item||existing T+E Item Name for updates|
Note, do not import the expense amount as that is the receipt amount converted to the currency of the organization and calculated automatically (multi currency). Similar the document amount is the receipt amount converted to the currency of reimbursement (dual currency).