Isnull formula perhaps, but not sure

[Jonathan212]

For the sake of this example I have five operating rooms. These rooms are called:
OR1
OR2
OR3
OR4
OR5

My output for a given day is grouped by operating room name and is:

OR1
Case number 12333
Case number 45666
Case number 78999

OR3
Case number 44444
Case number 55555

OR4
Case number 76767

OR5
Case number 88776
Case number 93244
Case number 68688

Please note, in the above example, that there were no cases in OR2.

Am I able to do a formula that loads in the names of all the operating rooms (note in real life I have 30 operating rooms) and then search for existence of at least one case number in an OR name.

For the above output, I would like to insert a comment in some section that reads "There were no cases done in OR2”.

I have CR10. Thanks in advance. Jonathan

 

[CoSpringsGuy]

do you have a table in your database that has Operating Room information? If you do you can use a left outer join

For example ....
I had a report that I needed to list all users and the calls made by each whether they made any or not.

I used 2 tables ... User Master which has all of the User employment info and Call statistics.

A left outer join from User Master to Call Statistics did what I needed.

 

[Jonathan212]

Thank you CoSpringsGuy, I will look into that suggestion.

Do you think that will permit me to report on a room that was NOT used at all and to display that information on the report.

 

[CoSpringsGuy]

sorry i was late on my response I am out of town... yes if you do a left outer join it will create a record for every entry within your parameters for the left table and only append fields from the right table if the linked fileds match. If they dont match, then the requested fields from the right table will be a null value....

 

[CR85user]

As above suggestions - Within your report then you can group on operating room name, the header could then contain the name of each OR and the details section could contain the formula to either show case numbers or if null then a string to show 'There were no cases...' etc. alternatively you could group also by case number to give more flexibility if required.

//@Case number
if isnull({cases.caseno}) then 'There were no cases recorded within {oproom.name}' else
'Case Number:' + ' ' + totext({cases.caseno})


Good luck

'J

 

[Jonathan212]

CoSpringsGuy and CR85User many thanks for helping me arrive at a solution. I appreciate your input and help on this. Jonathan