Estimates and Invoices Tools [BETA]

This extension provides commands to import or export data into Banana from your Offers and Invoices files in .csv format. More specifically, it allows you to import and export:

• Estimates
• Invoices

To install the extension click on the menu Extensions > Manage Extensions, among the Online ones look for "Estimates and Invoices Tools" and click on install.

Specifications

To import and export data you need to use csv (coma separated values) files, where:

• Values are separated by a "," (comma).
• Text values are delimited by quotes '"'.
• Text values can't contain quotes, quotes are replaced by apos '`'.
• Dates values are in ISO format "2022-10-24" (yyyy-mm-dd).
• Amount values have a dot "." as decimal separator, and no thousand separator.

Here you can see an example :

InvoiceDate,CustomerNumber,ItemDescription,ItemTotal,ItemVatRate,ItemVatTotal
2022-10-24,120001,"Red roses",100.00,V77,7.70
2022-10-24,120001,"White tulips",20.00,V77,1.54
2022-10-25,120002,"Garofani",12.00,V77,V77,0.92
2022-10-25,120002,"Rododendrus",20.00,V77,1.54

Estimates and invoices Import

To import your Estimates and Invoices data into Banana from outside (such as another software) you need to be able to generate a *.csv file with predefined headers or a .txt file with predefined headers.

Once you have the *.csv file with the data you want to import, open your Estimates and Invoices (.ac2) file and from the Extensions menu select "Estimates and Invoice tools" and select what you want to import. If the data is correct, a window will be displayed with a preview of all the rows that will be imported, click OK to finish the import.

Headers to use for creating a csv or txt file with invoices to import:

Base columns

• CustomerNumber
  • Mandatory field..
  • The number/ID of the customer defined in the Contacts table.
• InvoiceNumber
  • Mandatory field.
  • The invoice number.     
• InvoiceDate
  • Mandatory field.
  • The invoice date. The date must be entered in the format YYYY-MM-DD.
• InvoiceDescription
  • The subject/title of the invoice entered before the details.
• InvoiceCurrency
  • Mandatory field.
  • The currency of the invoice.
• ItemDescription
  • Mandatory field.
  • The description of the item.
• ItemQuantity
  • Mandatory field.
  • The quantity of the item to be invoiced. The minimum required quantity is 1.
• ItemUnit
  • The unit of the item to be invoiced.
• ItemUnitPrice
  • Mandatory field.
  • The unit price of the item.

Advanced columns

• InvoiceDueDate
  • The invoice due date. The date must be entered in the format YYYY-MM-DD.
• InvoiceDiscount
  • The discount to be applied to the total invoice amount.
• ItemNumber
  • The number/ID of the item.
• ItemDiscount
  • The discount to be applied to the unit price of the item.
• InvoiceRoundingTotal
  • The rounding of totals. The default value is 0.05 if nothing is indicated. Value indicated in the "Rounding totals" field in the create/edit invoice dialog.
• ItemTotal
  • The total of the line item. If the amount is net (excluding VAT), the total is without VAT. If the amount is gross (including VAT), the total includes VAT.

VAT Columns

•    InvoiceAmountType
  •  vat_excl (net amount,  VAT excluded). It is the default value if nothing is indicated.
  •  vat_incl (gross amount, VAT included).
  • vat_none (without VAT).          
• ItemVatCode
  • The VAT code applied to the item.
• ItemVatRate
  • The VAT rate applied to the item.            

Control Columns

• InvoiceTotalToPay
  • Value not used for import. It is used to verify if, after import, the invoice amount calculated in Banana and the amount in the imported file match.
• InvoiceVatTotal
  • Value not used for import. It is used to verify if, after import, the total VAT amount calculated in Banana and the amount indicated in the imported file match.
• CustomerName
  • Value not used for import. It contains the name of the organization or the full name and location.
• ItemVatTotal
  • Not used for import.

Import rules

• The invoice/estimate number will be replaced with an internal invoice/estimate number.
• The customer number must exist in the Contacts table.
• The item number must exist in the Items table.
• The VAT code must exist in the VAT codes table.
• You can also get an error:
  • Invoices/quotes are imported without having added contacts in the Contacts table.
  • The .csv file is missing a required field (see Base Columns).
  • A field does not match the correct format.
• In case of an error, the invoice is not imported. Resolve the issue and retry the import.
  For any error messages during the import, visit the Error Messages and Troubleshooting documentation.

Example of *.csv file with some invoices: 

InvoiceNumber,InvoiceDate,InvoiceDueDate,InvoiceDescription,InvoiceDiscount,InvoiceVatTotal,InvoiceTotalToPay,InvoiceCurrency,InvoiceRoundingTotal,InvoiceAmountType,CustomerNumber,CustomerName,ItemNumber,ItemDescription,ItemQuantity,ItemUnitPrice,ItemUnit,ItemVatRate,ItemVatCode,ItemDiscount,ItemTotal,ItemVatTotal
10,2022-06-17,2022-07-17,Invoice,,,67.50,EUR,0.05,vat_none,1," ",1000,Green tea,24.00,2.50,pc,,,,60.00,
10,2022-06-17,2022-07-17,Invoice,,,67.50,EUR,0.05,vat_none,1," ",6000,Shipping expenses,1.00,7.50,pc,,,,7.50,
20,2022-08-07,2022-09-06,Invoice,,2.44,107.50,EUR,0.05,vat_incl,2,"Customer 1 ",1000,Green tea,40.00,2.50,pc,2.50,V25,,100.00,2.44
20,2022-08-07,2022-09-06,Invoice,,2.44,107.50,EUR,0.05,vat_incl,2,Customer 1,6000,Shipping expenses,1.00,7.50,pc,,,,7.50,
30,2022-10-15,2022-11-14,Invoice,,1.00,48.50,EUR,0.05,vat_excl,3,Customer 2,1000,Green tea,16.00,2.50,pc,2.50,V25,,40.00,1.00
30,2022-10-15,2022-11-14,Invoice,,1.00,48.50,EUR,0.05,vat_excl,3,Customer 2,6000,Shipping expenses,1.00,7.50,pc,,,,7.50,

Example of *.csv file with some estimates: 

InvoiceNumber,InvoiceDate,InvoiceDueDate,InvoiceDescription,InvoiceDiscount,InvoiceVatTotal,InvoiceTotalToPay,InvoiceCurrency,InvoiceRoundingTotal,InvoiceAmountType,CustomerNumber,CustomerName,ItemNumber,ItemDescription,ItemQuantity,ItemUnitPrice,ItemUnit,ItemVatRate,ItemVatCode,ItemDiscount,ItemTotal,ItemVatTotal
1,2023-12-15,2024-01-13,Estimate 1,0.07,0.19,2.60,CHF,0.05,vat_excl,2,Peter Ridge,1000,Tea,1,2.50,pc,7.70,V77,,2.50,0.19
2,2023-06-17,2023-07-17,Estimate 2,2.04,1.96,27.40,CHF,0.05,vat_excl,1, ,1200,Herbal teas,4.00,5.00,pc,7.70,V77,,20.00,1.54
2,2023-06-17,2023-07-17,Estimate 2,2.04,1.96,27.40,CHF,0.05,vat_excl,1, ,6000,Shipping fees,1,7.50,pc,7.70,V77,,7.50,0.58
3,2023-06-17,2023-07-17,Estimate 3,2.32,10.03,140.20,CHF,0.05,vat_excl,2,Peter Ridge,1000,Tea,50.00,2.50,pc,7.70,V77,,125.00,9.63

Export estimates and invoices

The export generates a *.csv file containing the exported data. When exporting, data from all columns are exported; the *csv file generated has the same structure as defined for importing.

When you export you can decide:

• The name of the file that is generated.
• Whether to immediately open a preview of the file.

The generated data cannot be imported into an integrated invoicing model.

Error Messages and Troubleshooting

• "[Invoices] Contact ID xx not found in the contacts table. Did you import the contacts?"
  • This message is displayed if invoices are imported without first importing the contacts. The program will import the invoice without any contact data.
  • Solution: Import all contacts into the Contacts table, then retry the invoice import.
• "xx is a required field"
  • This message is shown if the "xx" column is empty in the .csv or .txt file. This field is mandatory and cannot be empty. The invoice will not be imported.
  • Solution: Enter text in the required field and retry the import.