Find next activity date in simple case management system

[bitstreams_red]

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 :

CASEID	Scheduled action	Date
65	Sent for review	10/10/2024
65	Publish findings	12/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?

[Pascal_Gallais- · Accepted solution]

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

[bitstreams_red]

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

[bitstreams_red]

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

[bitstreams_red]

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]

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?

[bitstreams_red]

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]

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