Counting a Conditional formula Text Message 1

[amoroney]

I'm new at this Crystal stuff so bear with me.
I am using Version 8.5.

I am building a report where I include a number of notification messages by using formulas such as:

IF isnull({episode_history.date_of_discharge}) AND {staff_tx_history.SERVICE_CODE}="3010" and {?Run date} >= ({staff_tx_history.date_of_service}+90) then "A Face to Face is OVERDUE!"

I have grouped as follows: Group 1-Staff Name, Group 2- Patient Name, and Group3-Service Code. My formula must be placed in the 3rd group footer-Service code section because I only want to view the most current information.

I would like to count the number of time the "A Face to Face is OVERDUE!" message appears but have not had any luck. I would like the count to appear in the Group 1 footer and also have a grand total at the end of the report.

A distinct count always returns a value of 2, of course, counting 1 for when my text appears and another for when it does not. A count inflates the number because it is counting how many times it is possible for my formula to show and not taking into account the instances when I have "suppressed" it, so to speak.

I am trying to avoid adding another group because there are about 4 different formula notification messages I would like to do counts on. I"m thinking there must be a much better way to do this! Help!

 

[lbass]

What does this refer to?

and not taking into account the instances when I have "suppressed" it, so to speak.

I would place the formula in the detail section (so you are not dependent on the sort order of the records to get the correct value) and then insert a maximum on the formula to get the display at the Group footer #3 level. To summarize it at the Group #1 footer level and grand total levels, create these formulas:

//{@reset} to be placed in the Group #1 header and suppressed:
whileprintingrecords;
numbervar grpsum;

if not inrepeatedgroupheader then
grpsum := 0;

//{@accum} to be placed in the group #3 footer:
whileprintingrecords;
numbervar grpsum;
numbervar grtotal;

if maximum({@yourformula}) = "A face to face is OVERDUE" then
(grpsum := grpsum + 1;
grtotal := grtotal + 1);

//{@displgrpsum} to be placed in the group #1 footer:
whileprintingrecords;
numbervar grpsum;

//{@displgrtot} to be placed in the report footer:
whileprintingrecords;
numbervar grtotal;

-LB

 

[Madawc]

An alternative is to move the test to a formula field - make it a 'boolian', with no "if ... then ...", just something called @MakeTest containing isnull({episode_history.date_of_discharge}) AND {staff_tx_history.SERVICE_CODE}="3010" and {?Run date} >= ({staff_tx_history.date_of_service}+90). This will return 'true' or 'false' but you can test for it just by mentioning the name: e.g. If @MakeTest then "A Face to Face is OVERDUE!" .

You then create a running total. In [Evaluate], use a formula, which can be just @MakeTest.

Madawc Williams (East Anglia, UK)

 

[amoroney]

Thanks for the responses, but I think I didn't explain my problem very well. I would like to count the number of times that the "Face to Face" message appears only when it is the last record, hence my placing my display info for the 3rd group in the footer. I am including a date_of_service field and am ultimately want the message to be counted if looking for the most current "3010" service for each Patient (Group 2), i.e. there should be no more than one per patient. The problem I'm having is that my selection formula is returning to me all the services for the last 180 days, which I need to display for other aspects of my report (so changing my selection formula is not an option). So my count is off because virtually every patient will have some instance in the past 180 days where the formula noted above will be true, and counts and running totals are counting all of the records, not just the one I am showing in the footer. I am guessing there is something I can add to this formula to make it only print a "Face to Face is due" when

isnull({episode_history.date_of_discharge}) AND {staff_tx_history.SERVICE_CODE}="3010" and {?Run date} >= ({staff_tx_history.date_of_service}+90)

is true for the most recent "3010" service. I am new to the computer world as of 6 months ago so it may be some simple thing I could add to my formula (I am just learning to write formulas) that I am missing dt ignorance.
Help!

In case it helps, my selection formula is as follows:
not ({staff_tx_history.program_code}in "721", "904", "910"]) and
{staff_tx_history.SERVICE_CODE} in ["3010", "3013", "8002"] and
{staff_tx_history.date_of_service} >= ({?Run date}-180)

 

[lbass]

My solution should work.

