Sage 50 - Journal Entry Dates

Sage 50 - Journal Entry Dates

Didn't find your answer?

If I run the report in Company > Nominal Ledger > Day Books: Nominal Ledger without setting any date criteria, I get a list of all Journals with overall totals for Debits and Credits that balance.

However, if I run the same report and set a specific date in the criteria (i.e. all transactions dated 31/08/09) I get a list of all journals on that date BUT the totals for Debits and Credits do not balance.

Can anyone please explain? How can one leg of a Sage journal have a date different to the other one?

Replies (29)

Please login or register to join the discussion.

avatar
By johndon68
20th Jul 2012 10:54

Journals

Opposite sides of journals can't have different dates (at least not without the data being edited using the Sage Developer Tools).

Couple of things:

1. I assume no other criteria, other than date range was used on the report?

2. Were any nominal codes selected in the list prior to running the report?

3. Does Check Data return any errors?

john

Thanks (1)
avatar
By Richard Willis
20th Jul 2012 11:19

The thing that caught me out in Sage

was that if you highlight, intentionally or otherwise, any line(s) in a master list any report run will return for that/those account(s).  You may THINK that you haven't specified any account but............

Thanks (1)
Replying to johngroganjga:
By brucedenney
23rd Jul 2012 12:16

not always

That is not always the case.

Reports can ignore the selection of what is highlighted, and with the Day Books: Nominal Ledger report this happens to be the case.

(If you want to use this feature, the mechanism for it is not obvious, you have to have a criteria and you have to name it exaclty the same as the key field name in Sage, only then does it work, you can also do the reverse and rename the "key" criteria field to prevent this from happening on reports)

Thanks (0)
By Craigie_Bhoy
20th Jul 2012 12:15

yep totally agree with the above posts.

Either some other criteria was input, such as a nominal code range (which didnt include one side of the journal) or one of those annoying things Richard mentions when you highlight a code in blue prior to running the report and get nothing like what you wanted spewing out the other side!

 

Thanks (0)
Adrian Pearson
By Adrian Pearson
20th Jul 2012 15:15

Seemed like plausible answers but ..

I closed down Sage and restarted it - which I assume must have cleared any items inadvertently highlighted?

Criteria for report:

Report totals:

 

Check data returns no errors.

So, it seems we need a different answer?

Adrian

Thanks (0)
Dennis Howlett
By dahowlett
20th Jul 2012 15:26

This is not new

I saw this problem back in oooh....1994-5 with earlier iterations. It's a bug methinks. 

Thanks (0)
SiWoodhams
By Si_Woodhams
20th Jul 2012 16:12

@Adrian

Does you TB at the moment balance?

Thanks (0)
Adrian Pearson
By Adrian Pearson
20th Jul 2012 16:25

Trial Balance does balance

Yes, the trial balance (at 29/02/12) does balance. A full Nominal Daybook report balances. Same report filtered for date on 31/08/09 only does not balance.

Puzzled!

Thanks (0)
avatar
By johndon68
20th Jul 2012 21:26

Audit Trail

Has the Audit Trail been cleared at all?

Based on the image you posted, it does appear to be a single journal that is missing - if you look at the one that does appear on the report in Financials, does the opposite entry appear either side of it in the transactions list.  If not it would suggest that the entry has been removed during a Clear Audit Trail - that would certainly explain how the TB balances with an apparent 'missing' journal...

John

Thanks (0)
SiWoodhams
By Si_Woodhams
20th Jul 2012 17:00

Foriegn Bank Transactions

Do you use foriegn banks?

If you transfer between bank accounts with this and dont change the program date it will post the exchange on a separate date to the actual difference

Thanks (0)
Adrian Pearson
By Adrian Pearson
20th Jul 2012 22:31

More information

I exported the full Nominal Daybook report from Sage to CSV and then totalled all of the journal lines by date, using a Pivot Table. See below (rows where Debits = Credits hidden):

As you can see, the grand totals for all journal debits and credit are the same. Which is as it should be.

