"Scanning" Function

[MikeCopeland]

Using CRXI...
Nothing I'e yet tried has solved this problem, so I'm trying a new function. The problem is that I need to scan sets of rows to see if a certain value exists in one of them. If so, I want the return a value from that row; if none of the rows has the value, I want to return a default (string) value - always to a Group line.
Here is some data that illustrates the problem:
ID CKey PK Date
809549 213298 44 8/15/2007
813644 213298 47 9/9/2007
813825 213298 40 9/9/2007
814610 213298 30 8/15/2007
816644 213298 30 9/20/2007
817681 213298 40 10/7/2007
822866 213298 40 11/9/2007
828117 213298 47 12/9/2007
829863 213298 42 12/26/2007

828859 213502 43 12/20/2007
830785 213502 40 1/7/2008
832998 213502 48 1/18/2008
833000 213502 47 1/18/2008
In the first dataset, there is a row with the value of 42 in the "PK" column - I want to detect that and return the "Date" value. In the 2nd dataset, there is no row having 42, and I want to return "Open". Here is the formula I'm trying (which fails, of course):
// disDate - returns the Client's discharge date or "Open"
global NumberVar clientKey = 0;
global NumberVar bFound42 = 0;

if({ClientPacket.CKey} <> clientKey) then
( // initialize variable for new data set
clientKey = {ClientPacket.PK};
bFound42 = 0;
"Open";
)
else
(
if({ClientPacket.PK} = 42) then
(
bFound42 = 1;
toText({ClientPacket.Date}, "mm/dd/yyyy");
)
)
I suspect there are fundamental issues here, but try as I might, I'm unable to adjust this to achieve my goal. Please advise. TIA

 

[PatriciaObreja]

When assigning values to variables we should use ":=".

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[PatriciaObreja]

If records are grouped by PK in group header we must have a reset formula:
//@reset
stringVar text := "Open";

In Details section an evaluation formula:
//@found
stringVar text;
if {ClientPacket.PK} = 42 and text = "Open" then
text := toText({ClientPacket.Date}, "mm/dd/yyyy")
else
text := "Open";

And then in Group Footer display the result.

Display information in Group Footer and suppress details and Group Header.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[PatriciaObreja]

One change in @found:
//@found
stringVar text;
if {ClientPacket.PK} = 42 and text = "Open" then
text := toText({ClientPacket.Date}, "mm/dd/yyyy")
else
text := text;

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[smcnulty2000]

This might be a slightly easier route.

detail level formula

if {ClientPacket.PK} = 42  then
      ToText({ClientPacket.Date}, "mm/dd/yyyy")
else
      "Open";

then just insert summary object in your group footer based on this. Set for 'Minimum'. That's your Sigma button if you aren't familiar with using them. Or the insert menu, summary, if you want to go that route.

If there is no date, then the minimum would be the word 'Open'. If there is a date present, then it would be the minimum.

That way you can avoid using any variables, any resetting of global variables, etc.

 

[MikeCopeland]

Patricia, thanks for your help so far (it's not working yet). Question: you mention needing a "reset formula"; I don't know how to use such a function - how to activate it, from where to call it, etc. I can see its necessity, but I don't know how to use it. TIA

 

[lbass]

Mike, SMcNulty's solution is simpler and should work as presented without the need for variables.

-LB

 

[PatriciaObreja]

Concerning the reset formula: create a formula that will be placed in the group header and does only one thing: resets the value to "Open". That's why we say that is a reset formula.

//@reset
stringVar text := "Open";

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium