🐛 [Bug] - Trial Balance and Balance Sheet showing Incorrect Data

njmulsqb commented 5 months ago

Expected Behavior

When I add a journal entry, I want to check my assets and current bank balance and it should show correct amount as mentioned in Setup --> Chart of Accounts --> Current Assets --> Bank

Current Behavior

Instead it shows completely different and incorrect values

whereas the correct one is :

Steps to Reproduce

No response

FrappeBooks Version

0.20.0

Path or Feature name

Trial Balance

Country

PK

Language

English

OS

Macos (Apple Silicon)

Additional OS Info

No response

Reference: https://github.com/frappe/books/discussions/730#discussioncomment-6977228

mildred commented 5 months ago

I don't know how those reports are working either but possibly some transactions are not counted depending on the report settings.

What I suspect is that old transactions outside of the time range are not counted and I'm not sure this is a bug or if it is the way the report is supposed to be shown.

edit: but I agree with you that I'm missing a report showing the same amounts as in shown in the account tree.

It would be good if you could link a demo company file that has the problem to this issue.

njmulsqb commented 5 months ago

@mildred you may use this file to find the issue: https://pern-my.sharepoint.com/:u:/g/personal/najam_26356675_talmeez_pk/EUI4HDTD6dRKpk34xoFD0XoB_CZc06blBLZMma7omqxe6g?e=o4gttJ

njmulsqb commented 5 months ago

Any update with this?

Jace996 commented 5 months ago

@njmulsqb Not sure your problem is same with mine #819 My all reports messed up because of the date issue.

njmulsqb commented 5 months ago

@njmulsqb Not sure your problem is same with mine #819 My all reports messed up because of the date issue.

Maybe Jace, I have started using Gnucash in parallel hoping that this bug resolves soon. Gnucash though design-wise is not so good but its features are solid and reports are properly calculated. I think in finance numbers matter more than looks.

Isaac-GC commented 5 months ago

Hi @njmulsqb,

I've narrowed down where the issue is and am working on it. There should be a fix out within the next day or two.

Isaac-GC commented 5 months ago

Just a general update: From what I'm seeing, the issue you are having is related to DateTimeStamp issue that is in the other bug linked above as well.

There currently is a PR open https://github.com/frappe/books/pull/831 for this issue and I'm working on testing database patches to fix the affected items.

njmulsqb commented 5 months ago

Thank you for updates Isaac. Looking forward to a stable version.

Isaac-GC commented 5 months ago

v0.21.1 patch update was just released. Can you validate the information shows as expected now please?

njmulsqb commented 5 months ago

v0.21.1 patch update was just released. Can you validate the information shows as expected now please?

Unfortunately, numbers are still inconsistent in reports. Infact, I didnt see any change in the figures of reports.

Isaac-GC commented 4 months ago

@mildred / @18alantom The numbers look to be populating/aggregated properly (even after the datetime issues) in the various report views mentioned in comment#1.

Would you be willing to see if there are differences or issues that I'm missing please?

njmulsqb commented 3 months ago

Any update on this?

Isaac-GC commented 3 months ago

Hi @njmulsqb, I was hoping to have some correlation with findings from others above and also ended up getting sidetracked with other issues/feature-requests.

I did some manual accounting to validate what the outputs should be and came up with the below outputs. (please note the last item should be negative and is a typo)

I don't know if this is another bout of datetime issues, but pretty sure it is part of it. When using the General Ledger and changing the date to 18 January, an entry that shows as 17 January in the General Ledger (I.e. JV-1040) disappears.

Investigating deeper

There are three tables (at least) in the database that are part of the backend logic that build the numbers for the view for General Ledger. These tables are:

JournalEntry (stores most of the data)
JournalEntryAccount (Which stores the debit/credit amounts per item in JournalEntry)
AccountingLedgerEntry which actually stores the data used in the General Ledger (and other items under Reports or that extends the Reports class)

The items in JournalEntry/JournalEntryAccount table gets sync'd/copied over to the AccountingLedgerEntry table. Looking through the original database you provided above, I found that the date format is 2024-01-19 in the JournalEntry table whereas in the AccountingLedgerEntry table, the format is actually a datetime type 2024-01-18T19:00:00.000Z.

When this happens, the date format gets modified to a datetime format and due to the fun nature of datetime in Javascript, the date gets modified according to your timezone. So in the case above, when converting 2024-01-19 to datetime to Zulu (or GMT) time, the actual Zulu/GMT time is 5 hours behind your local timezone which then changes the date to 2024-01-18T19:00:00.000Z (18 Jan 2024 7pm GMT).

Patching it should be relatively simple... and probably will be done in the next version (If the above is indeed the issue, a fix will be pushed midweek). But for a larger scale, this brings up a valid issue with patching (see https://github.com/frappe/books/issues/867) and the overall way the schemas are managed (future work TBD)

frappe / books