re-create FRx data tables

[jymm]

I have no idae how, but it seems that FRx is not in sync with the trial balance for the same accounts.

I am running gp8 &frx6.7 --- anyone tried the reconcile (after clearing the gl10110) to get things to sync up? near as we can tell the info in the GL is right (so the trial balance is correct) but FRx's info is the one that is somehow off.

anyone else tried this (deleting GL10110 and then reconciling each year) before I take this huge leap? or is there a better way?

 

[stef315]

Is your FRx report off by specific accounts or in total? If in total, make sure all accounts are including. The exception report works well for this (Report Options-Advanced tab). If specific accounts make sure you are looking at the same period.

If it's a YTD column FRx may be pulling from the summary table. Does your summary trial balance match your detailed trial balance? If not, then you will need to reconcile your years.

Most important thing is to make sure you are maintaining history. Besides that, I've done the reconciling history many times. You start with oldest year and go forward. I've never had any problems.

 

[jymm]

specific accounts --- usedd FRx for YEARS and never would have believed it --- I ended up doing a sum on the sql tables to proove it ---pretty weird

 

[stef315]

Okay, in Great Plains does the Summary Trial Balance match the Detailed Trial Balance?

 

[jymm]

actuallly Stef - does not matter --- the sql sum of the records in the table does not match. Isolated it to March (why they are just catching this in June we will not know, but...) and to just selected accounts, so it is not as wide spread as I originally though --- I might play around with the data and fix a few records.

 

[jymm]

First I checked to see if something was weird in the FRx records

select actindx, year1, periodid, perdblnc, debitamt-crdtamnt as summary
from gl10110
where debitamt-crdtamnt <> perdblnc

fortunatly that was all ok (so FRX is in balance with itself)

Second I idetified the record in the GL that did not match the FRx records (this is where the problem was).

CREATE TABLE #Temp_Sums ( 
	[AccountIndex] Int ,
	[OpenYear] SmallInt ,
	[MonthNum] Int,
	[GLAmount] Decimal(11,2)
)

Insert Into #Temp_Sums
select actindx, openyear, Month(trxdate), Sum(debitamt)-Sum(crdtamnt)
from gl20000 
group by actindx, openyear, Month(trxdate)

select *
from #Temp_Sums inner join gl10110 
on AccountIndex = gl10110.actindx and Year1 = OpenYear and GL10110.periodid = MonthNum
where Perdblnc <> GlAmount

Drop table #Temp_Sums

there are only a few accounts, but those are off the amount that originally we saw... good grief charlie brown

 

[stef315]

The SQL sum does not match FRx? That's why I'm asking if the summary GL matches detailed. Maybe you could check that in GP for those accounts you found were off?