Question

I've been fumbling around as best as I can on my own and finally hit a roadblock that I am hoping someone here has a solution to. In my first database, I have periods of start and end dates for each page which represent the start and end dates for a position held at an employer. Then in a second database I used this formula to pull all the related date periods for a specific employer:

prop("Positions")
.map(current.prop("Period"))
.filter(current != "")

As you can see in the photos, there are instances where the end date may not be present because it is an ongoing position. I am trying to use a formula in the second database to look at these related pages and return only the earliest start date and latest end date, and if any of the related pages are missing an end date the result of the formula should be only the earliest start date. The idea here is to sort of automate the start and end period for an employer based on the collection of periods in the database of positions. Currently, a listing of all date ranges is as far as I have gotten. If anyone has any suggestions, I'd really appreciate it. I've already tried rollups, but those will not allow for blank results based on the criteria I laid out. I know I can use a "helper" column, but I am really trying to avoid that if at all possible.

Examples of dates and current formual

Answer 1

Took some work, but of course I would figure it out after posting for help. If anyone is looking for something similar this formula is how I did it using a status column as reference:

if(
    prop("Status") == "Active",
    formatDate(
        prop("Positions")
            .map(dateStart(current.prop("Period")))
            .filter(not empty(current))
            .sort()
            .at(0),
        "MM/DD/YYYY"
    ),
    if(
        prop("Positions")
            .map(dateEnd(current.prop("Period")))
            .filter(empty(current))
            .length() > 0,
        formatDate(
            prop("Positions")
                .map(dateStart(current.prop("Period")))
                .filter(not empty(current))
                .sort()
                .at(0),
            "MM/DD/YYYY"
        ),
        formatDate(
            prop("Positions")
                .map(dateStart(current.prop("Period")))
                .filter(not empty(current))
                .sort()
                .at(0),
            "MM/DD/YYYY"
        ) + " → " + formatDate(
            prop("Positions")
                .map(dateEnd(current.prop("Period")))
                .filter(not empty(current))
                .sort()
                .at(-1),
            "MM/DD/YYYY"
        )
    )
)