I have 3 tables: Intake, Echo Tracking, and Echo Results.

I would like to create a calculation that will add a certain amount of years to the [color:red]echo next field based on a [color:red]patient most [color:red]recent Echo Date and Risk level.

Case#_Grid# is the primary key which is unique for all patients.

The database should only look at the most recent echo date for each case#grid# and add the number of years based on the risk level onto the echo next field. This value should also appear in the portal.

If patient has risk 0 then the next echo field should be echo date + 5 years.

If patient has risk 1 then the next echo field should be echo date + 2 years.

If patient has risk 2 then the next echo field should be echo date + 2 years.

Please see datbase file. thanks.

DTEST.zip

Well, I spent a full 5 minutes staring at your file's definitions, and decided I have no idea what it does.

Let's say you have TWO tables: Patients and Tests. Each patient can have several tests.

The most recent test date for each patient (calculated from the Patients table) is:

Last ( Tests::Date )

(This is assuming the tests are created in chronological order, and that the relationship is not sorted otherwise. If you cannot be sure of that, use Max ( Tests::Date ) instead.)

The next test date is:

Let (

d = Last ( Tests::Date )

;

Date ( Month ( d ) ; Day ( d ) ; Year ( d ) + Choose ( Risk ; 5 ; 2 ; 2 ) )

)

As mentioned, this is calculated from the Patients table, so I have no idea in which portal this should appear, or why.

Hi Veteran,

Thanks for your help. I added your code into my program but it is adding 5 years to all echo dates.

Can you please help me calculated the next echo date based on the risk level and most recent echo date.

Ex 1. If patient 100 has echo date on 01/01/2006 and has a risk level of 2 then the next echo should be 01/01/08.

Ex 2. If patient 200 has echo date on 01/01/2006 and has a risk level of 0 then the next echo should be 01/01/11. My requirements are:

Risk level 0 = Echo Date + 5 years

Risk level 1 = Echo Date + 2 years

Risk level 2 = Echo Date + 2 years

Pretty please keep in mind the next echo date should only have the patients most recent echo + # of years based on risk level. This data should also appear in the portal. (note: there is now only 1 portal). please see attachment.

DTEST.zip

I am afraid I still cannot understand your structure. See if the attached file helps.

A couple of notes:

1. The relationship is based on an auto-entered serial number in Patients - so each Test is guaranteed to have only Patient associated with it;

2. Risk level is an attribute of Patient. The next test date is also an attribute of Patient. Therefore, both need to be in the Patients table. Your requirement to show the next date in a portal of Tests doesn't make sense to me.

NextTestDate.fp7.zip

thank you for the code. i will put it to use.