Child Records: Next Date

Forum|Forum|7 years ago
July 6, 2018
3 replies
46 views

+14

William_Nutt
Known Participant

I’m this close to my objective. Please help me get there! Here’s a simplified explanation:

Objective:

I have tasks and sub-tasks (parents and children)—each with a due date.
For each parent, I need a field to calculate its next (MIN) child date.
With the parents and children in separate tables, this is easily accomplished with a Rollup field. However, I very much need the parents and children in the_same_ table, and unfortunately, Rollups remain inexplicably incompatible with linked records in the same table.

Progress:
This may be the wrong approach altogether, so please don’t let it influence your own ideas!

By creating a [Dependencies] table with a single record linked to all tasks (parents and children), I’m able to use Rollup and Formula fields to create some potentially useful values:
- An array of all due dates
- An array with a value for each child formatted as {Parent}:{Due Date}
- For each parent, a string of its children’s due dates. If I could convert that string back to an array and thus use MIN, I’d be golden. Or if there’s another way to find the minimum value in a string, that’d be terrific as well.

Willing Concessions:

I’d prefer not to need a [Dependencies] table but am willing to do so so long as it requires only a single record.
It’s not worth it to incorporate a third-party tool like Zapier; I’d rather keep the parents and children in separate tables.

Again, I don’t want to influence your solutions, so I’ve withheld links to my experimentation, but please let me know if it’d help.

Thanks so much!

This topic has been closed for replies.

W

+5

W_Vann_Hall
Inspiring
Forum|Forum|7 years ago
July 11, 2018

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.

Like

+14

William_Nutt
Author
Known Participant
Forum|Forum|7 years ago
July 19, 2018

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.

Can’t thank you enough for taking the time to respond so thoughtfully! I will implement your suggestions and continue to seek more efficient options. I sure hope same-table Rollups will become available soon!

Like

+14

William_Nutt
Author
Known Participant
Forum|Forum|7 years ago
November 3, 2018

For anyone else who stumbles upon this post, same-table Rollups are now functional; however, the absence of automated bidirectional linking creates a persisting challenge. Nonetheless, I’m much better off than I was previously.

Like

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded