Crystal reports formula get value from another column based on value from a third column

[gh_cr05]

Hi The following is my sql query output.:

I want to return (var Name parTxtID) A24/0012 from the first row second column (text_id) if the parent_aliquot in the second row second column (var parSampNum) 74465 for (var SampNum ) 74466 is equal to the value in first row first column - 74465.

Can anyone help me with the crystal reports formula for this?

Regards

[gh_cr05 · Accepted solution]

That code did not work. However i got the solution from elsewhere. I had to create an Alias table on the Sample table and link them in the Database link on the correct field to get this to work. 

Thanks for the effort put. 

Regards

[JWiseman]

Hi gr_cr05, create a new formula using the code below. A set of arrays is used to keep track of the sample_number and text_id values and each current parent_aliquot is matched up with the sample_number array. This of course assumes that the data is coming in a specific order like in your sample data.

whileprintingrecords;

//////////////////
// set these 3 vars to your fields
/////////////////

numbervar thisId := {Command.sample_number};
stringvar thisValue := {Command.text_id};
numbervar thisParent := {Command.parent_aliquot};


//////////////////
// the code below does not need to be modified
/////////////////

// vars for worker arrays
stringvar output;
numbervar i;
numbervar array allIds;
stringvar array allValues;

// vars for finding member position in array
numbervar counter:= 1;
numbervar thisIdPosition:= 0;

// add current row values to arrays
i:= i + 1;
redim preserve allIds[i];
allIds[i] := thisId;
redim preserve allValues[i];
allValues[i] := thisValue;

// check for match between parent and Id
while counter < ubound(allIds) do (
    if thisParent = allIds[counter] 
    then (
        thisIdPosition := counter; 
        exit while
    );
    counter := counter + 1
);

// assign output
if thisIdPosition > 0
then output := allValues[counter]
else output := "";

output;