Courtney Posted December 3, 2015 Share Posted December 3, 2015 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 More sharing options...
webko Posted December 3, 2015 Share Posted December 3, 2015 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 More sharing options...
Fitch Posted December 3, 2015 Share Posted December 3, 2015 Another option is the Last function. Link to comment Share on other sites More sharing options...
comment Posted December 3, 2015 Share Posted December 3, 2015 48 minutes ago, Courtney said: can I create a calculation field in the Projects table that will give me the date from the most recent related Timesheet record? What would this calculation field be used for? Link to comment Share on other sites More sharing options...
Courtney Posted December 3, 2015 Author Share Posted December 3, 2015 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 More sharing options...
comment Posted December 4, 2015 Share Posted December 4, 2015 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 More sharing options...
Courtney Posted December 7, 2015 Author Share Posted December 7, 2015 Max() did the trick for me. Thank you all! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3190 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 accountSign in
Already have an account? Sign in here.
Sign In Now