Last Patient Record

[lopay]

Hi,

I am using 6, but in the process of upgrading to 7 so If it is not possible in 6, then let me know about 7.

I would like to be able to have a patient's last vitals show up. May sound simple, but I have tried a lot of things.

Lets say that I have person a, b, and c. They are stored in "Main" database. Their vital signs are stored in vital signs database. The two are related by serial number. The vitals are shown through a portal and for a new visit, they just enter the vitals at the bottom of a portal. In a Visit database, which is related by serial number also from main, I would like to be able to look at say date 5/25/2004 and have the last vital signs appear, I would like from them to be calculation fields so that if a new date is entered between two old dates, the data will automatical update it's self.

So I guess what I need is a relationship to it's self based upon the serial and the date of the current record. How do you make it so that it shows the values that are less that that records date only?

[transpower]

In version 7 you would create a non-equijoin relationship based on date.

[lopay]

Thanks transpower, but does anyone know if it is possible in version 6. I am not using 7 yet and I need this before we upgrade (that is if it is possible)

[-Queue-]

In your VitalSigns db, create an unstored number calculation of visitdate = Visits::visitdate. Then modify (or add) a relationship from Visit to VitalSigns to sort by the calculation ascending and visitdate descending. Then make your last vital signs calculations of relationship::field. This should work because the sort order of a relationship determines what the first related record is. I haven't tested it, so let me know if you can't get it to work correctly.

[Fenton]

To see multiple dates in a portal, a now-obsolete 6 method is the "multi-line" key technique, which is to build an Unstored calculation that produces the date, and a bunch of dates before it. The calculation looks like:

Date

Date - 1

Date - 2

etc..

The dates are converted to numbers, which is FileMaker's internal method. So you create a calculation with result number for your date also.

The multi-line above is related to the "date as number" field. So the date, or any earlier (within the range)* will give a match. I used a global field for the date to set the start of the range. But you'd probably use a regular date field. Just change the date field in mine, and its name. Then you can copy/paste the calculation.

*The range can be fairly large, up to 64,000 (30,000?) characters. I did 730 days, 2 years, and it's only 5116 characters. The FileMaker layout was not long enough to show them all -)

The "Smart Ranges" technique would give you an infinite range I think; but requires some serious learning. Depends on your range.

In any case, it should be an Unstored field, so it doesn't bloat the file; but it is not instantaneous.

Included is a calculation to help MAKE the calculation; as well as a script to produce the numbers needed. You may need help with that.

Dates_multi-line.fp5.zip

[lopay]

Wow Fenton, that was a lot of work. I had to add a Serial field, change the datevisitnum to Serial & NumtoText(DateVisit), change the _cdate_txt to include the serial number, created a script to copy all records ( could not find how to do it manually), it works. thanks. I wish the people I work for would upgrade to 7 sooner, I am doing a lot of useless stuff, but I get paid. -)

[Fenton]

Good you could get it working. Hold Alt (Option) when Copying to Copy All Records. Rarely needed.