Best way to import old purchase & sales invoices

I have finished setting up ERPNext with all my customers, contacts, items, addresses, settings. The only thing that is left is importing my old purchases and sales invoices.
I am trying to figure out the fastest and the best approach to import invoices, but the issue is, item and customer codes have been changed to follow ERPNext naming series.
So I am thinking of manually entering data in the following steps;

  1. Create a purchase invoice, set invoice as paid and enable “Update Stock” option
  2. Create a sales invoice and enable update stock option
  3. Add payments to sales invoice

Now I believe this should work properly, however it is very time consuming, subject to errors, and I have no idea how to set an invoice to a different fiscal year.

Is there any method that is faster and more efficient to import all my previous invoices?

Note: I want this to be as accurate as possible so we can go on with auditing.

Please assist me
Thanks

Any suggestions on this?

Good day @Yamen_Zakhour

I was in a similar situation And I decided to take the long road.
I simply had all the information handy e.g. invoice, bank-statement with the payment and I
entered it by hand. Yes , it is easy to make a mistake, which I did. Lucky I could limit that
to 2 mistakes over a spread of 200 invoices … ( not bad I thought ! ! )

And yes, you are right, one does not have to do the entire array of docs i.e. material request,
PO, PINV, Payment. All I did was to enter PINV, mark the “Update stock” and then enter the
payment. I used my back-statement to determine the order in which to enter the invoices and
at the end of the month, I did a journal to re-concille the bank-charges and interest.
And yes it is a long process but I felt the same as you , in that I wanted to know that it was
accurate.

Your question about fiscal year. Just type into the awesome bar “fiscal year” and you
can create other fiscal years.

When you enter the invoice , just select the radio-button to “Update posting date and time”
and you can enter any date. You can also change the posting date of the payment.
I found that the place that I made a mistake the easiest, was the posting date of
the payment. So I just got into a habit of FIRST changing the dates on any document
that I create. Then I focus on the actual financial data.

I also created a habit of regularly going to the GL to checked the entries and also to check
my creditors account … if that was in order i.e. zero. I also regularly scanned the
invoice and payment list to see if I can pick up an incorrect posting date.

This entire process does sound long but it is a lot easier if you spend some some
time beforehand to set up your items correctly … your warehouses … your accounts…
etc … and also your addresses and contacts.

Some heads-up… if you want to use Cost Center … please make sure you enter that.
This does not auto-load from the company-master and I had to do some SQL
queries afterwards to add the cost center. I have raised this an an issue with the DEVS
but I see they have not had a chance to look at this. I feel that the default
Cost center should be auto-loaded. So just watch for that.

This is not the answer you were looking for … but I thought I would make this post.

Best

1 Like

Thanks for sharing your experience, as far as I understood the documentation that is the only way possible to import invoices.

So basically I will be assigning a data entry company for this so may I confirm if the following steps are correct in entering the data;

  1. Create fiscal years for all invoicing years

  2. From sales settings, disable “require sales order to create invoice” and “require delivery receipt to create invoice”

  3. Create a purchase invoice
    3.a. Enable modify date and time, and modify them according to the invoice
    3.b. Set cost center.
    3.c. Mark invoice as paid
    3.d. Populate customer and items
    3.e. Enable update stock option
    3.f. Save & Submit

  4. Create a sales invoice
    4.a. Enable modify date and time, and modify them according to the invoice
    4.b. Set cost center.
    4.c. Populate customer and items
    4.d. Enable update stock option
    4.d. Save & Submit

  5. Add payments to invoice

Did I miss any essential step or are these enough to record all my invoices?
Thanks!

Hi @Yamen_Zakhour

I think you have hit the nail on the head !

Just a type …I think 3(d) should have been … Populate supplier and Items.
Personally I did not do 3(c) but that was because I used the “Create” …“Payment”
drop-down option from PINV and created an actual payment based on my bank-statement.

The only thing that I also would add, is regular “checkups” … i.e. what makes you comfortable
that your data is accurate .

As I mentioned, I cross-referenced my GL entries regularly, checked that I did not make
mistakes with my “posting dates” ( easiest mistake to make !!! ) and also check my
creditors account to be zero, which means all payments are up to date.
In my case I also had month-end journal entries when I could check my bank-balance
against my bank-statements. Or any checks that makes you feel that things are going
well. Perhaps it would be a good point to ponder on your side before you go diving in.

And may I offer some advise that I enforces onto myself to prevent the possibility of errors,
and I don’t mean this as an insult to you … and that is to recognise if you are tired. The urge
would be to push and get everything into ERPNext, but I have noticed that , that is the time
that I made mistakes. Whenever I got to an import-transaction and it was 22h00, I left it
until the following day. This may seem a silly comment but it has saved me a few times.
ERPNext handles corrections rather well, but one would like to enter all info without having
to cancell invoices and payments etc.

