reading and evaluating one-to-many records

[crazycoot]

Using Crystal 10 on SQL Server.

Using two tables, work_orders, and work_order_details, linked by a common work order number with an inner join. There can be many records in the details table to each work order in the main table.

In the details table is a field called actionID. I need to check all the action ID's for each work order, looking for a specific action ID (2custinit is the name of it). When I do a simple if-then like this:

if {work_order_details.action id} = "2custinit" then
custinit_flag = "Yes"
else
custinit_flag = "No";

it always evaluates to false, even when that action id is present. It looks like it's only evaluating the most recent detail record instead of checking all of them. How do I step through all the detail records checking for this specific string?

I've tried grouping on the action id field with the if-then in the details section or the group footer, but it still always returns false.

The action ID I'm looking for might or might not be there, so I have to check for it and set a flag of some sort. If it is present, then I have to do some date/time calculations. I can manage those, but I can't figure out why I can't determine if the action id is present.

I've got a feeling that there's a simple solution to this and I'm going to feel foolish when someone points it out, but I'm at wits end with this problem.

Thanks for any help offered.
Kevin

 

[GMcNamara]

Try:

if {work_order_details.action id} = "2custinit" then
custinit_flag := "Yes"
else
custinit_flag := "No";

 

[synapsevampire]

Where are you placing the formula?

Rather than stating how you're trying to accomplish a nebulous requirement, why not post technical information and requirements?

Crystal version
Database/connectivity
Example data
Expected output

Do you only want rows with that ID, or are you taking special action on only those rows?

I didn't see the difference between your formula and GM's...

This formula should be in the details, and used to identify a condition.

I would code it as:

whileprintingrecords;
boolean custinit_flag;
if {work_order_details.action id} = "2custinit" then
custinit_flag = true

Don't use an else.

Now in the group header use:
whileprintingrecords;
boolean custinit_flag := false;

Now if it finds that row in the details, you can take some action in the group footer referencing the variable.

-k

 

[lbass]

Or, place the following formula {@present} in the detail section:

if {work_order_details.action id} = "2custinit" then
1 else 0

Then if you need to do group level calculations based on whether a group contains this field, you can use a formula like the following:

if sum({@present},{table.group}) > 0 then //whatever

-LB

 

[GMcNamara]

My formula is setting a value (note the ':=') where his formula is performing a boolean comparison ('='). This is why the formula always returns false, because custinit_flag never equals "Yes".

 

[crazycoot]

I was right about that simple solution making me feel foolish. The difference in my code that of GW is I was using "=" rather than ":=". The first is for testing equality, the second, correct way, sets the value of the variable. I'm sure you know that, but I missed it due to my inexperience.

My if-then is now evaluating correctly. Thanks for pointing that out to me.

Sorry, if I didn't provide enough information in my initial post. Sometimes, too much information just clouds the issue, so I tried to keep it simple.

Thanks to everyone who replied!