Erik, I agree with your root cause. Your solution may be the right one,
given the circumstances, although without looking at the actual join, i
would have thought that the inner join with the additional "account
type" restriction would have worked.
I can tell you I was bothered by the fact that the the "id" field in the
chart view was not a primary unique key. (Effectively adding the
"account Type" restriction does that) If it had been unique, then
neither of my issues would have happened. I bet some time in the past
the account_headings and the accounts were in the same table. The
unintended consequence of splitting them into two tables was the non
unique keys in the chart view. The safest, simplest solution is to
insure unique keys in the chart view. Hopefully 1.4 will do that.
The really good news about all of this is that none of my data is in any
way corrupted, as this was only an error in the reporting of the data.
The GL is intact.
Thanks, for getting to the bottom of this. Quite honestly, I can wait
for 1.4 or never, and just ignore the few errant reports. My data is
good, which was my primary concern.
Regards,
Bill Ott
Email: Mailto:bill...@theotts.org
Website: http://www.theotts.org
On 07/14/2013 05:22 PM, Erik Huelsmann wrote:
Hi Bill,
On Sun, Jul 14, 2013 at 11:09 PM, Bill Ott <bill...@theotts.org
<mailto:bill...@theotts.org>> wrote:
Erik, I looked the the "chart" view. It only lists the account and
the account_headings and all of the id's. No linking was apparent.
There is no monetary data shown in the view, as there is when
looking at reports->COA from the web interface.
Thanks for your additional analysis. It allowed me to find the cause
quite quickly. I have identified the problem to be in
LedgerSMB/RP.pm::&get_accounts(). The issue is in the query at line
927 which joins transactions with the id column in the 'chart' view.
If the intent of the report is to show monetary information, then
providing a summation for the accounts under the heading make
sense. In my case the "summation" appears to be just reflecting
the amount from the account "id" that matches the account_heading
"id".
Yea. The intent of the report is to show monetary info on each
account, just to find accounts on which balances are being held. The
hard part of trying to solve this issue is that I know for a fact that
this code has been replaced in 1.4 already: the entire code in RP.pm
has been replaced by much more robust code which looks more 2013 as well.
My idea would be to change the (INNER) join between acc_trans and
chart to a LEFT or RIGHT join with an additional join restriction: the
account type being 'A' (thus not being 'H').
Regards,
Erik.
Regards,
Bill Ott
Email:Mailto:bill...@theotts.org
Website:http://www.theotts.org
On 07/14/2013 04:37 PM, Erik Huelsmann wrote:
On Sun, Jul 14, 2013 at 10:29 PM, Bill Ott <bill...@theotts.org
<mailto:bill...@theotts.org>> wrote:
> For example 1000 Current Assets lists only the checking
account debit. The AR debit
> appears in the Capital Assets debit heading. 1820 Office
equipment
> appears under 3300 Share Capital. and so on.
I have been digging around and here is what I've found. in
every case, where an amount is shown on a "heading", the "id"
from the account_heading table matches the "id" from the
account table. The id for the "1000 Current Assets" is 1. The
"id" for "Checking Account" is 1. The "id" for "3300 Share
Capital" is 6. The "id" for "1820 Office Equipment" is 6, etc.
It appears that there is a join between the "account" table
and the "account_heading" table using "id" from each.
That's correct: in LedgerSMB 1.3, there's a "chart" view which
does exactly what you describe. Maybe you could check that and
see if it presents you with incorrect data? If that's the case,
we at least know the cause.
The solution for the CoA report is probably not to use the
'chart' view, but that'd be the next step.
--
Bye,
Erik.
http://efficito.com <http://efficito.com/> -- Hosted accounting
and ERP.
Robust and Flexible. No vendor lock-in.
------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-users mailing list
Ledger-smb-users@lists.sourceforge.net
<mailto:Ledger-smb-users@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-users mailing list
Ledger-smb-users@lists.sourceforge.net
<mailto:Ledger-smb-users@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
--
Bye,
Erik.
http://efficito.com <http://efficito.com/> -- Hosted accounting and ERP.
Robust and Flexible. No vendor lock-in.
------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-users mailing list
Ledger-smb-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
------------------------------------------------------------------------------
See everything from the browser to the database with AppDynamics
Get end-to-end visibility with application monitoring from AppDynamics
Isolate bottlenecks and diagnose root cause in seconds.
Start your free trial of AppDynamics Pro today!
http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-users mailing list
Ledger-smb-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
