Jump to content

Most Recent Related Record


This topic is 3007 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Given that I have a table called Projects, and a table called Timesheets (which is related to Projects through a Project Unique ID field), and that the Timesheets table has a Date field, can I create a calculation field in the Projects table that will give me the date from the most recent related Timesheet record? (I realize multiple related records might have the same date, but it doesn't matter to me if it's one or many on the same date.)

Link to comment
Share on other sites

Sort the relationship by reverse timestamp order - the first record will be the one with the most recent timestamp. You can just display the relevant field directly on the Parent layout to view...

Link to comment
Share on other sites

1 hour ago, comment said:

What would this calculation field be used for?

I want to (a) search for projects where the most recent timesheet is over X days old and mark them as inactive projects, and (b) the project managers want to be able to see on a regular basis whether projects are active or "stale" - this would probably be done via report with a list of active projects and their "last work" date, along with some other budgeting information.

Link to comment
Share on other sites

15 minutes ago, Courtney said:

(a) search for projects where the most recent timesheet is over X days old ...

You don't need a calculation field for this: you can simply search for projects where Timesheets::Date > Get ( CurrentDate ) - X and omit them.

I didn't understand the (b) part - perhaps you could use conditional formatting to "mark" the inactive projects. In any case, either the Last() or the Max() function could be used (use Last() if you are sure records are entered in chronological order).

Link to comment
Share on other sites

This topic is 3007 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.