Best

1 Like

Thanks for the advice!
I agree with everything but I figured out that ERPNext doesn’t actually handle corrections very well. Despite having “Delete Accounting and Stock Ledger Entries on the deletion of Transaction” enabled from account settings, I attempted to delete an invoice but it actually keeps the GL Entry. Eventually I had to reset my ERPNext because I ended up with multiple GL Entries that are not referenced to any invoice, and I couldn’t delete them.

That’s why I’m asking this question just to make sure from the steps as I wouldn’t want that to happen again.

Thanks for your time and suggestions.

Hi @Yamen_Zakhour

Perhaps we can compare procedures for corrections and maybe learn from each other.

I had one PINV that was not correct and I “Cancelled” it but did not delete it. I then went
ahead an “Amend” it ( all these actions are top right ).
It cleared the invoice from GL and entered an amended PINV.

The mistake I made , was that I did not select “Packet” so that ERPNext can do the UOM
conversion. So it priced the purchase of a packet of O-rings instead of the individual O-rings.
So the totals were all correct and when ERPnext unlinked the payment, I could amend the
PINV and re-link the payment.

Stock entry was corrected correctly as well.

But thats why I made the suggestions that I made. because I understand that you want
to get through this exercise without having to make coorections ! :smiling_face:

How many PINVs / SINV do you have to enter ?

1 Like

So basically we operate a pharmaceuticals distribution business and invoices usually include “bonus products”, meaning that for example if we purchased 100 Panadol packs for 20$ we get an additional 15% of Panadol packs as a bonus from the supplier. So we usually calculate the purchase price at 115/20 which is 5.75.
Our previous accounting company were not familiar with this so all of our invoices were faulty and correcting them would take more time than actually redoing them. So I had to delete all the PINVs (after I made sure that “Delete ledger entries on deletion of transaction” was enabled).
However as I explained earlier the GL Entries were still recorded eventhough I deleted all invoices.

We have around 100 PINVs to add and a lot of sales invoices (We operate B2B and B2C and have a lot of invoices from Amazon and other local online marketplaces).

Speaking about that I’m also trying to figure out a method to implement a “bonus” pricing rule such as Free items after certain qty of purchase but can’t figure out a method.

Also can you suggest how you deal with product samples & invoicing? For example if we made a campaign and handed out samples of a product, how should we invoice that?

Hi @Yamen_Zakhour

Perhaps you should open another thread for this. Otherwise the thread will digress …

I will have to think about the first one…

The second one … just had a quick think about it. ( If I think of something else I shall comment)

Invoice the item as per normal so that stock is removed at proper stock value
Then use “Taxes and Charges” table to put that against write off ( at negative value )

Item table
SKU00001 Tea cup 1 x R10.00

Taxes and Charges
Actual Write-off -R10.00

Grand total R0

Waking up this thread, since its version 13 and I am facing a similar problem. I am importing Purchase Invoices of an older system. Created the items and supplied data. While Importing the data from a csv, I did not enter the GST tax values which I think should be calculated automatically.
But that is not happening.

Does it mean that while importing purchase invoices, I will have to add the GST column values as well, because that gets tricky since various GST parameters have to be done manually.

I think tax calculation is done while creating a purchase but it does not automatically calculate the tax values if records were imported.
To be honest as much as you research this you will figure out that the best way to import previous data is to manually enter them.
If you decide to handle this manually like I did, my best suggestion is to modify the doc to your needs. For example, when I was entering purchase invoices, I did the following changes:

  • Changed “modify date & time” to enabled (1) by default
  • Changed “Is paid” to enabled by default
  • Changed “Update Stock” to enabled by default
  • Changed “Cost Center” to have my default CC populated.
  • Hid multiple sections that would not affect my transactions (Subscription, More Information, Print Settings, ETC…) – To reduce scrolling

With these changes, my average was almost an invoice per minute as I only had to enter customer, date, items and enter paid amount.
Once you are done just remember to reset the document to default fields.
Goodluck =)

Edit: Also a suggestion, keep everything as “saved” and don’t submit invoices until you are done entering all records.
Just in case you figured out a mistake, considered a better approach in entering or wanted to edit/bulk update the invoices, you wouldn’t have to cancel and amend every record.

This is correct.

Some of the Tax records are only added if you enter the document (e.g. Purchase Order/Invoice, Sales Order/Invoice), through the web UI. This is because there is frontend JavaScript code, that does not have equivalent backend Python code.

Creating invoices with Data Import or API calls may result in missing Tax data.