Skip to main content
Solved

Find next activity date in simple case management system

B

I have a simple linked table. There is a table of CASES with a bunch of fields and then another table called CASE ACTIVITY which has scheduled activities for each CASE, so the tables are linked by CASEID which is the primary field on CASES.

That all works fine. Under CASE ACTIVITY each row is a dated activity, so :

CASEIDScheduled actionDate
65Sent for review10/10/2024
65Publish findings12/10/2024

I would like to have a formula field in CASES that shows the date when the next activity occurs. And I started with a formula field that simply said "MIN({Date (from Case Activity)})" but it returns a value of zero.

Ultimately it would also need to not pick dates that have already passed.

Any ideas?

Best answer by Pascal_Gallais-

Hello,

I assumed that your purpose is to display the next date to come for each case, my apologies if i misunderstood your request.

Here is what i tried.

Table Case Activity

Case 1 hase 3 dates, one past and 2 to come

Table Case

Field "Next date" diplays the next date to come.

To achieve that, I did the following

Table Case Activity / Numeric date

Formula field "number_date" transforms each date into an integer:

the "+0" at the end of the formula is to make sure that we do end up with a numeric field type.

Table Case / Formulas

Field dates is a rollup on field "number_date" from table "Case activity" with a condition to discard past dates:

Field "First_Date" returns the minimum of field "Dates" and transform it into text type:

Field "Next date" makes the result look like a date:

Note that this field is a text type field, not a calendar type.

Regards,

Pascal

View original
Did this topic help you find an answer to your question?

9 replies

Pascal_Gallais-

Hello,

I assumed that your purpose is to display the next date to come for each case, my apologies if i misunderstood your request.

Here is what i tried.

Table Case Activity

Case 1 hase 3 dates, one past and 2 to come

Table Case

Field "Next date" diplays the next date to come.

To achieve that, I did the following

Table Case Activity / Numeric date

Formula field "number_date" transforms each date into an integer:

the "+0" at the end of the formula is to make sure that we do end up with a numeric field type.

Table Case / Formulas

Field dates is a rollup on field "number_date" from table "Case activity" with a condition to discard past dates:

Field "First_Date" returns the minimum of field "Dates" and transform it into text type:

Field "Next date" makes the result look like a date:

Note that this field is a text type field, not a calendar type.

Regards,

Pascal

B
1 person likes this
  • B
    bitstreams_red
B
  • bitstreams_red
  • Author
  • Known Participant
  • 13 replies
  • October 3, 2024

wow, that worked perfectly, thank you so much. I especially like the old_school right/mid/left string manipulation at the end.

B
  • bitstreams_red
  • Author
  • Known Participant
  • 13 replies
  • October 3, 2024

ok - slight final glitch. I wanted to add the 'Next date' field to a timeline view, but Timeline only allows you to add a 'Date' field to the view and this is a formula field.

Pascal_Gallais-

Hello,

Try to use this formula to transform field "Next Date" into a calendar field type:

Regards,

Pascal

B
  • bitstreams_red
  • Author
  • Known Participant
  • 13 replies
  • October 3, 2024

Yes, that works, though it sees 03/10/2024 as 10th March 24 not 3rd Oct 24, as we're in the UK.

Pascal_Gallais-

Yes, you are right, I would advise to modify the "Next date" formula to express it in US format:

You can then choose what date format shall be applied on the "final date" formula field (European in this example):

Regards,

Pascal

TheTimeSavingCo
Forum|alt.badge.img+18

Hm I realize your problem's already solved but I'm curious about the business logic here.  In the "Case Activity" table, do you have some sort of status marker to indicate whether the activity has been completed?

Adam, Consultant @ The Time Saving Co
B
  • bitstreams_red
  • Author
  • Known Participant
  • 13 replies
  • October 4, 2024
TheTimeSavingCo wrote:

Hm I realize your problem's already solved but I'm curious about the business logic here.  In the "Case Activity" table, do you have some sort of status marker to indicate whether the activity has been completed?


Yes I do and I will need to handle overdue activities but I wanted to play with a timeline view that showed what was due to happen this week. It may be that it's not a linear path, so there are activities that are overdue but others are scheduled and can be performed on time.

TheTimeSavingCo
Forum|alt.badge.img+18
bitstreams_red wrote:

Yes I do and I will need to handle overdue activities but I wanted to play with a timeline view that showed what was due to happen this week. It may be that it's not a linear path, so there are activities that are overdue but others are scheduled and can be performed on time.


Hmm, I wonder if it's possible to filter the date lookup field instead to achieve your desired result:

Link to base

Very simplified example that assumes we can use the Status fields to filter for what your business logic needs, of course

Adam, Consultant @ The Time Saving Co

Reply


Most helpful members this week

Terms & Conditions