Overview
Tesorio supports Flat file data integration. Our Tesorio Connect flat file integration is incredibly flexible and populates data into our AR Automation and Collections products as well as our Cash Flow Management product. Tesorio provides you with file templates you can start populating as soon as your Tesorio account is created. Additional data points can be added to your files as available native or custom fields. See the templates and data schema below for details.
This document describes the object schema and the file format required for an optimal implementation that can set up your team in Tesorio quickly.
To ensure Tesorio has all the up-to-date data needed for your team, Tesorio requires that you have an automated mechanism to export your ERP data in CSV format and an automated way for SFTP transfers. We require at least one daily upload.
More frequent file uploads are picked up for processing every hour at the top of the hour.
If you have multiple ERPs, don't worry, our Customer Success team will work with you to ensure the appropriate data schema is defined.
Frequency of File Uploads
Tesorio ingests new files via SFTP every hour, at the top of the hour.
The frequency of updates is determined by your upload frequency. Tesorio highly recommends daily file uploads to keep your data up to date.
đĄ Tesorio's recommendation is to SFTP all files together.
See the file and unique id dependencies below.
For example: Tesorio cannot process invoice records if the customer record is not received. If an invoice file is received at 11:30am (before the top of the hour) and the customer file is received at 12:05 (after the top of the hour), the invoice record will error out and will not be processed. Tesorio will not automatically reprocess the failed record.
Required Data Files and Data Point Relation
âWe require one file per data type
âYour files' column names must match the Tesorio template files column names
âYour files' column names must be lower case
âYour data needs to be organized to contain common identifiers to link data objects
Account Receivables Data
See below how we link Accounts Receivable data and the unique identifiers we require in each file to be able to link related objects:
Subsidiaries: Required to be uploaded first if you have a multi-subsidiary structure and will be including subsidiaries in the Customers with open balances file
Customers with open balances: this data record will contain the high-level metadata on your customer (whether that be a corporation or individual)
Only customers with open balances need to be on your file. If a previously imported customer record is not in the new file, their balance will be changed to 0
Tesorio Connect has support for parent/child customer structure. Add parent_id to your file to establish the relationship
Open Invoices: this data record will contain the information needed to follow up with customers that are behind on their payments
The files should to contain open invoices only. If a previously imported invoice is not in the new file, its balance will be changed to 0 and status changed to CLEARED
Contacts (Customer and/or Invoice level): this data record will contain the contact information for your customers
Payment Receipts: reflects customer payments received and is displayed in the Tesorio Customer Payments workspace. You will be able to track all historical payments within Tesorio and our AI will be able to learn from this in order to drive prioritization & actionability for your team.
Payment Applications (Payment Lines): this file is used to link customer payments to invoices for visibility in the Tesorio workspaces. Also can be used to re-calculate and update the unapplied balance of customer payment receipts.
âYour file must contain all payment applications to properly reflect and update the lines in Tesorio. Example: If a payment was applied partially to one invoice and later fully applied to a second invoice, your file must contain both payment lines in order to properly link both invoices to the payment. Tesorio always saves the most recent payment line information.
Credit Memo Receipts: This information will allow your customer statements to include the full breakdown of your customers' open balances. It is reflected in the Tesorio Credit Memos workspace
Credit Memo Applications (lines): Used to link Credit Memos to invoices for visibility in the Tesorio workspaces. Also can be used to re-calculate and update the unapplied balance of credit memo receipts.
âYour file must contain all credit memo applications to properly reflect and update the lines in Tesorio. Example: If a credit memo was applied partially to one invoice and later fully applied to a second invoice, your file must contain both credit memo lines in order to properly link both invoices to the credit memo.
Tesorio Connect AR Data Template Files - download to start creating your files
(also available to download in Tesorio -> Administration -> Flat Files)
âYour files' column names must match the Tesorio template files column names
Subsidiaries - Subsidiaries_template.csv
Customers with open balances- Customers_with_open_balances_template.csv
Invoices - Open_customer_invoices_template.csv
Customer Contacts - Customer_contacts_template.csv
Customer Payment Receipts - Customer_payment_receipts_template.csv
Payment Applications (Payment Lines) - Customer_payment_applications_template.csv
Credit Memos - Credit_Memos_template.csv
Credit Memo Applications - Credit_memos_applications_template.csv
Data Schema and Sample files - additional data you can include in your files
âYour files' column names must match the Tesorio template files column names
Required Fields - cannot be blank. The number in the id fields must be unique for each record
Available by Default - these are frequently used/recommended fields configured by default and on the default template. Additional fields are available as per the file and can be added to your files (column names must match the sample files below)
Allow Blanks - Tesorio allows these fields to be on your file with no value
Subsidiaries - Subsidiaries_data_schema_sample.xlsx
Customers with open balances -Customers_data_schema_sample.xlsx
Customer Contacts - Customer_contacts_data_schema_sample.xlsx
Customer Payment Receipts - Payment_Receipts_data_schema_sample.xlsx
Payment Applications - Customer_Payment_Applications_data_schema_sample.xlsx
Credit Memos - Credit_memos_data_schema_sample.xlsx
Credit Memo Applications - Credit_Memo_Applications_data_schema_sample.xlsx
Accounts Payable
See below how we link Accounts Payable data and the unique identifiers we require in each file to be able to link related objects:
Vendors with open balances: this data record will contain the high-level metadata on you vendors (whether that be a corporation or individual)
Only vendors with open balances need to be on your file. If a previously imported vendor record is not in the new file, their balance will be changed to 0
parent/child vendor structure is not supported
Open Bills: A daily load of your open bills.
The files should to contain open bills only. If a previously imported bill is not in the new file, its balance will be changed to 0 and status changed to CLEARED
AP (Vendor) Payments: A daily load of your bill payments
AP Payment Applications: this file is used to link vendor payments to bills in Tesorio workspaces
Tesorio Connect AP Data Template Files - download to start creating your files
(also available to download in Tesorio -> Administration -> Flat Files)
âYour files' column names must match the Tesorio template files column names
Vendors with open balances- template-vendors_open_balances.csv
Open Bills - template-open_bills.csv
AP Payments - template-ap_payments.csv
Ap Payment Applications - template-ap_payment_applications.csv
Data Schema and Sample files - additional data you can include in your files
âYour files' column names must match the Tesorio template files column names
Required Fields - cannot be blank. The number in the id fields must be unique for each record
Available by Default - these are frequently used/recommended fields configured by default and on the default template. Additional fields are available as per the file and can be added to your files (column names must match the sample files below)
Allow Blanks - Tesorio allows these fields to be on your file with no value
Vendors with open balances- vendors_data_schema.xlsx
Open Bills - bills_data_schema.xlsx
AP Payments - Bill_payments_data_schema.xlsx
Ap Payment Applications - bill_payment_applications.xlsx
Data within Files
The suggestions/data schema provided above is recommended, but should not be considered a blocker in case your data schema is different.
Our integration & product has robust custom field support. Any fields you donât see in the data schema that you know your team will want to bring over will not be a problem for our platform.
Naming Conventions for CSV Columns/Fields
Tesorioâs flat file integration requires specific column names. If your files' column names match the Tesorio expected column names, the configuration is entirely automated and you can add or remove fields from your file at any time. See all the additional data fields available for each file in the sample files above.
If you have any questions, or need further support or customization, please contact your Tesorio CSM.
What you will see when you first log into Tesorio
In Administration -> Flat Files you will see the templates for the Accounts Receivable data files. You can download them right from this page and start creating your files.
Refer to the beginning of this article for the file relations and unique ID fields required to connect records.
If you are using our Cash Flow Management product, Accounts Payable data files will be also be available to download
Custom Fields
You can add a custom field to your files at any time. Please notify your CSM so the field can be enabled and visible in the Tesorio workspaces.
Any column on your files (for the types above) that is not an available Tesorio native field will be saved a custom field.
Tesorio fully supports custom fields for the following records.
Customers
Invoices
Payment Receipts
Credit Memo Receipts
Vendors
Bills
File Formats
Tesorio accepts data in the form of comma-separated values (CSV files). Tesorio currently does NOT accept other types of flat file formats (e.g. XML, JSON, XLSX).
Customers with Open Balance File
Customers with Open Balance is a file of customers that have an open balance (non-zero). This allows us to minimize the amount of customer data being exchanged on a daily basis and allows us to ensure we always have the customers you want to communicate with regarding their Accounts Receivable balance. Tesorio assumes Customers not included in this file have a zero ($0) balance.
âPlease note that Tesorio will not delete a customer record if it is not in the most recent file. If a customer is not present in the file, the balance will be updated to 0, but the record is never deleted from Tesorio.
Invoice File
This file needs to include all your customer invoices with an open balance. Invoices not included in this file are marked as CLEARED in Tesorio.
Contacts File
This is a file of contacts with the corresponding Customer ID or invoice ID to link the correct record within Tesorio.
Tesorio will keep your contacts up to date, however, any changes made to customer or invoice contacts, require to have all contacts for that customer or invoice in the contacts file. Tesorio assumes any contact missing from the file is no longer valid and it will be deleted from the customer or invoice record.
For more details on the Tesorio contact management, refer to this article.
Invoice PDFs
Tesorio Connect supports importing your Invoice PDFs.
Tesorio requires a linking field in your invoice file to be able to link the pdfs to the invoice information. The linking field value (the pdf_id) on the invoice file should not include an extension (do not format it like this inv123.pdf. Correct format is inv123)
The Invoice PDF file name in the SFTP must have the extension in lower case as our system is case sensitive- inv123.pdf.
The linking field could be the invoice number or another unique identifier in the Invoice, as long as it follows the above guidelines.
FAQs
What happens if we have a comma in a field value?
Commas in field values must be escaped with double quotes. Example âMicrosoft, Incâ
Do column names have to be exactly what is described in the Data Schema above?
Yes. Tesorioâs flat file integration requires specific column names. If your files' column names match the Tesorio expected column names, the configuration is entirely automated and you can add or remove fields from your file at any time.
What happens if my files fail to upload?
The email/emails set-up by Tesorio to receive sync failure alerts will receive an email notification. Tesorio's support team is also notified and will investigate every file failures.
The status of the file uploads is also visible in the Administration -> Flat Files module in Tesorio (the module needs to be switched to ON for the permission group to be able to view it)
How do I update the list of people to email if my files fail?
The email/emails set-up by Tesorio to receive sync failure alerts will receive an email notification. These emails can be edited right from the Flat File status page. At the top of the Flat File status page you will see a list of emails. To Add an email simply select an empty area and the box and choose from the list of emails or type in the address. To remove an email click on the X next to the contact.