Integrations & ERP Custom Fields / CSV Files & FTP

CSV zip file requirements

Introduction

YayPay allows integration with our platform using CSV files. CSV files should contain the data that YayPay needs to operate and depending on your setup, can be generated by your ERP reporting tools or using your preferred ETL/Integration software.

YayPay would need to have the following entities in the CSV files:

  • Customers
  • Customers' contact details
  • Invoices 
  • Invoice Items
  • Payments, Credit Memos, and other documents that impact AR ledger.
  • Transaction allocation information - what documents are allocated to what Invoices. 
  • Sales order

Relationships between those objects are displayed in the diagram below:

 

Below you can find a detailed description of each CSV file that would contain this information. 

There are two modes of CSV data upload that you can use:

  • Snapshot. In this mode on your every upload of the CSV files to YayPay, you are loading an entire snapshot of the AR data from the ERP. This will take more time to load just because of the file size, but it makes sure that at every upload your data is exactly the same as you expected. 
  • Incremental. In this mode, after the initial data load, you are only loading updates to the documents. The only risk here is if for any reason one of the uploads did not go through, and the next one does not mention updated documents - they will stay in an incorrect state in YayPay.

If possible, we recommend using an Incremental approach, but with a floating window of the update documents. For example, every day you can be sending all the updated documents for the last 7 days. This will allow the synching process to solve-correct in case of errors.

CSV files can be loaded manually or automatically through the FTP server, configured on the customer side. 

CSV Integration works the same way as a regular integration, except that CSV files need to be uploaded to Yaypay regularly in order to keep the system up-to-date with the AR situation in the client's ERP (e.g. these regular updates help to facilitate workflow collection & reminders sent to your end-customer by using the latest information. It is recommended for updated CSVs to be uploaded on a daily basis.


 

CSV Format Requirements

YayPay needs to receive CSVs in a certain format in order to be able to process them properly so that the values correspond to what is expected.

Delete

Warning

The coding of the CSV file uploaded must be UTF-8.

Other codings may bring the wrong display of symbols.

See this article to find out how to manually save your CSV file with UTF-encoding.

The first row of the CSV files should always contain column names, which are case-sensitive. 

YayPay recommends quoting text, so any data that includes a comma in the system doesn't shift the output.

Data Types used:

  1. Double - any number with . as a separator
  2. Boolean - String with the value true or false. This value is case sensitive and must be in lower case only 
  3. String - any String value
  4. Date - Date in format yyyy-MM-dd'T'HH:mm:ss. Example of valid date: 2017-08-13T11:05:19. 
Delete

Date Format

  • By default, all dates are expected to be in the ISO 8601 date format.
    Short overview and description of the ISO 8601: https://www.w3.org/TR/NOTE-datetime
     
  • The default date format can be overwritten by utilizing of the JSON mapping file. You can specify you own formatting string for all or some of the files.
    You can test your custom date format here: http://www.sdfonlinetester.info/
     
  • invoice.csv file. discountDate column
    The date format of discountDate is specified in the JSON mapping as the parameter "discountDateFormat" and can have such values of format as "yyyy-MM-dd" or "yyyy-MM-dd'T'HH:mm:ss".
    If "discountDateFormat" is not specified in JSON mapping, the default ISO 8601 format is expected.

 

Delete

Important

Every CSV file contains an "internalID" (or "txID") field, which should be a unique identifier of the record within the system. It is important to not change your internal ID generation/representation approach over time because this can result in duplicated records in YayPay (which will then create the need for data to be removed). Choose the approach that best generates the unique internal ID for each record (this could also come from your ERP system) and leave it the same thereafter. If you have to change the internal ID generation and representation approach, please reach out to YayPay Support or your YayPay Rep. to have the previous records identified and removed from YayPay

 

Delete

Follow RFC 4180 Standards and Guidelines

  • Each record represented in the CSV file should contain the same number of comma-separated fields
  • Fields containing a line break, double quote, or comma must be enclosed in double quotes. If they are not, the file will not process correctly
  • If double quotes are used to enclose fields, then a double quote inside a field must be escaped by preceding it with a second double quote.


 

All CSV files should be in the same zip file. The zip file should contain:

  • customer.csv
  • contact.csv
  • invoice.csv
  • invoiceLines.csv
  • transaction.csv or transactionFull.csv
  • transactionAllocations.csv
  • salesOrder.csv (not obligatory) 