-LB

 

[amoroney]

Tried it, but it didn't work. I've doubled checked to make sure I followed your suggestions to the tee.

I am guessing that in the {@accum} formula the "maximum({yourformula})" meant for me to insert the maximum I created for Group #3 "Maximum ({@FtF Overdue}, {staff_tx_history.SERVICE_CODE})" and not to type exactly as you had it (I am just learning please be patient), because then it simply counted 1 for each service code grouping under each patient for a maximum of three per patient, since I had a total service codes in my selection formula.

However, even when I insert the Group #3 Maximum that you had me create, I am still getting an inflated count. Investigation shows that it is counting 1 for every patient that has had a 3010 service code completed in the last 180 days (my selection formula) where the following is true:
isnull({episode_history.date_of_discharge}) AND {staff_tx_history.SERVICE_CODE}="3010" and {?Run date} >= ({staff_tx_history.date_of_service}+90)

It appears to be counting all the rows where the date_of_service is over 90 days. This would work great if patients could only have one 3010 service every 180 days but they actually will almost always have more than that. Multiple "3010" rows appear in the details for each patient, but I only want the patient to be counted if the "3010" row with the most recent date has a date_of service that is 90 days previous to the run date of the report.

Please let me know if you know of any other options that may work. I very grateful for any assistance.

 

[lbass]

Actually, {@yourformula} should be:

if isnull({episode_history.date_of_discharge}) and {staff_tx_history.SERVICE_CODE}="3010" and {?Run date} >= ({staff_tx_history.date_of_service}+90) then "A face to face is OVERDUE"

And yes, it should have been:

whileprintingrecords;
numbervar grpsum;
numbervar grtotal;

if maximum({@yourformula},{staff_tx_history.SERVICE_CODE}) = "A face to face is OVERDUE" then
(grpsum := grpsum + 1;
grtotal := grtotal + 1);

The @accum} formula should be in the group #3 footer. This should not result in inflation, since it is in a group section. Are you sure you have it placed in the correct section?

-LB

 

[amoroney]

yes, this is exactly what I had. I've triple checked the formulas and the placement, but it is still counting 1 per client if they have ever had a row of data where the date_of_service for "3010" is 90 days past the current date. I'm not familiar with some of the formulas you used-so I could be way off on this- but I'm wondering how the maximum of my formula which has two possible values: "A Face to Face is OVERDUE!" or null can communicate that I want the most current date of service.

 

[lbass]

Sorry, I see your point. I think {@yourformula} should be:

if isnull({episode_history.date_of_discharge}) and {staff_tx_history.SERVICE_CODE}="3010" then {staff_tx_history.date_of_service}

Then {@accum} should be:

whileprintingrecords;
numbervar grpsum;
numbervar grtotal;

if maximum({@yourformula},{staff_tx_history.SERVICE_CODE}) <= {?RunDate}-90 then
(grpsum := grpsum + 1;
grtotal := grtotal + 1);

-LB

 

[amoroney]

This didn't work either, as making this change causes it to count 1 per service code (not necessarily the desired "3010") that has a date_of_service <= {?RunDate}-90
causing two problems, 1) I'm counting for service codes other than "3010" and 2) it is counting more that one per patient.

However, I tweaked it a bit and was able to get the following to work perfectly (Yahoo!):

1) I inserted a maximum on date_of_service and displayed it on the Group#3 level.
2) I kept the {@reset} formula the same.
3) I changed my formula to:

IF isnull({episode_history.date_of_discharge}) AND {staff_tx_history.SERVICE_CODE}="3010" and
{?Run date} >= (Maximum ({staff_tx_history.date_of_service}, {staff_tx_history.SERVICE_CODE})+90) then "A Face to Face is OVERDUE!"

4) I changed the {@accum} formula to:

whileprintingrecords;
numbervar grpsum;
numbervar grtotal;

if{@my formula}= "A Face to Face is OVERDUE!" THEN
(grpsum := grpsum + 1;
grtotal :=grtotal + 1);

5) I kept the {@displgrsum} and {@displgrtot} the same.

I would have never been able to figure this out without your assistance, I appreciate your help immensely!!! Thank you! :-D