Calculated Field Relationship

[bb84604]

I have 3 tables:

1- A Dashboard table with "search" fields that I use to filter records using portals

2- A Patient table with basic patient info

3- An Appointment table listing all the appointments

I am trying to get a portal on the Dashboard to show all the patients whose last scheduled appointment is between two dates

I set up a calculation in Patients, "Max (AppointmentInfo::AppointmentDate)" that displays the date of their final appointment, but have hit a dead end since I can't use that calculated field in my relationship with the Dashboard right?

I know there is an easy solution somewhere, but can't find it.

Any help is greatly appreciated...

[Peter (duksis3)]

Look in www.hierarchy.lv - TimeTable

[bb84604]

Thanks for the idea. Is there not a way to do it with my existing setup (I have put a lot of time into customizing)?

Thanks,

Brian

[bobearl]

I'm a novice, so you should take this with a grain of salt, and my apologies if I'm either completely off base or stating the obvious. Seems like one way to come at this is to use a stored calc on the child side of the relationship. I think that you may have to refresh the values (I just posted a couple of days ago requesting help on the most efficient way to do this) since they are stored, but then you are able to make the relationship. Another option would be to do a find by script. If you have an example of your setup, it may be helpful to post for the folks here to troubleshoot.

[Lee Smith]

I would do a search of the Forum for +Timeline, as I know this topic has come up more than once in the past.

Here are two links to older files that may help you.

Bob Weaver

and

Lee Smith

Note: [color:blue]www.fmfiles no longer exist. And the files are done in older version using the functions available at the time. They should however convert to the current versions by just opening them up.

HTH

Lee

[Søren Dyhr]

I set up a calculation in Patients, "Max (AppointmentInfo::AppointmentDate)" that displays the date of their final appointment, but have hit a dead end since I can't use that calculated field in my relationship with the Dashboard right?

Make a search for Ugo's method here in this forum ... or study the attached file!

--sd

bb84604.zip

[David Jondreau]

I would use a script trigger on the Appointment Date field to run a script updating a stored Patient Last Date field.

[bb84604]

Thanks for the help. That Ugo method seems perfect.

Why do you recommend script triggers DJ?

[Lee Smith]

Soren, can you, or anyone else for that matter, Please post the link to where Ugo's Method originated.

I have read all of the treads that I could locate using your Keywords of Ugo's Method (and some others too), and I'll be darned if I can find it.

TIA

Lee

[Søren Dyhr]

Could it be this one??

http://fmforums.com/forum/showtopic.php?tid/153197/fromsearch/1/tp/0/all/1/

--sd

[David Jondreau]

Because it's simple to implement and fast to use.

You change the data type of one field, add a script trigger to the Appointment Date entry field, and write a simple Set Field script and you're done.

The related records will just show without the need for background calculated keys which would be speedier.