1 Topic by kerrsmith (edited by kerrsmith 04/30/2010 05:39:28 pm)

  • From: UK
  • Registered: 10/11/2009
  • Posts: 50

Topic: Small error on supplier payment report

I have been using FrontAccounting for a while now for my business and everything is going great.

Yesterday I did a Supplier Payment Report and noticed an entry appearing when all my invoices were paid.

I had a bit of a look at the code and manually used the SQL code in phpMyAdmin to see what was happing:

SELECT 0_supp_trans.supp_reference, 0_supp_trans.tran_date, 0_supp_trans.due_date, 0_supp_trans.trans_no, 0_supp_trans.type, 0_supp_trans.rate, (ABS(0_supp_trans.ov_amount) + ABS(0_supp_trans.ov_gst) - 0_supp_trans.alloc) AS Balance, (ABS(0_supp_trans.ov_amount) + ABS(0_supp_trans.ov_gst) ) AS TranTotal FROM 0_supp_trans WHERE 0_supp_trans.supplier_id = '2' AND ABS(0_supp_trans.ov_amount) + ABS(0_supp_trans.ov_gst) - 0_supp_trans.alloc != 0 AND 0_supp_trans.tran_date <='2010-04-30' ORDER BY 0_supp_trans.type, 0_supp_trans.trans_no

This produces the single line containing a balance of -3.5527136788005e-15

This balance should be zero but for some reason it is not quite getting there when doing the balance calculation:

(ABS(0_supp_trans.ov_amount) + ABS(0_supp_trans.ov_gst) - 0_supp_trans.alloc) AS Balance

(ABS(13.7) + ABS(2.4) - 16.1) = -3.5527136788005e-15

I doubt this will happen very often for anyone else but thought I would mention it just in case there is an easy fix to prevent it happing.

  • From: UK
  • Registered: 10/11/2009
  • Posts: 50

Re: Small error on supplier payment report

I just paid another invoice and the same thing happened.

In order to avoid confusion as to what has been paid and what hasn't I have modified the SQL of the report so that the results are rounded to two decimal places:

ROUND(ABS(".TB_PREF."supp_trans.ov_amount),2) + ROUND(ABS(".TB_PREF."supp_trans.ov_gst),2) - ROUND(".TB_PREF."supp_trans.alloc,2) != 0

This has now fixed the problem for me and the paid invoice have now been removed from the report.