Calculated date range for ValueList

[lsmall]

Hi,

I would appreciate some help with this.

I've got a database for some information on hospital patients. Each patient record has a hospital admission date which is manually entered and can be unique for each patient record. I need to associate dates during the admission with events that occur that are related to each patient. I would like these associated dates to be part of a value list calculated based on the original admission date. So the value list should dynamically contain a range of dates specifc to each patient, based on the manually entered admission date. The range would be (admission date up to admission date+10days) So the date value list for each patient should contain 11 values in this case.

I'm sure there is a way to do this, but it is eluding me.

Thanks

[Fenton]

Create a calculation field, = Admission date + 10. Then a compound relationship. I'll call the other target date "DateEvent".

PatientID = PatientID

AND AdmissionDate <= DateEvent

AND _cAdmissionDate10 => DateEvent

You could use this relationship for either a portal, or to filter a value list targeting the DateEvent field.

[Søren Dyhr]

Not that Fentons suggestion in any way is wrong, but have due my expression difficulties in english, made you a template to investigate instead!

--sd

Admission.zip

[lsmall]

Both of these solutions look great.

I'll need to tweak them, but they are working.

Thanks a lot for your help!

[comment]

And the need for Evaluate() and Auto-Enter is?

Admission.fp7.zip

[Søren Dyhr]

Ah! I tried to ensure the field was index'able if say it was a global - good point though!

--sd

[chemparrot]

Here's a similar problem. This is for a freezer inventory.

One table is Boxes, with fields Box ID and Date Received, which also tells me which freezer the box went into.

Another is Specimens, which also has a Date Received, which tells me information about the specimens in each box.

I would like to set up a value list of "current boxes", which would be Box IDs for all the samples received in the last 60 days. I got this working just fine in the Boxes table, but it wouldn't work in the layout for the specimen table.

I had a self-join in Boxes

DateReceived >= cCurrentBoxes

where cCurrentBoxes is a calc field = DateReceived - 60

as Fenton suggested above. The value list was defined to show only related values. This worked great in Boxes, but not in Specimens, where I had the two tables were linked by DateReceived (which should be the same in both cases, and which I don't mind entering twice, as long as I can get the list filter working).

So, I tried taking out the "intermediate" Boxes instance in the relationship diagram, redefined a direct relationship between

Specimens::DateReceived>=Boxes::cCurrentBoxes

and redefined the value list to show related values from that relationship. This shows a list of ALL the boxes, not what I want.

Help??!

[chemparrot]

Well, duh! I was taking Fenton a bit too literally.

I nixed the self-join in the Boxes table, then put cCurrentBoxes (which I've now changed to Get(CurrentDate) - 60, which better reflects what I'm trying to see in the list) in the Specimens table, joined Boxes and Specimens like so:

Specimens::cCurrentDate<=Boxes::DateReceived

And now it works just fine. The difference is that I just need to find all the boxes received in the last 60 days, while Ismall needed to get a list that was linked to a particular patient, and that is what made the self-join necessary. It wasn't needed for my simple case. Don't know why I had difficulty - may have been basing the relationship on a calculated field, which is indexable in this case, so OK (usually when I've tried to do this since moving to FM7, that hasn't been the case).

Just in case this helps someone else.... :crazy: