Estimates and Invoices Tools [BETA]

This extension provides commands to import into Banana or export from Banana the data in your Offer and Invoice files, specifically, you can import or export:

• Invoices
• Estimates

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.

Specifics

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

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.

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.

Invoices

Headers to be used to create a *csv file with Invoices to be imported:

• InvoiceNumber: By Import will be replaced with the internal number.
• InvoiceDate: Required field.
• InvoiceDueDate
• InvoiceDescription
• InvoiceDiscount
• InvoiceVatTotal: Value not used for import, is used to check if after the importation the amounts correspond.
• InvoiceTotalToPay: Value not used for import, is used to check if after the importation the amounts correspond.
• InvoiceCurrency
• InvoiceRoundingTotal
• InvoiceAmountType: vat_excl, vat_incl, vat_none (default is vat_excl).
• CustomerNumber: Required field.
• CustomerName: Value not used for import, contains the Organisation Name or the First and Last Name plus the Locality.
• ItemNumber
• ItemDescription: Required field.
• ItemQuantity
• ItemUnitPrice
• ItemUnit
• ItemVatRate
• ItemVatCode
• ItemDiscount
• ItemTotal: Required field, contains the total exclusive vat if InvoiceAmountType is vat_excl, otherwise it contains the total inclusive vat.
• ItemVatTotal: Not used for import.

Import rules

• The invoice number will be replaced with an internal invoice 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:
  • If a required field is missing you get an error
  • If any field doesn't match the format an error is returned.
• In case of any error, the invoice is not imported. Fix the problem and then try again with the import.

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,

Estimates

Headers to be used to create a *csv file with Estimates to be imported:

• InvoiceNumber: By Import will be replaced with the internal number.
• InvoiceDate: Required field.
• InvoiceDueDate
• InvoiceDescription
• InvoiceDiscount
• InvoiceVatTotal: Value not used for import, is used to check if after the importation the amounts correspond.
• InvoiceTotalToPay: Value not used for import, is used to check if after the importation the amounts correspond.
• InvoiceCurrency
• InvoiceRoundingTotal
• InvoiceAmountType: vat_excl, vat_incl, vat_none (default is vat_excl).
• CustomerNumber: Required field.
• CustomerName: Value not used for import, contains the Organisation Name or the First and Last Name plus the Locality.
• ItemNumber
• ItemDescription: Required field.
• ItemQuantity
• ItemUnitPrice
• ItemUnit
• ItemVatRate
• ItemVatCode
• ItemDiscount
• ItemTotal: Required field, contains the total exclusive vat if InvoiceAmountType is vat_excl, otherwise it contains the total inclusive vat.
• ItemVatTotal: Not used for import.

Import rules

• The estimate number will be replaced with an internal 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:
  • If a required field is missing you get an error
  • If any field doesn't match the format an error is returned.
• In case of any error, the invoice is not imported. Fix the problem and then try again with the import.

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

Contacts and Items

You can use the Data menu to import contacts and items data in Banana.

The import file format follows the Text file with columns header.

Export

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.