Start a Post

Item Purchase Import multiple items zero quantity Error - 25. Import file produced by AccountRight!

Contributing Partner Shawky
6 Posts
Contributing Partner
Australia

Item Purchase Import multiple items zero quantity Error - 25. Import file produced by AccountRight!

Hi

I have gone bald trying to solve this one. My import files worked fine on old Permier 7 but I am having no luck importing a Purchase Order to AccountRight Premier 2016 whenever one or more of the line items has a zero quantity.

The use case is purchasing shares with Commission.

I have created an example WYSIWIG Purchase 1000 shares at $30 for a total of $3000

I add a line item with 0 billed, received, backorder, with price $25 and total $25 with description Commission.

This works fine.

I export it to CSV

I edit the export file to change the Purchase No. and Supplier Inv No.

I import and get Error -25: Invalid amount

If I edit the file and change the quantity for the commission from 0 to 1 it imports OK.

I also tried modifying the Order, REceived and Billed fields to no benefit.

My question is twofold, answering the first would be wonderful

1. How does one import a Purchase Order with multiple items where some should not have a quantity.

2. Why isn't MYOB importing the very file it exported?

 

Example reference for Purchase order setup followed my solution developed in 2005 which is also detailed nicely at:

http://community.myob.com/t5/From-our-Business-Gurus/Handling-a-share-portfolio/ba-p/404735#_ga=1.63...

 

Kind regards

Shawky

6 REPLIES
MYOB Moderator Neil_M
3,672 Posts
MYOB Moderator
New Zealand

Re: Item Purchase Import multiple items zero quantity Error - 25. Import file produced by AccountRight!

Hi @Shawky

 

Welcome to the MYOB Community Forum, I hope you find it a wealth of useful information.

 

When AccountRight imports an item related transaction it checks each line with a simple check of does the quantity multiplied by the rate equal the total figure of that line. If the calculation doesn’t make sense or doesn’t balance, for example 0 x 25 = 25 then it will return an error.

 

If you were importing a transaction where you needed to have a quantity of zero, then the total on that line as well as the tax line would also need to be zero. This will then permit that line to go through the aforementioned check as 0 x 25 = 0 is correct mathematically.

 

In addition, as there are differences in the importing and exporting file formats between the classic version of AccountRight v19 and AccountRight 201X.X, which can cause issues when trying to import files created in one version and imported into another version. If you were importing into AccountRight 201X.X, you could export out the same type of information and then open that in Excel and use that information to check your import file to ensure that it is in the same format.

 

Do let us know how you get on with this,



Kind regards,
Neil
MYOB Client Support
Contributing Partner Shawky
6 Posts
Contributing Partner
Australia

Re: Item Purchase Import multiple items zero quantity Error - 25. Import file produced by AccountRight!

Thanks Neil

I hope I have not missinterpreted your feedback.

Unfortunately, I  think what you have just said is that import of an item with a zero quantity is not supported. I hope this is a mistake, or that you have not informed me of the work around to get past this validation step in the case of valid line entries that do have a zero quantity and unit price and have a non zero total as per the excellent example  from MYOB SupportNoteGuy at Handling a share portfolio

MYOB Premier V7 whilst it has a slightly different CSV format does support import of such line items. IMO, such line items should be supported in some practical way. Even if it requires a specifal code to be included in Order, Delivered or Quantity. e.g "*" or "NA".

The current limitation in AccountRight Premier is preventing me from upgrading from Premier V7.

 

From SupportNoteGuy, the current recommended practice for itemising Commission / brokerage for a share purchase is to include it as an item with zero quantity and a total value. This also happens to be exactly the mechanism my accountant and I determined was most appropriate for managing Contracts For Difference in MYOB back in 2003. I was pleased to see SupportNoteGuys article and other related articles on the topic of managing shares.

 

Would you be so kind as to explain the values of each pertinent property in the Purchase import record that would allow such a line item to be entered into MYOB through CSB Import. As you would know, the user interface allows such transactions and I am quite sure it is not uncommon.

 

Thanks for the prompt reply. Very much appreciated, and yes the community information has been very usefull. It is taking a little while to get the lingo right to come up with related articles, but I'm getting there.

 

BTW, I wonder what the scope is with MYOBapi in this regard.

 

regards

Shawky

MYOB Moderator Neil_M
3,672 Posts
MYOB Moderator
New Zealand

Re: Item Purchase Import multiple items zero quantity Error - 25. Import file produced by AccountRight!

Hi @Shawky

 

