date filter headache

[rbrouwer]

I'm sure there is a simple solution but my brain can't quite see it at the moment. I'm trying to make a calculation based on whether a date in the future is within 14 days of the current date, more than 14 days in the future from the current date or less than the current date (ie. in the past).

I tried

date > Today +14

date < Today +15

date < Today .. and various combinations. The idea is to apply the results to a portal so I can show the three different sets of patients who are to be reviewed in the next 14 days, or who need review more than 14 days from now and those who have been reviewed in the past. I can't get it to work and any ideas much appreciated.

[spb]

I'm a bit confused by your logic. Does not "within 14 days of the current date" plus "more than 14 days in the future" plus "less than the current date" simply collapse to "all records? Except dates that are *exactly* 14 days from the current date, or the ones *with* the current date will be the only ones exempt.

Or did you want to see three groups in the portal one after the other, within 14 days, greater than 14 days, in the past? From the way you wrote, I got the impression that you wanted to see all three groups as a single list in the portal. A date sort would keep them seperate.

Finally, you should never use the "Today" function in scripts or calcs. This is a badly designed function that will give you grief down the road. Always use instead Status(CurrentDate) to do the same thing.

Let me know more about what you are trying to do (all three lists at once, which is simply everybody, or each list one after the other). I have some ideas on how to go about it.

Steve Brown

[Vaughan]

Firstly, don't use the TODAY function. Use Status(CurrentDate)instead.

"The idea is to apply the results to a portal..."

Ahh, the results from Status(CurrentDate) need to be unstored, and unstored fields cannot be used as remote keys in relationships...

Yout probably need to use the Smart Ranges technique to achieve the results you want in FMP 6 and earlier. Search for Smart Ranges in Google.

[spb]

Firstly, don't use the TODAY function. Use Status(CurrentDate)instead.

Er, that's what I said.

Insofar as the unstored nature of Status(CD), as I am assuming the original poster will need to look at his three lists one after the other (as opposed to all in one big list), he will probably need to alter his portal relationship between one list and the next via script. Depending on his response, I was going to suggest setting a global field to one of his three list criteria & have a calc keey work from the contents of the global, rather than the Status(CD) function itself.

Steve Brown

[rbrouwer]

Thanks for the advice about not using Today function.

My idea is for 3 portals, one which lists patients who are due to be seen in the next 14 days (from the current date), one which lists patients who are due to be seen after the next 14 days, and the third which lists all patients that have been reviewed in the past (ie before the current date). I was toying with a calculation using Case and filtering the portal based on the 3 different results it will return based on which category a patients date of review falls in.

ie. Case (date is in next 14 days, 1,

date is more than next 14 days,2

date is in the past,3)

I'll try out the ideas you've suggested and let you know.

Thanks for the advice.

[spb]

If you have three porrtals, then you'll need three different relationships. I assume you are viewing in one file with a primary ID (patient ID) and the date info is in another file which will be viewed in the portals. If it's all in the same file, then you can do the same thing, but with self relationships.

Make three calculation fields, one for each relationship/portal in the date info file. The calcs would (for the first one)

Case (date > Status(CurrentDate) and date < Status(CurrentDate) + 15,

PatientID, "")

This will give you a field, call it ID1, that will mostly be empty. If a record is within the date range, the field will contain the patient ID. Define the relationship for your first portal based on your primary patient ID on one side, and this new field on the other. This portal will now only contain listings for that date range.

When you place the fields in the portal, make sure you choose the new ID1 relationship. This is a common mistake. Portal fields placed that do not come from the portal relationship will give you bizarre results.

Steve Brown

[comment]

If you use Get functions in defined calculation fields, the calculation results must be unstored or the calculation will not update when the status changes.

OTOH, an unstored calculation cannot be used on the right-hand side of a relationship.

[spb]

The original poster is using FM 6, so Get functions are not available. But your comments about stored vs. unstored relationship keys is germane.

Perhaps a script could be run just before one needs to view the portals. The three-part script would isolate each of the three groups, then run a replace with calc into standard text fields. The calc is as per my post above. Then the fields could be used as right-hand keys?

Steve Brownabove.

[comment]

Either that, or the dates need to be spelled out on the left-hand side.

14 dates are not that difficult to spell out, but for a general 'before today' something like Smart Ranges is called for - as Vaughan already pointed out.

[rbrouwer]

Thanks very much for all your help...I have got it working it seems.

The calculation (unstored so it refreshes)

Case(date_next_mdm >= Status(CurrentDate) and date_next_mdm < Status(CurrentDate) + 15, 1,

date_next_mdm < Status(CurrentDate),2,

date_next_mdm >= Status(CurrentDate) + 15,3,

"")

The script:

Looping script which sets a text field (patient_status) to "current, past of future" depending on whether a 1,2 or 3 is returned by the above calculation. The script triggers whenever you navigate to the page which displays the portals.

The relationships:

Auto-enter text field with either "current, future or past" : self-join to patient_status field on which the portals are based.

Your suggestions were great ! Thanks. And I promise to read 'smart ranges' ... but right now after work my cognitive abilites are seriously diminished.