The bill import tool is unusable

Problem reported by Richard Cooper 10 years ago

I'm referring to https://secure.clearbooks.co.uk/demo/accounting/importer/import-bills/

  1. Why is "Bill no." a required field rather than being automatically calculated like it is everywhere else in ClearBooks? It means I can't automatically generate the cvs files without knowing which ClearBooks IDs are in use.

  2. It's also really easy to mess things up with "Bill no." because there is no format checking. The system happy accepts "This is garbage" as a Bill no. which creates an approved invoice which is invisible in the "All bills" list.

  3. I set "Currency code" = "USD" and "Multicurrency net price" = some positive number. The resulting invoice is in pounds and all of the line items set to unit cost = net cost = 0.00

  4. There's no way to specify a VAT treatment or exchange rate or to create an invoice in the "draft" status.

2 Replies

Hi Richard

Importing Bills feature is used by Clear Books users to import bills raised in a different system and also while importing historic data. Hence there has to be a wide margin of bill numbering and formats. To answer your questions in the same order,

  1. On import, the Bill number has to be specified since system needs a reference when posting it. Clear Books requires unique reference.

  2. Users may be downloading from a different software and uploading into Clear Books, one does not know what the bill number format would be.

  3. To import multi-currency invoices, Line Net price should be your base currency price in this case say GBP, Line VAT rate will be the VAT rate that you want to use and the multi currency net price and multi currency VAT should be in the currency you need to import ie USD values. See below a sample.

Example upload: Base currency GBP and importing USD invoices

You need all the information below in a columnar form as a csv and the map them to the table once you import the csv

Invoice number : 123PRF

Invoice Date : 14/10/2015

Customer Name : Jo Bloggs

Line description : Cost of licenses

Line Account Code : 1234

Line Quantity : 1

Line net Price 500.00 (GBP)

Line VAT Amt 100.00

Line VAT rate 20%

Currency Code USD

Multi Currency net price 762.66

Multi Currency VAT amount 152.53

4.VAT rate can be specified but if you import VAT amount and rate, amount would supersede the rate. Hence best to import the net amount and the VAT rate allowing the system to post the VAT. Creating invoices in Draft is not possible but on import you can check if you need to post and delete before posting.

Caution: Always take a full back up of the data before you upload any invoices since one posted, there is no way of mass deletion of imported data.

Hope this has been helpful.

Best regards

Latha

  1. I know Clear Books requires unique reference per invoice. But when I create an invoice via the website or the API it creates that reference for itself without any input from me (usually in the format PUR000123). Why can't the import tool work the same way? I don't want to have customised bill numbers. I just want to use the Clear Books ones. But as things stand I need to check which bill numbers are available when generating the csv file. Then, if any of my staff add an invoice between me generating the csv file and importing it it will cause the import to fail.

    I understand that there needs to be a way for clearbooks to work out which rows in the CSV file belong to the same invoice but why not have an id column which is only used for that purpose during import and is then discarded.

  2. It looks something has changed here. Trying to use "This is garbage" as a bill number now breaks in a different way. Instead of creating one invoice with multiple line items it now creates multiple invoices with one line item each, all with the same bill number. Which I think proves my point that this area of code is very under-tested and buggy.

  3. OK so instead of supplying an exchange rate and USD amount, i supply a GBP amount and USD amount. That makes sense and is something I can work with. Thank you.

    Except that - again - there is distinct lack of error checking. I can use wildly different exchange rates for each line item without any errors. This is invisible when I look at the invoice on the website because the website doesn't display per item GBP amounts, only an aggregated exchange rate for the whole invoice. The error only becomes visible when I look at the raw transactions.

    Now, obviously I can just be careful and make sure that my input file doesn't contain that sort of error. But a lack of basic error checking which could lead to subtle and hard to spot errors in my accounts doesn't exactly fill me with confidence.

  4. I'm not talking about VAT rates. I'm talking about "VAT Treatment", the extra field you get when you enable the "Reverse charge & EC rates" feature. I wouldn't mind not being able to set it so much if the imported invoices respected the supplier's default VAT treatment. But they don't, they are always imported as "Goods or services from UK supplier"

Reply to this problem

Attach images by dragging and dropping or upload
 

Your comments will be public and can be answered by anyone in the Clear Books community.

Find out what we do and who we are