Need advice...

Hello folks,

LSMB 1.2.5 with updated Form.pm.

I have a weird problem that I've been trying to (futilely) solve for 
three days.  A user reported a problem that I can't replicate.  Here's 
what happened, according to the user:

She needed to return money to a customer who over-paid by check.  To do 
this, she:

1) went into AR -> Add Transaction
2) made sure the correct user was chosen and entered the invoice number
3) put in a total of -4.00
4) set the AR account to 4012 (account used to track returns)
5) entered payment
6) hit update to make sure all is correct
7) posted the transaction

Afterwards, she went back and reviewed the transaction to make sure all 
worked as intended and noticed that there was an NaN.00 in the payment 
field.  I went in to view the transaction in question and she's right.

A couple of days later, a different user reported that the trial balance 
report and several others are missing accounts, throwing off the totals 
and dates by a lot.  In fact, it's missing every account that has one or 
more records with "NaN" as the balance.

A couple of questions:

Re: Need advice...

On 6/30/07, Charley Tiggs <lists@...> wrote:
> Hello folks,
>
> LSMB 1.2.5 with updated Form.pm.
>
> I have a weird problem that I've been trying to (futilely) solve for
> three days.  A user reported a problem that I can't replicate.  Here's
> what happened, according to the user:
>
> She needed to return money to a customer who over-paid by check.  To do
> this, she:

>
> 1) went into AR -> Add Transaction
> 2) made sure the correct user was chosen and entered the invoice number
> 3) put in a total of -4.00
> 4) set the AR account to 4012 (account used to track returns)
> 5) entered payment
> 6) hit update to make sure all is correct
> 7) posted the transaction
>
> Afterwards, she went back and reviewed the transaction to make sure all
> worked as intended and noticed that there was an NaN.00 in the payment
> field.  I went in to view the transaction in question and she's right.
>
> A couple of days later, a different user reported that the trial balance
> report and several others are missing accounts, throwing off the totals
> and dates by a lot.  In fact, it's missing every account that has one or
> more records with "NaN" as the balance.
>

Re: Need advice...

Chris Travers wrote:
> On 6/30/07, Charley Tiggs <lists@...> wrote:
>> Hello folks,
>>
>> LSMB 1.2.5 with updated Form.pm.
>>
>> I have a weird problem that I've been trying to (futilely) solve for
>> three days.  A user reported a problem that I can't replicate.  Here's
>> what happened, according to the user:
>>
>> She needed to return money to a customer who over-paid by check.  To do
>> this, she:
> 
> 
>> 1) went into AR -> Add Transaction
>> 2) made sure the correct user was chosen and entered the invoice number
>> 3) put in a total of -4.00
>> 4) set the AR account to 4012 (account used to track returns)
>> 5) entered payment
>> 6) hit update to make sure all is correct
>> 7) posted the transaction
>>
>> Afterwards, she went back and reviewed the transaction to make sure all
>> worked as intended and noticed that there was an NaN.00 in the payment
>> field.  I went in to view the transaction in question and she's right.
>>
>> A couple of days later, a different user reported that the trial balance
>> report and several others are missing accounts, throwing off the totals
>> and dates by a lot.  In fact, it's missing every account that has one or
>> more records with "NaN" as the balance.

Re: Need advice...

Ok, the first thing to do is to try to get rid of these erroneous
records.  This means correcting the data at the database level.

netamount should be 0 in this case.  It looks like you may need to
update the acc_trans table to set that to 0 instead of NaN.  I.e. back
up the data, (you may also want to select into a new table also) and:
UPDATE acc_trans SET amount = 0 WHERE amount = 'NaN';

Once this is done, set up constraint in the database:
ALTER TABLE acc_trans ADD CHECK (amount <> 'NaN');

This way  a bad invoice will cause an error instead of posting odd
data.  This also means more immediate support calls and a better
chance of tracking this down.

Best Wishes,
Chris Travers

> Sent you 1 of the 4 bad records I found privately.
>
> Charley
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2 express and take
> control of your XML. No limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> Ledger-smb-users mailing list
> Ledger-smb-users@...

Re: Need advice...

Chris Travers wrote:
> Ok, the first thing to do is to try to get rid of these erroneous
> records.  This means correcting the data at the database level.
> 
> netamount should be 0 in this case.  It looks like you may need to
> update the acc_trans table to set that to 0 instead of NaN.  I.e. back
> up the data, (you may also want to select into a new table also) and:
> UPDATE acc_trans SET amount = 0 WHERE amount = 'NaN';

Database backed up and update done without issue.

> Once this is done, set up constraint in the database:
> ALTER TABLE acc_trans ADD CHECK (amount <> 'NaN');
> 
> This way  a bad invoice will cause an error instead of posting odd
> data.  This also means more immediate support calls and a better
> chance of tracking this down.

Done.

Thanks, Chris.  Much appreciated!

Charley

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/

Problem with Upgrading from 1.1.12 to 1.2.4

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/

_______________________________________________
Ledger-smb-users mailing list
Ledger-smb-users@...
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users

Moving from SQL-Ledger

I currently run sql-ledger 2.6.20.  I'd like to move to ledger-smb.  Other than backing up my database and
templates, is there anything else I should be concerned about?

Thanks.

Danita

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/

Re: Problem with Upgrading from 1.1.12 to 1.2.4

On 7/2/07, Rodolfo Rodriguez <rodolforodriguez_us@...> wrote:
<snip>
>
> when i try to login in the application 1.2.4 I get the error: column 'value'
> doesnt exist in the table defaults

Ok, the sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql makes a number of
alterations to the database schema.  It seems that this either did not
run or rolled back.  Run this script in psql and if it still doesn't
work, send us the output.

Best Wishes,
Chris Travers

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/

Re: Moving from SQL-Ledger

On 7/2/07, Danita Zanre <danita@...> wrote:
> I currently run sql-ledger 2.6.20.  I'd like to move to ledger-smb.  Other than backing up my database and
templates, is there anything else I should be concerned about?

Usually the upgrade goes fine.  Back up your templates and your users directory.

If you run into trouble, write back here and we can help.

Best Wishes,
Chris Travers

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/

Re: Problem with Upgrading from 1.1.12 to 1.2.4

Chris Travers wrote:
> On 7/2/07, Rodolfo Rodriguez <rodolforodriguez_us@...> wrote:
> <snip>
>> when i try to login in the application 1.2.4 I get the error: column 'value'
>> doesnt exist in the table defaults
> 
> Ok, the sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql makes a number of
> alterations to the database schema.  It seems that this either did not
> run or rolled back.  Run this script in psql and if it still doesn't
> work, send us the output.

You may also want to verify which postgresql version you are running.

Joshua D. Drake

> 
> Best Wishes,
> Chris Travers
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by DB2 Express
> Download DB2 Express C - the FREE version of DB2 express and take
> control of your XML. No limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> Ledger-smb-users mailing list
> Ledger-smb-users@...
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
> 

--

-- 

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/