Check multiple records belonging to one client

[paulcook]

I have been asked (does your boss ask?) to create a report that checks 3 conditions for each client. The first two conditions were easy because they just checked conditions in one record, the third condition and the difficult part (for me anyway) is that each client can have multiple client goals and therefore multiple records showing and I need to check all of the records for a client.

The report looks something like this.......

clnt.clnt_name goal.primary_goal = "1" wia_goal.result_cd
----------------------------------------------------
clnt.clnt_name goal.primary_goal = "1" wia_goal.result_cd
----------------------------------------------------
clnt.clnt_name goal.primary_goal = "1" wia_goal.result_cd
----------------------------------------------------

Each client must have a primary goal (goal.primary_goal = "1") open at all times. I need to check to see if the client has a primary goal that has been attained and a new primary goal has not been setup. The clients can have multiple goals inlcuding both primary and secondary goals.

When a client is found to meet this last condition I have to show some type of alert on the report, i.e. colored highlight, different text style.

Any and all help is greatly appreciated.

Thank,

Paul

 

[lbass]

Paul,

I'm familiar with WIA, but not with how your fields work...

If a client has a primary goal not yet attained, can we assume that {wia_goal.result_cd} is null? Is the {wia_goal.result_cd} used for both primary and secondary goals? If both of these are true, then I think you could group on {clnt.clnt_name}, and then create a formula {@opengoal}:

if isnull({wia_goal.result_cd}) and
{goal.primary_goal} = 1 then //if this is what specifies
//a goal as primary
1 else 0

Then create a second formula:

if sum({@opengoal},{clnt.clnt_name}) = 0 then "Alert--No Current Goal Set" else ""

Place this in the Client group header. Or, alternatively, to highlight names with no open goals, go to the section expert (format->section)->Group Header (Client)->color tab->background->x+2 and enter:

if sum({@opengoal},{clnt.clnt_name}) = 0 then cryellow else crnocolor

-LB