Delete

Important

The ZIP file must not contain any subfolders that store files. After unzipping the ZIP file, there should only be a set of CSV files without any other folders inside it.

 

Customers

This CSV file should include information about all of the customers in the system, including ones that are not currently active.

Download customer.csv template

Filename MUST be: customer.csv    

    
Name Type Accepted Values Required Description
internalId String
 
yes

This is the ID of the Customer in the ERP/internal system. The value will be shown as the ERP ID column in YayPay’s Aging Report (after the file import). 

The value is CASE SENSITIVE.

If you need this value to be also included in YayPay’s Email Templates (Subject line or the message body), or to be shown in YayPay’s Statement page, represent the value as a custom field column (see notes later). The custom field will be available as a data tag when configuring the Email Template 

status String Active, Inactive no Status of the customer in the ERP/internal system. For most situations, YayPay requires Active status customers i.e. to represent your pool of active end-customers that have a current, commercial relationship with you, with a $0 balance or open outstanding amounts, etc.
companyName String
 

 
yes Company name
email String
 

 
no Main company email address. This is not the billing contact email. See Contacts Table.
phone String
 

 
no Main company phone number. This is not the billing contact phone number. See Contacts Table.
altPhone String
 

 
no Alternative phone number
webAddress String
 

 
no Customer company page URL
creditLimit Double
 
no Credit Limit of the company
terms String
 
Any relevant string - examples: Net 30, Net 60, etc. no Customer Terms as maintained in the ERP. This field is not shown in the YayPay Application by default. Represent it as a Custom Field again if needed
parentId String
 

 
no Internal ID of the parent company if available.
balance Double
 
no Current AR balance of this company.
currency String
 
USD, EUR, GBP, etc. yes ISO 4217 Currency Codes
country String
 

 
no Customer's country
city String
 

 
no Customer's city
state String
 

 
no Customer's state
zip String
 

 
no Customer's zip code
line_1 String
 

 
no Customer's address line 1
line_2 String
 

 
no Customer's address line 2
customFields String
 
refer to notes below no Include column when the need for Custom Fields arises
is_deleted Boolean true or false no This is to represent that the record was de-activated /deleted from the source ERP system, and therefore also to be removed from YayPay if set to true.
cf_* String
 

 
no Custom field columns. The value from these columns will be used if "customFields" column not exist. Examples: cf_field_1, cf_field_2,cf_field_3.
salesRepEmail String
 

 
no

 Sales Representative email. 

The respective User type has to be created in YayPay first (under User Management), or already exist prior to the CSV file import.
The email address of the Sales Representative, Customer Success Specialist, and AR Manager used in the customer.csv file must match with the email of the User type created in YayPay.

The value is CASE SENSITIVE.

customerSuccessEmail String
 

 
no

 Customer Success Specialist email

The respective User type has to be created in YayPay first (under User Management), or already exist prior to the CSV file import.
The email address of the Sales Representative, Customer Success Specialist, and AR Manager used in the customer.csv file must match with the email of the User type created in YayPay.

The value is CASE SENSITIVE.

arManagerEmail String 
 
no

 AR Manager email

The respective User type has to be created in YayPay first (under User Management), or already exist prior to the CSV file import.
The email address of the Sales Representative, Customer Success Specialist, and AR Manager used in the customer.csv file must match with the email of the User type created in YayPay.

The value is CASE SENSITIVE.

dunsNumber String   no Customer's DUNS number
entityId String
no The trigger to sync AR document by the entity ID (company).

For Custom Fields:

  1. Json Column:
  • The column header in the file must be called "customFields"
  • Add unlimited custom fields/values for each record/row in the customer.csv file. The format must be as follows:
  1. JSON data-interchange format is used
  2. The text must be enclosed in double quotes and additional double quotes are required in JSON format
  3. Open with a curly bracket, add a double quote before the curly bracket
  4. Enclose the first custom field name in two double quotes (JSON and CSV format requirements), separated by a colon, followed by the value in two double quotes
  5. Repeat the same manner for the second, third and fourth custom fields (each separated by a comma before beginning the next one)
  6. Close with a curly bracket and add the final double quote

An example for customer.csv with 2 custom fields could be:

"{""customer_group"":""Online"",""customer_industry"":""Media""}"

The field/value string must be in the same cell. It is not to be placed in separate columns in the CSV file