What I cannot fathom is how separate lines of journal entries can have different dates!

My copy of Sage does not have foreign currency functionality. This is client data, so maybe their version does. I'll investigate further and report back on that.

In the meantime, any ideas?

Many thanks for all efforts so far by the way :)

Adrian

Thanks (0)
Adrian Pearson
By Adrian Pearson
20th Jul 2012 22:43

No foreign currency

I have checked all of the transactions and "Amount" is the same as "Foreign Amount" for every transaction.

Thanks (0)
avatar
By johndon68
20th Jul 2012 22:52

Journals

As per my previous post, if you look at one of journals that appears not to have a balancing entry in Financials, can you see the balancing entry either above or below it in the list - if not, it suggests, as I said, that Clear Audit Trail has been run which could give rise to what you are seeing...

John

Thanks (0)
Adrian Pearson
By Adrian Pearson
21st Jul 2012 08:56

Clear audit trail

@johndon68 thanks for that. How do I check if a Clear Audit Trail has been performed please? And, if transactions are cleared before a certain date, how can only part of a journal be cleared but not the other leg(s) which would, of course, also have the same date?

Thanks (0)
avatar
By johndon68
21st Jul 2012 09:28

Clear Audit Trail

To check if it has been run, go to Help -> About -> System Information -> Data Information and scroll down to the bottom.

As to how it can happen, earlier versions of Sage allowed for one side of an entry to be removed by the Clear Audit Trail process if it met the criteria so, take the following example.

Let's say a bank transfer (i.e. journals) was posted on 1st January 2011 and let's say that the debit was bank reconciled but the credit wasn't.  You then run Clear Audit Trail with a date of 31 December 2011 - as the debit has been bank reconciled, it can be removed from the Audit Trail and will be replaced with an opening balance dated 31 December 2011 so you end up with a credit dated January and a debit dated December so the overall report balances but one for January would not.

This was changed some time ago so that entries would only be removed if both sides met the removal criteria but, as the entry in question appears to be from 2009 your client may have been on a older version then...

Again, as I mentioned earlier, find the entry that still exists in the Financials window - if the opposite entry is not next to it in the list (or very close nearby depending on how many debits and credits were entered at the time) then it must have been cleared.

John 

Thanks (0)
Adrian Pearson
By Adrian Pearson
21st Jul 2012 10:18

Thanks John

I really appreciate your efforts here John.

Here is an example of a journal that seems to be split over several dates:

And here is the "About" info I just looked up:

Which suggests that a Clear Audit Trail has not been run?

Any further ideas?

Thanks again,

Adrian

Thanks (0)
Adrian Pearson
By Adrian Pearson
21st Jul 2012 10:25

For completeness ..

Just to confirm that it's not my Pivot Table that is wrong, here is how the journals highlighted above appear in the Sage UI - from Financials transaction list:

Thanks (0)
avatar
By johndon68
21st Jul 2012 10:44

Journals
OK, not Clear Audit Trail then...

Does the client use any 3rd party applications to post to Sage?

John

Thanks (0)
SiWoodhams
By Si_Woodhams
23rd Jul 2012 10:29

CSV Import

The only other way this could happen would be if the client did all their journals in excel then imported them as a CSV file, as it doesnt check the dates then

Thanks (0)
Replying to Accountant A:
avatar
By johndon68
23rd Jul 2012 10:33

CSV Import

Si_Woodhams wrote:

The only other way this could happen would be if the client did all their journals in excel then imported them as a CSV file, as it doesnt check the dates then

I thought that as well and actually tried it yesterday.  I haven't tried all versions but, certainly in my copy of v2011, I ran an import with journals with different dates and got errors reported that the journals for a given data did not balance so the import did not work...

This would not be the case with a 3rd party import program which, unless code was written to prevent it, can import a JD & JC with different dates.

John

Thanks (0)
Replying to qadri23:
SiWoodhams
By Si_Woodhams
23rd Jul 2012 10:57

