double relationships & emailing

[clevow]

Ok folks, bear with me here. I'm having trouble clarifying my own thoughts, so they're not going to be great in writing.

I've got 2 databases - projects and progress_reports. Project outlines go in the project database and progress reports on said project go in the progress_reports db. Makes sense.

I want to email people if the project that they proposed has not had a progress report added in the past two weeks.

I'm using the record ID in the projects db as a project ID. The project ID is entered in the progress reports db and all relevant information is pulled in based on the relationship between the project id in each db. So, the most recent progress report for a given project is determined by a calculation field: Max(project id from progress reports::progress_report_date). That part works fine.

So I can script it to say if status(currentdate)-last report date is more than 14, then send the email. That works fine too.

Here's the catch. I want the email to say, "In your last progress report, you indicated that the next step for this project was:" and then include what the next step was (a field in the progress_reports db). The problem is that I have no idea how to pull the most recent next step entry. Essentially I want to say that the next step is the "next_step" field from the record with the same project ID and the most recent date. But that seems like a double relationship.

If you can make any sense of what I'm trying to do, and have any ideas, let me know.

TIA,

Carrie

[slstrother]

What you need to do can be scripted. The following is assuming that you will e-mail from the projects DB. In your progress_reports DB create a script "sort©":

Sort [sort Order:Date (ascending)]

Go to Record/Request/Page [Last]

Copy [Next_Step]

In your projects DB, create a text field "Next Step"

And this script:

Go to Related Records [progress_reports]

[show only related records]

Perform Script[external:"progress_reports", sort©]

Paste [Next Step]

This should copy and paste the contents of the "next step" field of the latest record in the progress_reports DB into a text field in the projects DB, which can be used in your e-mail.

Hope this helps.

[Thom]

Make your relationship sorted by progress_report_date in descending order - latest first. You can do away with the Max() function; project id from progress reports::progress_report_date will be the latest automatically. Likewise, project id from progress reports::next_step will also be from the latest report.