2. Custom fields columns: 

  •  For each custom field, you can add a column with a name starting with cf_
  •  Each column is a separate custom field
  • You are unable to configure both solutions. If "customFields" column exists the Json Column solution will be used.

Contacts

This CSV should include contact details of the customer and/or invoice that will be part of the recipients used in the collection workflows. 

Download contact.csv template

Filename MUST be: contact.csv

    
Name Type Accepted Values Required Description
internalId String
 
yes

ID of this contact in the ERP/internal system

CASE SENSITIVE

customerId String
 
yes

ID of the customer in the ERP/internal system

CASE SENSITIVE

firstName String
 
no First name of the contact
lastName String
 
no Last Name of the contact
email String
 
no The email address of the contact
phone String
 
no Phone number of the contact
mobilePhone String
 
no Mobile phone number of contact
note String
 
no Any additional information. Max 200 characters.
primary Boolean true or false no If the value is 'true', contacts will be regarded and marked as a 'Billing Contact' in YayPay - Only Billing Contacts are used by YayPay's Collection Workflows. This field is case-sensitive, this means that TRUE and FALSE are not correct values, it must be in lower case. 
is_deleted Boolean true or false no

This is to represent that the record was de-activated /deleted from the source ERP system, and therefore also to be removed from YayPay if set to true.

entityId String
no The trigger to sync AR documents by the entity ID (company).

Transactions

This CSV should include all the transactions that influence customer's AR balance, such as:

  • Payment
  • CreditMemo
  • JournalEntry
  • Adjustment

Download transaction.csv template

Filename MUST be: transaction.csv

    
Name Type Accepted Values Required Description
txId String
 
yes

ID of this transaction in the ERP/internal system. Note, that this ID must be unique in this one CSV file. 

CASE SENSITIVE

txType String

CreditMemo, Payment, JournalEntry, Adjustment


 

yes
 

Defines the type of transaction record in the ERP/internal system.

CreditMemo/Payment transactions will credit the customer's balance (amount must be positive otherwise the negative amount will have the opposite effect - the balance will be debited).

Adjustment/JournalEntry transactions will debit the customer's balance (amount must be positive otherwise the negative amount will have the opposite effect - the balance will be credited).
 

customerId String
 
yes
 

ID of the customer this transaction associated with. This must match exactly "internalId" field of one of the customers from the customer CSV file. 

CASE SENSITIVE
 

amount Double
 
yes
 

This is the amount of the document. Note, that the Adjustment document type is interpreted as a Debit document, while Payment and Credit Memo are Credit documents. This means that if you pass over $50 as an amount for Payment or Credit Memo, it will credit the balance for this amount, but if you do the same for Adjustment - it will debit the balance (add to it). 

amountApplied Double
 
yes
 

The total amount of payment applied to invoices or other entities. Note, that this field must match the sum of this transaction's allocations listed in the transactionAllocations.csv file. If they don't match, the total customer balance will be calculated correctly, but it will not match with the allocation information on the Statement page Payments tab in YayPay.  

currency String

USD, EUR, GBP, etc.

yes
 

ISO 4217 Currency Codes

txDate Date
 
yes
 

Date of the transaction

exchangeRate Double
 
yes
 

The rate for converting currency of document to biz currency
 

refNum String
 
no

Payment reference number 
 

is_deleted Boolean true or false no

This is to represent that the record was de-activated/deleted from the source ERP system, and therefore also to be removed from YayPay if set to true
 

paymentType String
 
no

Type of payment. Free-form, but can take values like "ACH", "Credit Card", "Wire", "Check", "Writeoff', "Sales Order #", etc.

If txType = CreditMemo or txType = Payment, the "paymentType" field will display the value entered in the "paymentType" field.

If "txType" takes values other than CreditMemo or Payment, the "paymentType" field displays the value of the "txType" field.
 

entityId String
no The trigger to sync AR documents by the entity ID (company).

Download transactionFull.csv template

This file supports invoices along with credit memos, payments, journal entries, and adjustments.

Note, that you can use ONE out of the two files: transaction.csv or transactionFull.csv, and NOT both of them.

Filename MUST be: transactionFull.csv

    
Name Type Accepted Values Required Description
txId String
 
yes

ID of this transaction in the ERP/internal system. Note, that this ID must be unique in this one CSV file. 

CASE SENSITIVE

txType String CreditMemo, Payment, JournalEntry, Adjustment, Invoice yes

