date of last appointment as calculated field

[poly_paparas]

Hi

I have been trying to make a database solution for my medical practice and I am stuck with something.

The database contains two main tables

Patients with the usual patient details, history etc and

Visits where the patients' visits are stored.

The two are related via the Pt_ID so that each patient can have many visits.

In the patient's table I want to have a field where I can store the date of his last visit.

At the moment I have a layout with a portal where all the visits are displayed for the selected patient and by clicking on a button I initiate a script whereby the portal is sorted by date and then filemaker is instructed to go the last row and get the date from there.

The problem is that with this approach I have to click the button first, and the script steps can not be used in a calculation field to enter the information automatically into the field.

The reason I insist on doing this is that I have a list view layout, where all the patients are displayed with some basic information and I want to be able to display the date and reason of the last visit as well!

Many thanks to anybody who can offer some help

George

Using FM Pro 7 on Windows XP

[-Queue-]

Last(relationship::datefield) will return the most recent date, if the relationship is sorted by datefield ascending. relationship::datefield will also work, if the relationship used is sorted by datefield descending.

[poly_paparas]

Many thanks for the advice.

My related table was already sorted by date but when I tried creating a new calculated field on the main database where the result would be Last(relationship::datefield), this field seems to stay empty!

I suspect the problem might be with tha fact that when a record is created in the main table (a patient record), there are no related visit records.

I did not make the database automatically create a new visit record every time a new patient record was created as there could be isntances where I would only put the patiets details in (after a phonecall for example) without the patient actually having visited me.

Then, by creating a new visit record from a portal I could record when the patient actually visited me.

The problem is that, even when I do create visit records for a patient, the last_visit_date field in the patient's record still remains empty.

Is it possible that filemaker is not updating the calculated field? Or is there another way to do this?

[-Queue-]

My suspicion would be that you used an auto-enter calculation on a date field instead of a true calculation field. The former would not update until you changed the main record, while the latter should continuously update since it cannot be stored.

[poly_paparas]

Queue, you are a genious!!!

That was exactly the problem from the beggining, and it never crossed my mind!

Thank you again very much for your help, it now works like a dream.

George

[Himitsu]

and another helpful idea is after, just copy the fields straight from the portal onto the layout. That will use the sort of the relationship rather than the sort of a script. So you can have only the field on the layout, and that will show only the newest record. So the sort of the relationship takes a higher order then just sorting.

[poly_paparas]

Thanks Himitsu,

I have already done that.

By the way, since we are talking about portals, is it possible, if there are more than one portals on a layout, to select a specific portal and go to a specific line using a calculation or a script?

I have 5 portals on a layout, one of which is for imaging studies (xrays etc) related to the main table via (pt_ID). Next to this I have another portal from the exact same table only this time I have only one line visible that displays the picture.

I want to be able to select a study from the left portal (where 5 lines are visible) and the right portal to change automatically to the picture that corresponds to the selected record on the left. Obviously the reverse would also be nice althouth I fear it might be too complicated...

In other words I am trying to use the right portal as a window displaying the images only - much bigger than the other portal would allow me to.

Any ideas by anyone?

Many thanks for all the help

George

[Ender]

I don't quite get what you're doing, but you can specify a portal by using the Go to Field [ Relationship::Field ] script step. Specify a related field that is in the target portal. Then you can jump to a specific portal row with Go to Portal Row [ ].

[poly_paparas]

I have two portals from the same table, side by side.

The table has 5 fields, pt_ID (related to the main table), date, description, result and a container field with the image

The first one has 5 rows and displays the fields date, description and result. The other portal only has one row in the size of all the other rows together, and only has one field, the container that has the image in.

I want to be able to point to a record (row) on the left and the portal on the right to automatically show the relevant image from the same record.

I know how to get to a specific row in a portal, the question is, how can I then go to the other portal, displaying data from the same table and display tha image.

I tried GO To field [related::image_field] but it does not switch to the second portal.

Is there a way to tell filemaker to jump to a specific portal?

Any ideas???

George

What I want

[poly_paparas]

It works!

thanks for the advice guys

I am attaching a small file to demostrate what I was trying to do

George

portals.zip