As mentioned previously when AccountRight 201X.X imports transactions it runs a number of different checks on the import file, one of which is the checks that I mentioned is whether each line makes mathematical sense and that the quantity of that line multiplied by the value of that line matches the total of that line. If it doesn’t match, then you get that particular error.

 

The workaround I mentioned, where each line has to make mathematical sense and that if there is no quantity then there are no total amounts. If you needed to import an item with no quantity but a total, then you would have to have the import file line have a zero total and zero quantity. This will allow the file to import, and you will then have to go into that purchase order and change the zero amount to the desired amount.

 

The link you refer to shows how to enter that transaction into AccountRight, however this doesn’t translate into the import process because as I have mentioned the import process does run several checks on the information in the import file. Currently there is no way to get around these checks on the import file.

 

For more information on the different fields used during the import process, you may want to have a look at our help article Import and export fields



Kind regards,
Neil
MYOB Client Support
Contributing Partner Shawky
6 Posts
Contributing Partner
Australia

Re: Item Purchase Import multiple items zero quantity Error - 25. Import file produced by AccountRight!

Thanks Neil, you explained how it worked fine the first time round.

I fully understand how it processes the data.

The point I wish to make now is that this is a deficiency in the application.

CSV Export / Import is meant to be a mechanism to allow bulk load and also export from one company file (lets assume with a major problem) into a new or recovered company file.

To enforce manual correction of impored data is a defiency in my opinion.

 

What MYOB has is a disconnect between two portions of the programme with basic functionality.

 

Please tell me that the MYOB API does not suffer the same fate.

 

Sincerely disappointed

Shawky

Trusted Partner eJulia
97 Posts
Trusted Partner
Australia

Re: Item Purchase Import multiple items zero quantity Error - 25. Import file produced by AccountRight!

Hi @Shawky,

 

Your post piqued my curiosity so I tried this in the copy of Transpost I am testing. Sadly I have to report that the API returns "QuantityNotValid: 12100 - ShipQuantity must be non-zero".

 

What is the problem with using the quantity 1? If you actually have a blank in your data because the TransPost soloution to that dilemma is to set a default of 1 for quantity and leave price blank, but then ... MYOB import does not provide for user defaults.

 

 

Julia Roberts
TransPost Support

Phone: 1300 768 993
Email: support@transpost.com.au

Web: http://www.transpost.com.au

Contributing Partner Shawky
6 Posts
Contributing Partner
Australia

Re: Item Purchase Import multiple items zero quantity Error - 25. Import file produced by AccountRight!

Thanks Julia

Your effort has been much appreciated.It is a shame you have confirmed what I guessed would be the outcome.

I have a strong view on these things, and would expect a world class product to provide consistent functionality through the API, CSV export/import as the GUI is so far as recording and maintaining transactions. Whilst it is easy to correct one ledger entry due to an import capability constraint, it is not easy to do so for thousands. My MYOB processing is all automated using broker and bank reports. I was actually hoping transition to the API might make my programme useful to others for managing CFD and Forex.

 

On the weekend, I have tested a combination of Inventory items and Purchase Orders with the trade Commission as its own item. (BTW I have tens of thousands of records to import per year, not just a few).

The MYOB and my accountant's recommended approach (see my first post), is to hold keep the brokerage on the same Purchase Order and inventory item as the underlying share/instrument. To prevent the inventory quantities being wrong, the brokerage quantity and unit price is zero, giving a total or lot price only. This is handy as it increases the cost base of the inventoried instrument to include the brokerage, as expected by my accountant and for reporting holding stock.

 

The work around is to create a dedicated inventoried item (Yes, unfortunately it has to be inventoried for this to work and have quantities != 0) for the Brokerage or Commission. The operning/closing stock total value is the sum of the brokerage items and share/instrument items. But the actual cost of an individual share/instrument Contract is not immediately available (note that the practice for shares is to create a new inventory item for each share parcel or Contract, that way the cost base is not averaged across multiple purchases and that sales may uniquely identify short term/long term holds etc etc).

 

I have tested having a common item for commission per broker that needs to be included as closing stock.

 

So the downside is possibly only irritating and I have to re write my existing VBA code which is longggggg in the tooth. There is also a risk that the solution gets out of hand with more and more inventory items. Also, it may not be immediately visible the true cost of an instrument held across the financial year. As a total, iit is fine, so reporting closing and opening stock is OK. Manually spot auditing is a little harder.

 

BTW, still waiting for API access to get sorted, so I can't move forward at all.

 

Once again, thanks for your assistance, at least I won't have to test it in the API and waste more time.

 

kind regards

Shawky

Didn't find your answer here?
Try using advanced search to find a post more easily Advanced Search
or
Get the conversation started and make a new post Start a Post