Out of balance error with importing IIF invoice transaction

We are trying to import data out of a CRM system into Reckon Accounts. The transaction type is an invoice with multiple line items. The issue we face is that the CRM system calculates the GST from the invoice line totals and then rounds it accordingly. The ex-GST total amount and the rounded GST total amount are added together to provide the figure in the TRANS! AMOUNT value.

The issue arises because the GST amounts in the SPL! TAXAMOUNT fields, when totalled, are not rounded and we receive $.0.01 variations and the subsequent out of balance error (See image)

We have tried leaving the SPL! TAXAMOUNT fields blank and hoping that Reckon would auto-calculate the GST on import, but we still receive the error.

Any ideas on this one. I'm mostly looking for what are the minimum requirements for this import in terms of the required data and what Reckon will autocreate.

Worst case scenario for us is that we bring in a calculated amount into the TRANS! AMOUNT field. This raises a new issue with payment receipts being $0.01 out.

Thanks
Glenn

Comments

Datarec

Hi Glenn,

Can you stop the rounding that is being done by your CRM system?

Does your CRM system generate the IIF file itself or does it export reports which you subsequently transform into an IIF using other 3rd-party software?

You cannot leave out values in the IIF file, there is no auto-calculation being done upon importing.

* Data Recovery Team *

Glenn Houssenloge

The CRM system produces its own invoices, so this rounding is done that that level. Then those amounts are populated into an ARHeader table for the batching process. This relates to the TRANS! information. There is another table they call the ARDetail, which holds all the SPL! data.

We then configure it to produce the iif file based on the information required.

Ive emailed the vendor to see if we can tweak it, but I dont believe we can. The thing is that they have hundreds of QuickBooks customers in the US who at some stage must have faced this same issue, but it might be GST related issue.

cosmic

Hi Glen , why do you have to use iif tool?
we have other automated options?

Datarec

Hi Glenn,

In the USA QuickBooks product, the rounding per line is the norm. whereas in the Australian version, Reckon Accounts rounds the end total.

If you absolutely must use the output from the CRM system without being able to control the rounding, then you can consider making use of preprocessing scripts in Excel to adjust the TRANS amount value by calculating the sum total of the SPL lines.

* Data Recovery Team *

Graham Boast

Hi Glenn

I'm not quite sure how much control you have over the creation of the iif file, but rounding the price to the allowed 5 decimal places rather than 2 may help.

For example, the 2nd split line is obviously 10 units @ $10 Gross. It looks as if your program has calculated the net unit price (9.09) then multiplied by 10 = 90.90. If you could set the price to 5 decimal places, it would be 9.09091 x 10 = 90.91. For this transaction at least, there would be no error.

If you have no control over the export, you may be able to set all your prices so the net price is a multiple of 10 cents (ie Gross Price / 1.1 finished in a zero - .00 .10 .20 .30 .40 .50 .60 .70 .80 .90. That way, you will never get a rounding issue.

While you are talking to your vendor, get them to put an INVITEM value in the split line. This could be the actual item code if you are running inventory, or generic Non Inventory items such as "Parts" and "Labour" linked to their respective account. (if possible, use several "Category" items for Parts such as "Cables", "Switches", "Lights" etc). This will give you two things over your current import: better data (eg units of labour); and it will make the invoice much easier to edit post-import.

Graham Boast 0409317366
Reckon Accredited Consultant
graham@reckonhelp.com.au
http://reckonhelp.com.au/remotesupport.htm

Glenn Houssenloge

Hi Chandra,

We have an automated tool. This is for a particular customer and its not an option right now.

Hi Graham,

We could definitely take it to 4 decimal places and it may work if Reckon allows for an input of 4 decimal places and factors that in it's calculation of the SPL! lines? In fact the raw data out of the CRM system has 4 decimal places. We round it to 2 for obvious reasons.

As for the INVITEM we are outputing that as well. Its not shown in the image but its the next column after MEMO.

I'm going to give the 4 decimal places a try and see how we go. Thanks for the info.

Glenn

cosmic

Thanks, Glen, Take what Graham is suggesting, Used iif options for 17 years, or so , always had issues,should read my article on :workarounds vs automation"
just curious, if you have an automated tool, then, there should be no need to use iif.

Datarec

Hi Graham, the Data Recovery Team really appreciates your contribution to the Reckon Community with accurate, useful and clear information.

* Data Recovery Team *

Glenn Houssenloge

Graham,

That worked. Thanks for the tip. Taking it to 4 decimal places allowed Reckon to round it correctly and fixed the issue.

Glenn