Get next date calc

[ddinisco]

In my schedule table I have a field called 'date' and a field called 'job#.' There are multiple records with the same job# and different dates. I am looking for a calc called 'next_date' to only show the next date beyond the current date for each job# occurrence.

Thanks in advance

[LaRetta]

If you have a Job table (and it sounds like it since you have a Job#) then you connect Jobs::Job# = Schedules::Job#. Then sort your schedule table (in the relationship dialog) descending on date. The calculation (result is date) in your Jobs table would then be:

Schedules::Date + 1

However, if you don't have a Jobs table (and that Job# comes from external source) then you can achieve the same thing by a self-join on your Schedules table, joining on Job #. Then treat the new self-join table as described above.

If you do not wish to sort your relationship, you can also use:

Last ( Schedules::Date + 1 ) but ONLY if the records entered in the Schedules table are ALWAYS entered in date order ascending.

LaRetta :wink2:

[RodSierra]

Would'nt using the aggregate function Max work better here and alleviate the need to worry about sorts or entry order? Using the self join: Max ( Schedules:Date ) +1?

[LaRetta]

Max() would be the LAST option I would use simply because it IS an aggregate, which is precisely why I did not mention it. :wink2:

[comment]

I thought the question was how to get the date of the first FUTURE record in Schedules - not the day after the last one?

[LaRetta]

Upon reread, I agree! Thank you, Michael.

In that case, I wouldn't mess with the primary Jobs-to-Schedules relationship, leaving it based only on the JobID. Use a NEW relationship ...

Create a calculation (result is date) called Today in Schedules with: Get ( CurrentDate ). Be sure to make it unstored in Options. If you think you might want to see other dates (such as ... you forgot to do something for a schedule last week and you would like to pretend that 'today' is last week) then use a global date field for Today instead so you can modify it if needed. You can populate the global in your startup script for normal 'next day' use. Then join:

Schedules::Today < [color:green]SelfJoin Schedules:Date

AND

Schedules::JobID = [color:green]SelfJoin Schedules::JobID

You will still need to sort this relationship but this time in ascending order on date (if it isn't in natural date creation order) so you grab the first record past Today. This [color:green]SelfJoin Schedules::Date field can be placed directly on your layout so that, from any job, you will see the next upcoming scheduled date.

Edited March 8, 2009 by Guest