Formula to extract 1 record 1

[vb6rookie]

I wnat to create a formula that extracts 1 record for a group of data in a drill down so I can create a percent
In the Sample Data below I know if the "code" field data is 789 then that is "Net Sales" and I need value from the "Amount" field:
Example:
Sue 1/1/2003
void code 123 $40.00
cancel code 456 $15.00
net code 789 $300.00
Fred 1/1/2003
void code 123 $80.00
cancel code 456 $35.00
net code 789 $400.00
--------------------------------
Desired Result
Sue 1/1/2003
void code 123 $40.00 13.33%
cancel code 456 $15.00 5.00%
net code 789 $300.00 100.00%
fred 1/1/2003
void code 123 $80.00 20.00%
cancel code 456 $35.00 8.75%
net code 789 $400.00 100.00%

Thanks in advance

 

[chelseatech]

My first thought was to use variables, but that won't work because you want a total for each person.

Use and If- then else to add up the 789 records and then use the subtotal of that in your formula.

if {table.code}="789" then {table.amount} else 0

Subtotal that by person, and then your formula for the percent is
if Sum ({@789total},{table.person})=0 then 0 else
Sum({table.amount},{table.person}) %
Sum ({@789total},{table.person})

The if is to handle any potential divide by zero errors.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[vb6rookie]

Thanks for your help. I was partially successful
I made a formula @CashNet:
whilereadingrecords
if {tsCashier_Deductions.code} = "2013" or {tsCashier_Deductions.code} = "19" then
formula = {tsCashier_Deductions.AMOUNT}
end if
Then I inserted a subtotal and added @Cash to that group
The next fromula:
if {@CashNet} = 0 then
0
else
sum({@CashNet},{tsCashier_Deductions.CSHR_NAME}) %
sum({tsCashier_Deductions.AMOUNT},{tsCashier_Deductions.CSHR_NAME})

was made and the results add all the records of the cashier and divides by the @CashNet formula and displays data for the 1st record only in the group. I tried to add the tsCashier_Deductions.code line, but an error pops up " summary / running total field cannot be created"

if {@CashNet} = 0 then
0
else
sum({@CashNet},{tsCashier_Deductions.CSHR_NAME},{tsCashier_Deductions.code}) %
sum({tsCashier_Deductions.AMOUNT},{tsCashier_Deductions.CSHR_NAME},{tsCashier_Deductions.code})

How can I evaluate each record of the cashier?

 

[Naith]

Take the WhileReadingRecords out of @CashNet. You probably don't need it, and it's that that's making you run into your summary/running total error.

Naith