Previous field on a formula field 1

[lkh5650]

Hi I am trying to use previous field on a formula field and it wouldn't work.
for a formula called tracking
if next({CASE_ID}) = {CASE_ID} and not isnull({TRACKING_NUMBER}) and next({TRACKING_NUMBER}) <>{TRACKING_NUMBER} then
{TRACKING_NUMBER} +","+ previous({@tracking})

then it would give me an error saying A formula cannot refer to itself, either directly or indirectly

I just wanna do this
Case ID tracking number @tracking
1234 1
1234 4 4,1
1234 7 7,4,1


How can I do this ?

Please help

Thank you

 

[lbass]

You can do this with a formula like this:

whileprintingrecords;
stringvar x := totext({table.trackingnumber},0,"") + "," + x;
if len(x) > 1 then
left(x,len(x)-1)

-LB

 

[lkh5650]

Thanks it worked!

 

[lkh5650]

there is a problem..
so using ur advice, i wrote a code like this

whileprintingrecords;
stringvar z;
if ((previous({AE_CASES.CASE_ID}) <> {AE_CASES.CASE_ID}) or previous({AE_EVENTS.DISPLAY_NBR}) <> {AE_EVENTS.DISPLAY_NBR} or previous({AE_SUSPECT_DRUGS.SUSP_DRG_SEQ_NBR}) <> {AE_SUSPECT_DRUGS.SUSP_DRG_SEQ_NBR}) and not isnull({AE_DOSE_LOTS.EXPIRATION_DT}) then
z:= {AE_DOSE_LOTS.EXPIRATION_DT}
else if (previous({AE_CASES.CASE_ID}) = {AE_CASES.CASE_ID} and not isnull({AE_DOSE_LOTS.EXPIRATION_DT}) and previous({AE_DOSE_LOTS.EXPIRATION_DT}) <>{AE_DOSE_LOTS.EXPIRATION_DT}) then
z:= {AE_DOSE_LOTS.EXPIRATION_DT} +","+ z

else if (previous({AE_CASES.CASE_ID}) = {AE_CASES.CASE_ID} and not isnull({PC_INVESTIGATIONS.TRACKING_NUMBER}) and previous({PC_INVESTIGATIONS.TRACKING_NUMBER}) <>{PC_INVESTIGATIONS.TRACKING_NUMBER}) then
z:= z

else if (previous({AE_CASES.CASE_ID}) = {AE_CASES.CASE_ID} and not isnull({AE_DOSE_LOTS.EXPIRATION_DT})) then
z:=z
else if (previous({AE_CASES.CASE_ID}) = {AE_CASES.CASE_ID} and not isnull({AE_DOSE_LOTS.LABEL_LOT_NUMBER})) then
z:=z ;



So when it is on the first record of group (new case ID)
then z should reset and just display the field of that row
when it goes to next row, and if it qualifies for 2nd if statement, z should display the previous one and the current one

3rd row of data, if it qualifies for 2nd if, then z should display the previous 2 field plus the current field..

this part seems to be working

under certain circumstances, for instance the current field is null, or empty
then i don't want to add ", ", but i wanna just display the previous field values, so I do z := z,


However, the problem happens here

when it jumps to newer drug
previous({AE_SUSPECT_DRUGS.SUSP_DRG_SEQ_NBR}) <> {AE_SUSPECT_DRUGS.SUSP_DRG_SEQ_NBR}
or newer event
previous({AE_EVENTS.DISPLAY_NBR}) <> {AE_EVENTS.DISPLAY_NBR}
it does what its supposed to do

z:= {AE_DOSE_LOTS.EXPIRATION_DT}, thus i use this hoping that w.e previous fields were (let's call it z1), since it was for different event or drug, it should be erased.

however, the next row of data for those that doesnt qualify for 1st if statement, lets say it went to the 2nd if statement and it qualifies for it

then it should display the previous field (z) + current field

but it would display the z1 + current field....

it happens for 3rd 4th and 5th if statement too
even though 1st if statement happened and z should be replaced with new field
it will display
z1 as z..

Please help me
i hope this makes sense
if it doesn't please just ask me for clarification
thanks!!

 

[lbass]

I didn't read all this, but you should be using a reset formula in your group header, if you want results within a group.

whileprintingrecords;
stringvar z;
if not inrepeatedgroupheader then
z := "";

Regarding nulls, you should use a clause like:

if isnull({table.field}) then
z := z else
z := {table.field}+"," + z;

-LB