Defines the type of transaction record in the ERP/internal system.

CreditMemo/Payment transactions will credit the customer's balance (amount must be positive otherwise the negative amount will have the opposite effect - the balance will be debited).

Adjustment/JournalEntry transactions will debit the customer's balance (amount must be positive otherwise the negative amount will have the opposite effect - the balance will be credited).

customerId String
 
yes

ID of the customer this transaction associated with. This must match exactly "internalId" field of one of the customers from the customer CSV file. 

CASE SENSITIVE

amount Double
 
yes This is the amount of the document. Note, that the Adjustment document type is interpreted as a Debit document, while Payment and Credit Memo are Credit documents. This means that if you pass over $50 as an amount for Payment or Credit Memo, it will credit the balance for this amount, but if you do the same for Adjustment - it will debit the balance (add to it). 
 
amountApplied String
 
yes The total amount of payment applied to invoices or other entities. Note, that this field must match the sum of this transaction's allocations listed in the transactionAllocations.csv file. If they don't match, the total customer balance will be calculated correctly, but it will not match with the allocation information on the Statement page Payments tab in YayPay. 
 
dueDate Date
 
no

Due date of the Invoice

currency String USD, EUR, GBP, etc. yes

ISO 4217 Currency Codes

txDate Date
 
yes

Date of the transaction

exchangeRate Double
 
yes

The rate for converting currency of document to biz currency
 

refNum String
 
no

Payment reference number 
 

is_deleted true or false
 
no

This is to represent that the record was de-activated/deleted from the source ERP system, and therefore also to be removed from YayPay if set to true
 

paymentType
 
ACH, credit card, Wire, Check, etc. no

Type of payment. Free-form, but can take values like "ACH", "Credit Card", "Wire", "Check", "Writeoff', "Sales Order #", etc.

If txType = CreditMemo or txType = Payment, the "paymentType" field will display the value entered in the paymentType field.

If "txType" takes values other than CreditMemo or Payment, the "paymentType" field displays the value of the "txType" field.

entityId String
no The trigger to sync AR documents by the entity ID (company).
Delete

Note: Since you do not fill in the invoice Close Date field for the transaction (this field is absent in the invoice.csv and transactionFull.csv files), YayPay uses the following logic for the invoice payment date:

If the invoice has the PAID status, then the last_payment_date will be equal to the creation date of the payment that closed it (paid in full).

 

Transaction Allocations

This document would represent the relations between applied transactions and invoices.

Download transactionAllocations.csv template

Note, if you send an invoice allocation information, and invoice Open amount or invoice status changed - this invoice must be listed in the invoice.csv file. It is not enough to just list it in transaction allocations. 

Filename MUST be: transactionAllocations.csv 


Name Type Accepted Values Required Description
txId String
 
yes

ID of this transaction in the ERP/internal system. Note, this ID must exactly match the "txId" field of the associated transaction from the transaction.csv file. This field can have duplicates in this file, for example in case if one payment is allocated to multiple invoices. 

CASE SENSITIVE

invoiceId String
 
yes

ID of the invoice this transaction is associated with. Note, this is the internal ID of the invoice, not Invoice Number and it must exactly match "invoiceId" field from the invoice.csv file. Also, this invoice itself has to either be already present in YayPay or must be included in the invoice.csv file. This like will be ignored if the invoice was not found by this ID. 

A combination of the "txId" and "invoiceId" fields in this file must be unique in this file. 

CASE SENSITIVE

amount Double
 
yes The total amount of this transaction applied to invoices or other entities. This does not have to match a full transaction amount that must is listed in the transaction.csv file. This field only indicates how much of this transaction (Payment, Credit Memo or other transaction) is allocated to this specific invoice. 
 
date Date
 
yes Date of the transaction allocation. The field is not used for sync with YayPay. 
entityId String
no The trigger to sync AR documents by the entity ID (company).


 

Delete

Info

Please note that "is_deleted" field is not included in transactionAllocations.csv file. Allocations file works in pair with the transaction file, so if the client sends an update on the transaction with allocations synced by YayPay earlier and does not include those allocations, they will be deleted. And if new allocations are added, there should be old ones present in the transactionAllocations.csv as well. 

 

Invoices

This document should have a list of open and closed invoices.

Download invoice.csv template

The filename MUST be: invoice.csv

    
Name Type Accepted Values Required Description
invoiceId String
 
