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
wow, that worked perfectly, thank you so much. I especially like the old_school right/mid/left string manipulation at the end.
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.
Hello,
Try to use this formula to transform field "Next Date" into a calendar field type:
Regards,
Pascal
Yes, that works, though it sees 03/10/2024 as 10th March 24 not 3rd Oct 24, as we're in the UK.
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
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?
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.
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
