using formulas in formulas 1

[hjohnson919]

I'm trying to use a formula like a subroutine using Global variables to pass values back and forth. Does that work? Here is what I have:

REM @R&B
REM assumes everyone is discharged
WhilePrintingRecords
Global TestDate AS date
Global Fee AS number
Global Index AS number
Global LeftDif AS number
Global RightDif AS number
Global RangeDif AS number
Global Sdate AS date
Local KeepIndex AS number
Local KeepFee AS number
Local KeepRightDif AS number
Local SubtotalCharge AS number
Local Dummy AS number
Local NumDays AS number
Local i AS number

SubtotalCharge = 0

REM Find the row where the admission date falls between the start date and the end date
TestDate = {view_episode_summary_discharge.preadmit_admission_date}
Dummy = {@FindFee}
If Index = 0 _
Then
formula = -1
Else
KeepIndex = Index
KeepFee = Fee
KeepRightDif = RightDif

REM Check to see if the discharge date also falls between the start date and end date of that row
TestDate = {view_episode_summary_discharge.date_of_discharge}
Dummy = {@FindFee}
Select Case Index
Case 0
formula = -1
Case KeepIndex
NumDays = DateDiff("d", {view_episode_summary_discharge.preadmit_admission_date}, _
{view_episode_summary_discharge.date_of_discharge})
formula = NumDays * KeepFee
Case IS > KeepIndex
i = Index - KeepIndex - 1
REM calculate the subtotal of the charge from the admission date to the end of the first Fee
NumDays = KeepRightDif +1
SubtotalCharge = SubtotalCharge + (NumDays * KeepFee)
REM increment the subtotal by the amount from the start of the final Fee to the discharge date
NumDays = LeftDif
SubtotalCharge = SubtotalCharge + (NumDays * Fee)
REM work backward through the Fee changes as many times as needed
Do While i > 0
TestDate = CDate(DateAdd("d", -1, Sdate))
Dummy = {@FindFee}
NumDays = RangeDif
SubtotalCharge = SubtotalCharge + (NumDays * Fee)
i = i - 1
Loop
formula = SubtotalCharge
Case Else
formula = -1
End Select
End If

REM @FindFee
WhilePrintingRecords
Global TestDate AS date
Global Fee AS number
Global Index AS number
Global LeftDif AS number
Global RightDif AS number
Global RangeDif AS number
Dim Sdate AS date
Dim Edate AS date

If {view_episode_summary_discharge.program_code} = "APS" _
Then
Select Case TestDate
Case CDate(#9/1/2001#) To CDate(#11/30/2002#)
Fee = 589.00
Index = 18
Sdate =CDate(#9/1/2001#)
Edate =CDate(#11/30/2002#)

Case CDate(#12/1/2002#) To CDate(#7/31/2004#)
Fee = 647.00
Index = 19
Sdate =CDate(#12/1/2002#)
Edate =CDate(#7/31/2004#)

Case CDate(#8/1/2004#) To CDate(#1/1/2010#)
Fee = 756.00
Index = 20
Sdate =CDate(#8/1/2004#)
Edate =CDate(#1/1/2010#)

Case Else
Fee = 0
Index = 0

End Select
End If
If Index = 0 _
Then
LeftDif = 0
RightDif = 0
RangeDif = 0
Else
LeftDif = DateDiff ("d", Sdate, TestDate)
RightDif = DateDiff("d", TestDate, Edate)
RangeDif = DateDiff("d", Sdate, Edate)
End if
formula = Index

The very first time through, the TestDate is 11/20/2002 in the "calling" routine but null (or blank or something that doesn't print) in the "subroutine". The second, third, etc times if appears that the date is "passed" correctly but the returned answer is wrong.

My question is: am I on the right track? Will @FindFee be executed when the reference to it is found in @R&B? And will it be executed again when it is encountered a second and third time (the third time is actually in a loop)? Is my problem due to this technique or some other logic error?

 

[smcnulty2000]

I don't believe this technique will work, but I'm interested to find out if I'm wrong.

Here's what I think is going on: Your @findfee gets called and then evaluated the first time in @R&B, and then crystal does not evaluate it again for the rest of the @R&B formula.

Think about it this way, if you put in a statement at the beginning of the @R&B to 'evaluateafter(@findfee)' it wouldn't occur to you that it would try it more than once. I think crystal is doing that very thing.

I'll see if I can suggest something else but I haven't stepped my way through all of your code yet so I'm not fully certain what the final objective is.

scottm.

 

[hjohnson919]

Thanks Scott.

The objective is to calculate a room and board charge. FindFee selects which per day charge was in effect so that can be multiplied by the number of days to get the charge. If both the admission date and discharge date fall within the time period for a specific rate, the charge is equal to the number of days between the admission date and discharge date time the rate for that time period. If there was one rate in effect at the time of admission and the rate changed during the episode so that a different rate was in effect at the time of discharge, then the charge is equal to the number of days from admission to the end of the rate period times the first rate plus the number of days from the beginning of the second rate period to the discharge time the second rate. If there were more than one rate change during the episode, you add in the amount calculated for the whole time period each intervening rate was in effect. For example:

Person 123 was admitted on 11/20/2002 and discharged on 3/1/2005. The rate from 11/20/2002 to 11/30/2002 (11 days) was $589; the rate from 12/1/2002 to 7/31/2004 (609 days)was $647; the rate from 8/1/2004 to 3/1/2005 (212 days - they don't charge for the day of discharge) was $756. The total charge should be $560,774. I'm getting -1. My second test record was admitted on 4/14/2004 and discharged on 3/9/2005. The total charge should be $236,843. I'm getting $193,781(less than I should). My third test record was admitted on 5/13/2004 and discharge on 3/16/2005. The total charge should be $223,372 but I'm getting $232,092 (more than I should). Finally, my last test record was admitted on 9/11/2004 and discharged on 3/11/2005 which completely falls within one rate. The total charge is being correctly calculated at $136,836.

 

[hjohnson919]

I should say that this is Basic syntax in 8.5.

I remarked out all the lines of code in @R&B except the declarations, the WhilePinting Records, SubtotalCharge = 0, TestDate = {view...} and DumDate (AKA Dummy) = {@FindFee}. In @R&B formula = DumDate; in @FindFee formula = TestDate.
I get the following results

Patient# Admit Discharge @R&B @FindFee
123 11/20/2002 3/1/2005
223 4/14/2004 3/9/2005 11/20/2002 11/20/2002
323 5/13/2004 3/16/2005 4/14/2005 4/14/2004
423 9/11/2004 3/11/2005 5/13/2004 5/13/2004

Notice that it looks like the formulas are one record behind. That makes me wonder why I get the right answer for the last record.