yes

ID of this invoice in the ERP/internal system. It must be unique in this CSV file and across all the data send to YayPay. 

CASE SENSITIVE

customerId String
 
yes

ID of the customer who was issued the invoice. This must match exactly "internalId" field of one of the customers from the customer CSV file. 

CASE SENSITIVE

invoiceNumber String
 
yes

Invoice Number

dateCreated Date
 
yes

The date the invoice was created in the ERP/internal system

dueDate Date
 
yes

Due date of the Invoice

terms String

e.g. Net30, Net60

no Invoice Terms maintained in the ERP. This field is not shown in the YayPay Application by default. Represent it as a Custom Field again if needed
poNumber String
 
no

PO number associated with this invoice, if available. 

Represent this as a Custom Field with the name PO_NUMBER also if you want to show it in the YayPay User Interface. 

amount Double
 
yes

The Total Amount of the invoice.

YayPay stores it as a decimal with 2 decimal places. More precise values are rounded to two decimal characters.

discountAmount String   no The amount of the discount your customer will receive if (s)he pays this invoice according to the terms you have set
 
discountDate Date
 
no The date your customer must pay this invoice by in order to receive a discount. The discount your customer receives is based on the terms you have set for the customer 
 
paid Double
 
yes

The amount paid for the invoice.

YayPay stores it as a decimal with 2 decimal places. More precise values are rounded to two decimal characters.

(If the Invoice is fully paid, ensure that paid equals to amount. The Invoice will be regarded as a Closed Invoice in YayPay by the use of rounded values.*)

currency String

USD, EUR, GBP, etc.

yes

Invoice currency. ISO 4217 Currency Codes

exchangeRate Double
 
no

The currency exchange rate for this invoice

taxTotal Double
 
no

The absolute monetary value of the tax in the invoice currency. 

The provided numeric value will be reflected on the invoice as VAT if there's no value in "taxAmount" field

notes String
 
no

Memo to the customer, or a short Invoice note. 

customFields String

Refer to notes below

no

One needs to include this column when the need for Custom Fields arises

billingEmail String
 
no

For the purposes of creating an Invoice level billing contact - this will be matched with the email in the contact.csv file. If there is a match, the import will create an Invoice Billing contact.

Several email addresses can be contained in one comma-separated string. Email addresses imported this way will belong to one contact. For example: "first@example.com,second@example.com".

subTotal Double
 
no

Sub-total of the invoice before tax, usually a sum of the invoice items in the invoice currency. 

taxAmount Double
 
no

Tax amount in the invoice currency. 

cf_* String
 
no

Custom field columns. The value from these columns will be used if 'customFields' column does not exist. Examples: cf_field_1, cf_field_2,cf_field_3.

is_deleted Boolean true or false no

This is to represent that the record was de-activated /deleted from the source ERP system, and therefore also to be removed from YayPay if set to true.

entityId String
no The trigger to sync AR documents by the entity ID (company).

*Example:

natural value amount natural value paid paid value stored in YayPay YayPay Invoice status Invoice displayed in YayPay as
100.00 100.0000001 100.00 PAID Closed
100.10 100.099999999 100.10 PAID Closed


In YayPay, the invoices can be unpaid, partially paid, and paid.

On the Statement page, paid invoices are found in the CLOSED INVOICES tab, and unpaid and partially paid invoices are found in the OPEN INVOICES tab.

Consider the following rules regarding the “paid” field:

  • IF "paid" = 0 THEN the invoice is unpaid
  • IF "paid" > 0 AND paid < "amount" THEN the invoice is partially paid
  • IF "paid" >= "amount" THEN the invoice is paid
     
Delete

Note

The connector does not expect any negative amounts paid for the invoice, so please consider using transaction.csv to provide any payments that will decrease the customer's balance. The negative sign in the "paid" field will result in an increase of the customer's balance as it takes on the difference between the "paid" and "amount" fields. 

 

For Custom Fields:

1. Json Column:

  • The column header in the file must be called ‘customFields’
  • Add unlimited custom fields/values for each record/row in the invoice.csv file. The format must be as follows:
  1. JSON data-interchange format is used
  2. The text must be enclosed in double quotes and additional double quotes are required in JSON format
  3. Open with a curly bracket, add a double quote before the curly bracket
  4. Enclose the first custom field name in two double quotes (JSON and CSV format requirements), separated by a colon, followed by the value in two double quotes
  5. Repeat the same manner for the second, third and fourth custom fields (each separated by a comma before beginning the next one)
  6. Close with a curly bracket and add the final double quote