3rd Party software

johndon68 wrote:

Si_Woodhams wrote:

The only other way this could happen would be if the client did all their journals in excel then imported them as a CSV file, as it doesnt check the dates then

I thought that as well and actually tried it yesterday.  I haven't tried all versions but, certainly in my copy of v2011, I ran an import with journals with different dates and got errors reported that the journals for a given data did not balance so the import did not work...

This would not be the case with a 3rd party import program which, unless code was written to prevent it, can import a JD & JC with different dates.

John

I agree with the 3rd Party software import as well (its an odd jounral to split into those dates as well - dont you think!)

I beleive on the older versions of sage sort of v12 backwards you could import journals with non-matching dates on a csv - havent tried it - but guy said it was an old version.

Also wonder if the client is using old version - and he is using 2012 whether something has gone wrong in the restore process?

Thanks (0)
avatar
By johndon68
23rd Jul 2012 11:07

Import Journals

Just tried in v12 and you can, indeed, import journals with different dates albeit, as you say, it is an odd one to split...

John

Thanks (0)
Adrian Pearson
By Adrian Pearson
23rd Jul 2012 11:18

Thanks guys

I think I'll have to leave this now - it's turned into a bit of a saga.

I really appreciate the help offered here and have to conclude that the client has most likely imported journals with different dates using an old version of Sage.

I just tried to import into v2012 and it does validate the dates but obviously it didn't in older versions.

Thanks again all.

Adrian

Thanks (0)
By brucedenney
23rd Jul 2012 12:07

I don't know the answer but... this is what I would do...

I can not reproduce what you are seeing.

Until you know for sure what it is, you really have no idea why or what it is that is causing this and It should be of great concern.

What I would do is to narrow down the search.

Run the report for the first half of the month and see if the error is in the first part of the month, if it isn't then it must be in the second.

Then halve that to the first week or second week (of the first or second half of the month) then halve that and so on, until it is down to a single day.

At this point there would not be a huge amount of data to go through, so go through all the journals in the audit trail and see if you can find one that is an opening balance, or one where half the journal has one date and the other half another, you might even find half a journal with no other half (I have seen this in data that sage have fixed after a corruption) whatever is the cause, you will at least at this point know what caused it and be able to sleep easy with the certain knowledge of what caused it.

 

 

Thanks (0)
Replying to Ruddles:
avatar
By johndon68
23rd Jul 2012 12:18

Journals

brucedenney wrote:
I can not reproduce what you are seeing.

Whilst we don't know which version of Sage was being used when these journals were posted, it is certainly recreatable in v12 - the import routine is quite happy to allow you to import a debit and credit that have different dates and it reproduces the original circumstances exactly.

This does, of course, assume that the original journals were imported...

John

Thanks (0)
avatar
By Healthpay
02nd Aug 2012 11:15

Beggars belief!

I hit such a problem with Sage many, many years ago, and support were as much use as a holed bucket in a deluge.

That is why I stopped using Sage and have never gone back. The only consequence has been an enormous benefit to my expenses!

Thanks (0)
avatar
By JWB
02nd Aug 2012 11:16

Not balanced

Looking at your report above -

Are the totals £3,012,630.91 and ££3,458,444.92  just a subtotal for VAT code T9 ?  

What about the T1, T2, T3 codes etc ??  

Add them in and maybe it will balance ... 

 

Thanks (0)
Adrian Pearson
By Adrian Pearson
02nd Aug 2012 11:36

Not sub-totals

@JWB the totals are overall report totals. Not a sub-total, not filtered in any way. It seems that @johndon68 has already provided the answer, by re-creating the problem using an old version of Sage.

Thanks (0)
avatar
By tburns
24th Jul 2014 15:19

journals

Hi can someone please help.  I need to post an Invoice that relates to quarter and recognize revenue for each month in the quarter, not all in one bulk. How can I do this using journals? When the invoice get paid, for do I match again the entries

Thanks (0)