Formula to sum transactions

[CARLEENHOP]

Hi, I am using crystal reports 11.5. I have two tables which store similar data, however neither of them store it in a way I can extract what I need.

E.g. a stock adjustment is posted, and it posts a variance, if > 1 product on stock adjustment it posts a variance to > 1 account. So if I just grab the stock adjustment variance it duplicates the value when vieiwing both accounts.

If I view the accounts it has posted to, and the amount it has posted, I get the individual amount (which is better), but when > 1 amount has been posted to that account on the same day I can only see the total, so it then overstates this value in that scenario.

I essentially need to be able to say in a formula (but not sure how), if there is > 1 transaction against the same GL CODE on the same GL Date then print the stockadj.value else print the GLacc.value.

FYI - I am sorting by GL Acc.

Appreciate any help.
Thanks

 

[Madawc]

You need to write a formula field that checks at detail-line record and produces the value you want for that particular line. Start by writing and displaying this value, whether or not you want it on the final report.

Once you have the total at detail-line level then you can sum it.

I assume you are doing a left-outer link from the main adjustment to the stock variance. If a record might not be there, check for IsNull before doing anything else, because otherwise nulls will stop the formula.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[IanWaterman]

Formula you need will be something like

If isnull(>1Transaction) then stock adjustment
else if GLCodeStock = GLCodeTransaction and date(GLdateStock) = date(GLDate)Transaction then >1 TransactionAmt else
stock adjustment

Ian