An example of invoice.csv with 3 custom fields could be:

"{""sales_terms"":""Net 30"","ship_method"":""TNT Express"",""term_name"":""5% Discount""}"

The field/value string must be in the same cell. It is not to be placed in separate columns in the CSV file

2. Custom fields columns: 

  • For each custom field, you can add a column with a name started with cf_
  • Each column is a separate custom field
  • You are unable to configure both solutions. If 'customFields' column exists the Json Column solution will be used. 

Invoice Items

Download invoiceLines.csv template

Filename MUST be: invoiceLines.csv

    
Name Type Accepted Values Required Description
itemId String
 
yes

ID of this item in the ERP/internal system

CASE SENSITIVE

invoiceId String
 
yes

ID of the invoice the item is associated with

CASE SENSITIVE

name String
 
yes

Item name

description String
 
no

Description of Item

rate Double
 
yes

The rate for one item

quantity Double
 
no

Quantity of the item ordered

amount Double
 
yes

The total amount of the line item ordered (rate x quantity)

customFiels String see Invoice section no One needs to include this column when the need for Custom Fields arises
entityId String
no The trigger to sync AR documents by the entity ID (company).

 

Delete

Info

Please note that "is_deleted" field is not included in invoiceLines.csv file. invoice.csv and invoiceLines.csv are working in pairs: if the client sends an update on the invoice with lines synced by YayPay earlier and does not include invoice lines, those lines will be deleted. And if new lines are added to the invoice in the ERP, they must be added to the invoiceLines.csv as well. 

 

Sales Order 

Download salesOrder.csv template

Filename MUST be: salesOrder.csv 

    
Name Type Accepted Values Required Description
customerId String
 
yes

ID of the customer who was issued the sales order. This must match exactly "internalId" field of one of the customers from the customer CSV file. 

CASE SENSITIVE

internalId String
 

 
yes

ID of this sales order in the ERP/internal system

CASE SENSITIVE

externalId String
 

 
no

Additional sales order ID, for instance, if it created in other system.

orderNumber String
 

 
yes

Order number that will be displayed on YayPay User Interface.

poNumber String
 

 
no

PO number associated with this sales order, if available. It will be displayed on YayPay User Interface.

orderStatus String
 

 
yes

Order status that will be displayed on YayPay User Interface.

orderDate Date
 
yes

Order date that will be displayed on YayPay User Interface.

shipDate Date
 
yes

Ship date that will be displayed on YayPay User Interface.

total Double
 
yes

Order amount that will be displayed on YayPay User Interface.

subTotal Double 
 
yes

Sub-total of the sales order before tax.

taxAmount Double
 
yes

Tax amount in the sales order currency. 

currency String

USD, EUR, GBP, etc.

yes

Sales order currency. ISO 4217 Currency Codes, it will be displayed on YayPay User Interface.

exchangeRate Double
 
yes

The currency exchange rate for this sales order.

terms String
 

 
no

Terms of the Sales order. Free-form,  it will be displayed on YayPay User Interface.

memo String
 

 
no

A free-form memo, will be displayed on YayPay User Interface.

salesRepresentative String
 

 
no

Must be valid email address.

billingContact String
 

 
no
 
customFields String
 
see Invoice section no

One needs to include this column when the need for Custom Fields arises

is_deleted Boolean true or false no

This is to represent that the record was deactivated /deleted from the source ERP system, and therefore also to be removed from YayPay if set to true.

entityId String
no The trigger to sync AR documents by the entity ID (company).

Delivery of files to YayPay

   You should upload zip file with content to YayPay. Zip archive structure is as follows:

          /customer.csv

          /contact.csv

          /invoice.csv

          /invoiceLines.csv

          /transaction.csv or /transactionFull.csv

          /transactionAllocations.csv

          /salesOrder.csv (not obligatory)

 

Transaction File Mode with Invoices

The CSV Connector provides the ability to upload the transaction file that includes ALL the open documents that impact AR balances, including invoices. 

The new CSV file has the name transactionFull.csv. Besides, the CSV Connector supports processing the ‘old’ transaction.csv file. 

 

How the CSV Connector works if your ZIP archive includes a transactionFull.csv file: 

