Can S/O check this formula, linking & logic?

[lareya]

I have a validation report that checks for different Staff people in Surgery. The Report only looks at one Nursing Record at a time. They are linked by Case_No.
I have 4 formulas that all follow the same pattern. I have created 4 alias to the same table - Post_Resource.
The Post_Resource Table has the following fields Role_Code & Resource_Type that I need to get info from. Role_Code are numbers (1,2,3,…)
Resource_Type are (Staff, Equipment, Room)
They are all linked to the main Post_Case by Case_No LEFT OUTER JOINED.
I needed to get the the Resource_Type.Staff and check for the different Role_Codes

In each table I EQUAL linked from the original Post_Resource table to all 4 alias’s Post_Resouce Table(1 -4) From Role_Code to Role Code Resource_Type to Resource_Type

In the Select Expert, I selected Resource_Type = Staff for each of the alias tables, and for the Role_Code I selected the numbers that represent the different providers. Like 1 is the Surgeon and 9,10,72 are the scrub.
My formulas is as follows:

WhilePrintingRecords;
Local StringVar strValidate2;

(

IF ISNULL(DISTINCTCOUNT({POST_RESOURCE_1.ROLE_CODE}))
    then
if strValidate2 = "" Then
        strValidate2 := "Please Add a Surgeon"
   else         strValidate2 :=  strValidate2 & chr(13)& chr(13)  & "Please Add a Surgeon" ;

IF ISNULL(DISTINCTCOUNT({POST_RESOURCE_2.ROLE_CODE}))
    then
if strValidate2 = "" Then
        strValidate2 := "Please Add a Anesthesiologist"
   else         strValidate2 :=  strValidate2 & chr(13)& chr(13)  & "Please Add a Anesthesiologist" ;

IF ISNULL(DISTINCTCOUNT({POST_RESOURCE_3.ROLE_CODE}))
    then
if strValidate2 = "" Then
        strValidate2 := "Please Add a Scrub"
   else         strValidate2 :=  strValidate2 & chr(13)& chr(13)  & "Please Add a Scrub" ;

IF ISNULL(DISTINCTCOUNT({POST_RESOURCE_4.ROLE_CODE}))
    then
if strValidate2 = "" Then
        strValidate2 := "Please Add a Circulator"
   else         strValidate2 :=  strValidate2 & chr(13)& chr(13)  & "Please Add a Circulator" ;

);
strValidate2;

The results of this show this:
Please Add a Surgeon
Please Add an Anesthesiologist
Please Add a Scrub
Please Add a Circulator

Now, Is there a better way to do this? I ask that because I think I have too many alias, and I end up not being able to reference the case record. I get a empty case record. However, when I only have two alias’s it seems to work fine.

Am I missing something? If there is a better way I am interested in learning it also.

This is what I would like to do:

if role.code is NOT 1 then "Need Surgeon" else if role.code <> (9,10,72)then "Need Scrub"

but I can't get this to work.

Thanks a lot!

lareya


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Crystal 8.5; SQL database; MS Windows 2K; ORSOS/One Call Hospital Scheduling System v10.1.2; Huge Newbie to Crystal! Operating Room RN Analyst

 

[dgillz]

Using crystal syntax, try this:

if Not({role.code})= 1 then "Need Surgeon" else if Not({role.code}) in [9,10,72]then "Need Scrub"

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Madawc]

You'd be much better using formula fields. Have several and combine them. The first could be @IfSurgeon

IF ISNULL(DISTINCTCOUNT({POST_RESOURCE_1.ROLE_CODE}))
    then "Please Add a Surgeon"

And so on. Test and add line-skips in another formula field.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10