After kicking this around a bit in my head, I think your approach with a [Dependencies] table is probably your best, and possibly only, bet. You are attempting to compare values across multiple records in a single table — and, as best I can tell, using such an all-to-one linked-record appears to be the only way.
You’ll like the next part of my post even less: Your best bet at determining which child date is earliest is probably going to be through a really unpleasant IF() statement. Whether or not this is at all feasible depends on the likely maximum number of active child tasks.
Here’s a pseudo-code example based on five child tasks:
IF(
Date1<Date2,
IF( ; Date1 < Date2
Date1<Date3,
IF( ; Date1 < Date2,Date3
Date1<Date4,
IF( ; Date1 < Date2,Date3,Date4
Date1<Date5,
Date1, ; Date1 < Date2,Date3,Date4,Date5
Date5 ; Date5 < Date1,Date2,Date3,Date4
),
IF( ; Date4 Date1,Date2,Date3
Date4<Date5,
Date4, ; Date4 < Date1,Date2,Date3,Date5
Date5 ; Date5 < Date1,Date2,Date3,Date4
)
),
IF( ; Date3 < Date1,Date2
Date3<Date4,
IF( ; Date3 < Date1,Date2,Date4
Date3<Date5,
Date3, ; Date3 < Date1,Date2,Date4,Date5
Date5 ; Date5 < Date1,Date2,Date3,Date4
),
IF( ; Date4 < Date1,Date2,Date3
Date4<Date5,
Date4, ; Date4 < Date1,Date2,Date3,Date5
Date5 ; Date5 < Date1,Date2,Date3,Date4
)
)
),
IF( ; Date2 < Date1
Date2<Date3,
IF( ; Date2 < Date1,Date3
Date2<Date4,
IF( ; Date2 < Date1,Date3,Date4
Date2<Date5,
Date2, ; Date2 < Date1,Date3,Date4,Date5
Date5 ; Date5 < Date1,Date2,Date3,Date4
),
IF( ; Date4 < Date1,Date2,Date3
Date4<Date5,
Date4, ; Date4 < Date1,Date2,Date3,Date5
Date5 ; Date5 < Date1,Date2,Date3,Date4
)
),
IF( ; Date3 < Date1,Date2
Date3<Date4,
IF( ; Date3 < Date1,Date2,Date4
Date3<Date5,
Date3, ; Date3 < Date1,Date2,Date4,Date5
Date5 ; Date5 < Date1,Date2,Date3,Date4
),
IF( ; Date4 < Date1,Date2,Date3
Date4<Date5,
Date4, ; Date4 < Date1,Date2,Date3,Date5
Date5 ; Date5 < Date1,Date2,Date3,Date4
)
)
)
)
(The ;-preceded comments are for illustration’s sake — and to help me keep track while writing the pseudocode.)
The pseudocode is actually much nicer than the actual code would be. For one thing, it’s based on there always being five dates to compare, while the actual formula would need to check to see how many child dates existed to be compared. In addition, rather than a nice, antiseptic {Date1}, the real formula would need to include a MID() function extracting the date based on relative offset. (If you have a discrete string of child dates per parent, your work is partly done: You can simply use
MID({String},[(Index-1)*LengthOfDate],[LengthOfDate]) .
If you’re dealing with a single concatenated string containing each {ParentID} followed by the due dates of its children — similar to the method I outlined here — you’ll have to calculate the beginning of the string to extract using a FIND() function offset by the length of {ParentID} and any separator characters before adding the indexed offset.)
Should you decide to go this route, a couple comments:
- I would construct the
{ParentID}-child date string using unique boundary characters — for instance, '!' before each {ParentID} and '|' after each child due date. That way, if you’re extracting a parent substring from a larger concatenation of multiple parent substrings, your MID() function would run something like this:
MID(
{LongString},
FIND(
'!'&{ParentID},
{LongString}
)+LEN(
{ParentID}
)+2,
FIND(
'!',
{LongString},
FIND(
'!'&{ParentID},
{LongString}
)+1
)-FIND(
'!'&{ParentID},
{LongString}
)
)
-
You can determine the number of days to compare with
LEN(ParentSubstring)-LEN(SUBSTITUTE(ParentSubString,'|',''))
-
To simplify comparisons, for the dates in the strings rolled up from [Dependencies], I’d use days since epoch:
VALUE(DATETIME_FORMAT(Date,'X'))/86400
That will give you a unique 5-digit value for future dates through the end of 2037. You can revert to date format by multiplying the value by 86400 and using DATETIME_PARSE().
-
Clearly, if you have numerous child tasks, this approach won’t work. However, if you have a fixed number of active child tasks, you can roll up only those in [Dependencies] by ignoring completed tasks, past dates, or dates too far in the future.
I realize none of what I said is what you were hoping to hear — and, keep in mind, I’m describing how I would approach the problem, which is not necessarily the only way it can be solved nor even necessarily the best way it can be solved, but simply the best way I’ve figured out so far to do so. Every time I post one of these Bruno Magli¹ solutions, I’m hoping this will be the day someone shows me how to do it better…
- ‘Ugly-ass,’ according to O.J. Simpson.
