Document Summary
Definitions of upload messages
2. Delimiter Issue / Failure To Use Quotes
4. Invalid Client Contact Email
6. No Data Provided for Connected Business
7. Another file being processed at the same time
Appendix
1. Additional Data File Resources
2. Automating Data File Transfers
Document Summary
This document will provide further information to common issues users may encounter when running the extract, transform, and load (ETL) or data file upload (DFU) process when providing data through the Apperio platform. To quickly identify and troubleshoot file errors, click on the ‘Quick Fix’ sections in the table of contents above.
Apperio Process
For vendors using the DFU method to provide data to their client, Apperio will provide a SQL query to be run against the firm’s internal timekeeping system to create a data file with line entry-level information which can then be uploaded to the platform. This can be done manually by dragging-and-dropping files into the Upload Data page on the platform, or on an automated basis using SFTP/FTPS. For more information on how to set up an automated feed, please refer to the Appendix.
What is file fixing?
File fixing is the process of diagnosing and resolving issues file uploads. As part of the client request, timely and accurate data uploads to the Apperio platform are essential and will help prevent undue delays to invoice processing and payment.
Only the first error that the validation checks identify is displayed. Therefore, steps one and two may need to be repeated until all the errors have been either fixed or noted to advise the law firm of their changes.
Tools Required
- Microsoft Excel
- Apperio
- VisualStudio Code (or other text editors and IDEs that can be used to view and edit CSV files)
VS code and other IDEs can be more useful in cases where the formatting of the comma-separated line items may be the source of the issue. As Excel will often automatically re-format the file into columns as well as change the date format, viewing a file in VS code allows you to review the data in its original format.
Once the errors have been corrected manually, the new file can be uploaded manually for testing. Log on to Apperio and navigate to the Upload Data page. Drag and drop the file to start the upload process.
If the file uploads successfully, proceed with making the associated changes within the SQL query to ensure the issue will not arise again for future uploads. If the file continues to fail, fix the new error and repeat the file testing process iteratively. For a detailed breakdown of how to resolve each error, see the Troubleshooting Common Errors section.
Definitions of upload messages
After uploading a file, the Upload Data page will display a number of messages to indicate how the file is processed on the backend. The three messages you most commonly see are:
| Uploading… | Validating… | Processing… |
|
The initial message given when a file is uploaded. The system is uploading and transferring the file to Apperio databases.
|
This message indicates that the system is checking the file against Apperio’s validation rules. |
This message indicates that the system has not identified any errors and that the uploading process is complete.
|
Troubleshooting Common Errors
There’s a range of errors that can come up when uploading a data file to Apperio. These errors can be broadly categorised into:
Codec Issues
These errors could indicate several issues preventing the platform from being able to read the file properly.
Example invalid codec error message.
Frequently, this error message could mean that there is a:
Wrong Header
Check the header row containing all the field names to ensure that they are formatted correctly. While the system will accept underscores (e.g. “Matter_ID” and “Matter ID” are both acceptable), other types of formatting may generate an invalid codec error. Additionally, there may be additional non-Apperio fields added to the file by the firm.
| Example header issue error message. |
Quick Fix |
Copy the header row of the last successful file upload and paste the header in place of the failing file’s header. Re-upload manually to test the file. |
Notes |
Examine the file for special characters (e.g. non-breaking space characters), additional commas, line breaks, and incorrectly titled fields (e.g. “Billed Amount” instead of “Amount Billed”). Values within the Time Entry Narratives field are prone to formatting errors. |
Solution |
Refer to the Data File Uploader Guide or template query (see Appendix) shared with the firm to confirm the exact naming of the field names. Update the erroneous field name to match. |
For newly connecting firms, please consult the Data File Uploader Guide and or the DFU Time Entry example file (links in the Resources section below) to see how the columns in the data file should be ordered and labelled.
Delimiter Issue / Failure To Use Quotes
As the format is comma-separated, failing to quote text or strings or text that contain commas will cause the data file to fail.
| Example delimiter issue error message. |
Quick Fix |
Excel should automatically delimit .csv files upon opening, and an indication of the fields being delimited incorrectly can look like: Narratives running into different fields Pre-set values (e.g. currency codes, dates) in incorrect fields Data from single time entry split into multiple rows Additional entries past the Amount Billed column |
Notes |
Examine the file for special characters (e.g. non-breaking space characters), additional commas and line breaks. Values within the Time Entry Narratives field are prone to formatting errors. |
Solution |
Ensure the following export parameters are used when generating the data file: Change Text Qualifier to double quotation marks Change Delimiter to commas Remove double quotation marks from the query (to prevent multiple double quotation marks within the data file) For detailed instructions, please refer to this video (password: Apperio123). We recommend using Microsoft SQL Server to run the export. |
Where a file is not automatically delimited, use the Text to Column function within Excel and select ‘Comma’ as the separator.
Formatting Errors
Wrong Date Format
Dates must be in the ISO format - "YYYY-MM-DD" such as "2017-12-10".
| Example date formatting error message. |
Quick Fix |
Reformat date values using Excel by: Selecting the date columns → Format Cells → Date → Selecting YYYY-MM-DD. |
Notes |
Where dates are recorded internally in YYYY/DD/MM formats, ensure that the months and days are being recognised correctly. To manually convert US date formats to the Apperio-accepted format, use the Text to Columns function in Excel to format YYYY/DD/MM dates into the correct YYYY-MM-DD format. |
Solution |
Use CAST() or CONVERT() to format all date fields as YYYY-MM-DD in the output. Refer to the Data File Uploader Guide for the list of date fields in the Apperio data file. |
Beware that when files are opened in Excel, dates are automatically converted to “DD/MM/YYYY” format and must be changed back to ISO format before being uploaded to Apperio.
Wrong Number Format
| Example date formatting error message. |
Quick Fix |
Reformat number values using Excel by: Selecting the number columns → Format Cells → Number → Enter 4 in the Decimal places field. Beware that Excel will automatically remove leading zeroes and decimals. |
Notes |
Where 1000 separators are used, ensure commas are used and the numbers are encapsulated in double quotations. For European firms, please take special note of when comma separators are used and ensure they are converted to decimals. |
Solution |
Numbers should ideally be stored as numeric types (e.g. DECIMAL, FLOAT, etc.) Use the REPLACE() function along with CAST() or CONVERT() to format all number fields to a maximum of 4 significant figures. |
Data Validation Errors
When data in a data file is being validated, firms will commonly encounter issues with data consistency. There are certain requirements for data fields that are contingent on whether other fields have been provided.
Missing Required Fields
| Example missing required fields message. |
Quick Fix |
Check if there are other entries with similar information you can use. Using the above error as an example, check for other entries with the Matter Work Type ID “442” and see if an associated Matter Work Type has been provided (e.g. “Mergers & Acquisitions”). Then, you can add the work type name to the fields missing the Matter Work Type data. |
Notes |
As a Matter Work Type must be provided if a Matter Work Type ID has been provided (and vice versa), the system will flag an error if it identifies an entry where the Matter Work Type ID has been populated and the Matter Work Type has not. This applies to all fields in the DFU Guide where it states “Required if X field is set”. |
Solution |
Check if the missing information is not pulling through for a specific group of entries (e.g. certain matters, clients, etc.). Where data is missing, this will typically require updates to the matters, clients, etc. within the firm’s PMS. |
Multiple Associations
| Example multiple associations error message. |
Quick Fix |
Using the above error as an example, a matter’s high level information must be consistent across all entries recorded against the matter (i.e. all entries against the 230410 matter must either have the ‘open’ status or the ‘closed’ status, not both). |
Notes |
The Apperio data file is organised from least to most granular levels of data, increasing with specificity from General > Client > Matter > Entry-level information (e.g. Field 1 is “Entry Type” > “Client ID” > “Matter ID” > “Entry ID”). When validating the file, Apperio will check to make sure that each level of data provided is consistent across the entries provided. |
Solution |
As the SQL query provided by Apperio is a template, the coding (e.g. “op” = “open” for Matter Status) may not accurately capture all internal codes used at the firm. Refer to the Data File Uploader Guide for a list of acceptable values within each field. |
- Beware that opening a file in Excel may cause this issue unintentionally by reformatting data fields like Matter IDs (particularly IDs beginning with 0) and removing decimal points (some firms will use Client ID + Matter ID as the full matter reference). It’s recommended that you check this error using an IDE (e.g. VS Code) first.
Field Too Long
| Example data validation error message. |
Quick Fix |
Reformat number values using Excel by: Selecting the number columns → Format Cells → Number → Enter 4 in the Decimal places field. Beware that Excel will automatically remove leading zeroes and decimals. |
Notes |
Apperio imposes limits on the length of entries in certain columns to identify potential errors. In this instance, the Amount Billed column has a maximum length of 20 to protect against accidentally inflated entries and or currency figures with more than 2 decimal places. Note that number fields can have a maximum of 4 decimal places. |
Solution |
Either use ROUND() or CAST()to ensure number fields are limited to 4 decimal places. |
Invalid Client Contact Email
Apperio requires the Client Contact Email field to be populated only by standard email address characters.
| Example invalid client contact email error message. |
Quick Fix |
Check for the list of emails provided in the Client Contact Email field. Entries within this field must be in the “xxx@business.com” format. Ensure there are no trailing spaces after the email address. It is recommended that all email addresses are encapsulated by double quotation marks. |
Notes |
Where email addresses are unavailable, leave the entry blank instead of providing a NULL value. Where multiple email addresses are stored against a matter, the system will flag any separators (e.g. “john.doe@business.com; jane.smith@business.com”. We recommend only providing one client contact per matter to ensure the file will pass validation. If you’re unsure of which contact should be listed, please reach out to the client point of contact for confirmation. |
Solution |
Use LTRIM(RTRIM())to remove leading and trailing spaces when incorporating email addresses. |
Version Value Error
For firms using versions (i.e. splitting out data into different files), please ensure that the version name corresponds to the pre-set version value configured. For more information on versioning, refer to the Guide to Versioning.
| Example invalid version error message. |
Quick Fix |
Reach out to operations@apperio.com to confirm the Version value set up at the time of onboarding. |
Notes |
Please note the Version value is case-sensitive. Ensure that the Version value for each entry matches the configured value (e.g. “Client Name” and not “client name”). |
Solution |
The Apperio team can update the accepted Version value on the platform. Alternatively, if the value is hardcoded, update the Version value in the SQL Query (e.g. ’Client Name’[Version]). |
No Data Provided for Connected Business
The Apperio system only considers the most recent DFU from each firm, and so will overwrite all previous DFUs when a new file is uploaded. Therefore, firms must ensure all the required data for each connected client is contained in DFU (unless the firm versions).
| Example no data provided error message. |
Quick Fix |
Ensure the Client ID provided in the data file matches the Client ID provided in previous data file uploads. You can cross-check the client IDs and entities included within the data file against what has previously been ingested by navigating to the Clients page on Apperio. |
Notes |
Where a Client ID has been removed in error or formatted differently (e.g. ‘001234’ and ‘1234’), the system will assume data pertaining to the Client ID has been removed.
Ensure the data file contains the data for all connected clients. Where data has been consolidated (e.g. after a timekeeping system change), please notify the Apperio team to confirm the accuracy of the data being provided. |
Solution |
Encapsulate the Client IDs provided in the data file in double quotation marks. Ensure the following export parameters are used when generating the data file: Change Text Qualifier to double quotation marks Change Delimiter to commas Remove double quotation marks from the query (to prevent multiple double quotation marks within the data file) For detailed instructions, please refer to this video (password: Apperio123). We recommend using Microsoft SQL Server to run the export. |
In certain instances, where all entries have been removed (or where all entries except for the header row have been removed), the system will display an alternate error message. This could indicate a change in database paths as specified within the SQL query or changes in user credentials for firms using an automated feed.
Another file being processed at the same time
Where a file is uploaded successfully but gets stuck in processing due to a variety of factors, subsequent uploads are blocked from processing. By default, Apperio fails any file uploads that gets stuck in the “Processing” status for over 12 hours to ensure a manual re-upload can be attempted.
If the error persists, contact Apperio Support and a member of the team will be able to manually fail the file stuck in processing.
8. Unexpected error
This is typically caused by the data file having empty rows. Ensure that all rows are filled with relevant data, otherwise delete the rows and re-upload.
Additional Data File Resources
- DFU Time Entry Example File
- Apperio Guide to Versioning
- VS Code download link
- Data File Uploader Guide (accessible to Apperio users in the Upload Data page)
2. Automating Data File Transfers
- Uploading to Apperio using FTPS_SFTP.pdf
- Automating Apperio using our Uploader .pdf
- How to run the query and export the file (password: Apperio123)
- How to generate an API token for automation - Please note that the token can only be viewed once once it's been generated
- How to automate uploads using the Apperio uploader
If this document hasn’t addressed your issue or if you have feedback of any kind, please reach out to support@apperio.com.