1. Regardless of the fact that you can upload a new file transactionFull.csv with open entities, the CSV Connector proceeds to support the sync process for the ‘old’ transaction.csv file. The CSV Connector can switch the sync process in a new way to sync the ZIP archive with the transactionFull.csv file:
 

a) If the ZIP archive includes the transactionFull.csv file, the Connector handles the ZIP archive using new sync

b) If the ZIP archive includes the transaction.csv file, the Connector handles the ZIP archive using old sync

c) If the ZIP archive includes both files (transactionFull.csv and transaction.csv), the Connector fails the sync process with the error message.


 

2. The peculiarity of the new transactionFull.csv file is to support the new document type - Invoice. Thus, the new transactionFull.csv file combines the following AR documents:

  • CreditMemo
  • Payment
  • JournalEntry
  • Adjustment
  • Invoice → NEW 

3. Due to the new logic of processing the transactionFull.csv file, to sync Invoices to YayPay, you can fill in the required fields for Invoices in the transactionFull.csv file. Additional fields for Invoices can be filled in the invoice.csv file. The transactionFull.csv file structure requirements are given above.

By default, the "dueDate" field is optional for Credit Memo, Payments, JournalEntry, and Adjustments because these types of documents don’t use Due Date. The "dueDate" is required only for Invoices. Thus, during the validation process, the CSV Connector checks if the "txType" is equal to Invoices. If txType = Invoices, the "dueDate" field must be filled in.

Delete

Info 

You will be able to fill in the fields, that are not mapped, in the invoice.csv file. Thus, the CSV Connector uses the invoice.csv file as additional information for Invoices.


So, the invoice.csv file is an optional file during the sync process.


 

Delete

Info

If you upload the ZIP archive with the transactionFull.csv file, the invoice.csv file will be an optional file during the sync process. However, the invoice.csv file is still required in the zip archive, even though it may be blank (contain only headers).

 


 

 
User scenario Result during the sync process
You fill in data incorrectly in the transactionFull.csv file not observing the rules of field mapping.

The CSV Connector validates data quality. Thus, if data is incorrect, the ERP Connector returns an error during the sync process.

For example, the "amount" field includes text. In this case, the CSV Connector returns the error: columnName has the wrong date format.

You can add additional fields from the invoice.csv file to the transactionFull.csv file so that you can sync invoices using one file.

The CSV Connector checks only the completion of required fields and validates data quality. Thus, if you add the field that is not supported via the transactionFull.csv file, the CSV Connector will not sync this field.

Also, if you don’t specify the required field, the CSV Connector returns an error.


 

You fill in completely both files: invoice.csv and transactionFull.csv. Also, you want to sync both files to YayPay. During synchronization, the CSV connector ignores data from the invoice.csv file that is represented in transactionFull.csv. The only additional Invoice information will be pulled from invoice.csv
You fill in the invoice.csv file completely. However, you don't specify the required fields (or rows) in the transactionFull.csv file. During synchronization, the CSV connector returns an error stating that the transactionFull.csv file lacks the required fields.

4. The CSV Connector uses the invoice.csv file as a complementary file with additional data for Invoices that the CSV Connector will sync from the transactionFull.csv file.

The transactionFull.csv file prevails over the invoice.csv file. If during the sync process the CSV Connector doesn’t detect a connection for Invoices between the transactionFull.csv file and invoice.csv file, the CSV Connector doesn’t proceed with the sync process to sync Invoices from the transactionFull.csv file.

 
User Scenario Result during the sync process
You create the ZIP archive that doesn’t include the invoice.csv file. During the sync process, the CSV Connector returns an error due to the invoice.csv file being required.
You create the ZIP archive that doesn’t include the transactionFull.csv file.
But your ZIP archive includes transaction.csv file
During the sync process, the CSV Connector updates/creates Invoices from the invoice.csv file due to the Connector using the old logic during the sync process (works with the transaction.csv file).
You create both invoice.csv and transactionFull.csv files, but the CSV Connector cannot detect the connection between Invoices by txId==InvoiceId. The CSV Connector syncs Invoices to YayPay from the transactionFull.csv file without additional data from the invoice.csv file.
You create both invoice.csv and transactionFull.csv files, and the CSV Connector detects the connection between Invoices by txId==InvoiceId. Also, the invoice.csv and transactionFull.csv files include the "dueDate" field. The CSV Connector always uses the "dueDate" field from the transactionFull.csv file. If the transactionFull.csv doesn’t include dueDate for Invoices, the sync process finishes with an error.
You want to delete the Invoice from YayPay that was synced previously.

