Comparison, Fomula to use for

[Jonathan212]

Requesting help with a formula. I am using CR10.

We would like to create a report that shows if a surgeon does not have any cases “booked” into their allocated block date two weeks in advance of their allocated block date.

For example, if Dr. Doe was allocated block time on 11/1/07 and has no cases booked into that date slot by 10/18/07 (two weeks before), we would like to know that.

Here are some fields that I think might have to be used in the formula:

Field names used to allocate block time:
Blockownername – this is the name of the surgeon that was allocated block time (e.g., Dr. Doe)
Resourcename – this is the name of the operating room (e.g., OR1)
Blockstartdate – this is the date of the allocated block time

Field names used when a case is scheduled:
Surgeonsname – this is the name of the surgeon scheduled to do the case
ORname – this is the name of the operating room scheduled for the case (e.g., OR1)
Surgdate – this is the scheduled surgical date for a case

Resourcename and ORname may be unnecessary, as it is not critical that they match. I just need to know whether a surgeon has at least one case (or no cases) scheduled on their allocated block date.

We are trying to be more proactive in identifying open spots on the schedule that we can offer to other surgeons several weeks in advance of surgery date.

 

[synapsevampire]

Please give some indication as to what the output should be...

-k

 

[Jonathan212]

Example of desired output: for report run on 10/18/2007 with a future surgery date of 11/1/2007:

Surgeon Block Allocated Date Cases Scheduled
Doe 11/1/2007 No
Jonathan11/1/2007 No
Synapse 11/1/2007 Yes
LBass 11/1/2007 Yes

 

[Madawc]

Depends a bit on your data structure. Suppose one table has surgeon, another 'block of time' and a third 'cases'. Link time to surgeons and LEFT OUTER to cases. Then group by surgeons and do a summary count of the number of cases, or else check for a field on case being null. You can then suppress detail lines and set a formula field in the group footer to show Yes or No depending on cases.

Note that LEFT OUTER to cases will only work if there are no selection criteria for cases. And that tests including a null must test for a null first, otherwise the formula will stop without output.

You might also find it useful to use Hide (drill-down OK) instead of suppress for details.

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

 

[Jonathan212]

Thanks very much for the suggestions Madawc. Much appreciated. I will give them a try.