1. The CSV Connector always uses the "is_deleted" field from the transactionFull.csv file. If the transactionFull.csv file doesn’t include the "is_deleted" field OR the field is not filled or set to "false", the CSV Connector doesn’t remove the record.

2. If the "is_deleted"  field is set to “true”, during the sync process the CSV Connector removes Invoices that are presented in the file and were synced previously with YayPay.

You repeatedly synchronize both invoice.csv and transactionFull.csv files with the same data, but without any fields or with changed values.
 

1. The CSV Connector overwrites only open documents in YayPay using the transactionFull.csv file. Closed documents are loaded and updated as they become available.

2. If the transactionFull.csv file doesn’t include the available Invoice in YayPay, it means the invoice is closed (paid) in the ERP system.

3. The CSV Connector erases data that was synced previously but is not presented in the transactionFull.csv file. The reason, the CSV Connector will upload empty fields via the upload API.


5. Connection of the transactionFull.csv file with the invoiceLines.csv file.

The "invoiceId" field from the invoiceLines.csv file should exactly match the "txId" field of the associated invoice from the transactionFull.csv file.

 

 
User scenario Result during the sync process
You upload the ZIP archive that includes the transactionFull.csv file. Also, the ZIP archive doesn’t include the invoice.csv file. During the sync process, the CSV Connector returns an error due to the invoice.csv file being required. As an alternative, you can leave it blank.
You create the ZIP archive that includes the following files: invoice.csv, invoiceLines.csv, and transactionFull.csv. The InvoiceId value matches only between transactionFull.csv and invoiceLines.csv files. In this case, the CSV Connector retrieves data from the transactionFull.csv, matches them with the invoiceLines.csv file, and sync Invoices to YayPay. 
You ignore filling required fields in the transactionFull.csv. During the sync process, the CSV Connector will not sync items from the invoiceLines.csv file due to the transactionFull.csv file doesn’t include the required field such as "InvoiceID". Connector will stop the sync process and return error.

 

6. Connection of the transactionFull.csv file with the transactionAllocations.csv file.
The "invoiceId" field from the transactionFull.csv file should exactly match the "invoiceId" field of the associated Invoice from the transactionAllocations.csv file.

 

 

 
User scenario Result during the sync process
You create the ZIP archive that doesn’t include the transactionFull.csv file. In this case, the CSV Connector cannot determine the connection between the files highlighted in red to sync Invoices and allocated transactions to YayPay. The Connector has to return an error.
You create the ZIP archive that includes files: transactionAllocations.csv, and transactionFull.csv. The InvoiceId value matches between transactionFull.csv, and transactionAllocations.csv files.  

In this case, the CSV Connector retrieves data from the transactionFull.csv and syncs Invoices to YayPay.

Items from transactionAllocations.csv will be synced for Invoices from the transactionFull.csv.

You ignore filling in the required fields in the transactionFull.csv file. During the sync process, the CSV Connector will not sync items from the transactionAllocations.csv file due to the fact that transactionFull.csv file doesn’t include the required fields.

 

Delete

Note   

1. During the sync process the CSV Connector retrieves data from the transactionAllocations.csv file and overwrites previously synced documents to YayPay.

2. If YayPay has pre-synced documents, but the transactionAllocations.csv file does not, during the sync process the CSV Connector doesn't remove the pre-synced documents in YayPay.

 

7. Determining a Positive or Negative Amount in the transactionFull.csv file.

In the transactionFull.csv, the "amount" field defines if this is a Credit or a Debit document.

Therefore, in the transactionFull.csv field "amount" is UNIFIED. This means that the sign of the "amount" field should define if this is a Credit or a Debit document.

 

Example:

Invoice: $ 800
Payment: -$300
CreditMemo: -$100
Adjustment 1: -$50
Adjustment 2: $150

Total Balance: $500

Thus, if you fill in the Payment amount -$300 or $300, and Credit Memo amount -$200 or $200 in the CSV file, the CSV Connector will use the opposite value.

Example:

-$300 → invert to $300
$300 → invert to -$300

The Connector applies the amount inversion for the following fields in the transactionFull.csv file:

- "amount"

- "amountApplied"
 

Can't find what you need?

Contact our support team support@yaypay.com for help.