Default values for related ref columns

[Sully]

Longtime listener, first-time caller here. Looked around and couldn't find an answer to this one:

I have a table of "Task" records that has two ref columns: one is a required "Project" field and the other is an optional "SubProject" field. I wanted to make sure that records added via the "Related Tasks" inline on a SubProject detail page would also carry the correct "Project" default value, so I added a formula to the Project column

ifs(isnotblank([SubProject]),[SubProject].[Project])

which worked. The only problem is that it's somehow triggering the reverse problem: if a record is added via the Related Task inline for the Project (i.e. a record that doesn't have a SubProject value) it initially shows up with the correct "Project" default, but as soon as I begin editing other parts of the form, the Project field blanks out (seemingly returning the null case for the ifs() function).

I have tried different variations on the formula and have also confirmed that removing the formula fixes the issue for the Project view, while re-introducing my original problem for the SubProject view. Any ideas on how I can have functional defaults for both use cases? Thanks!

[TeeSee1 · Accepted solution]

I believe this works for you...

IF(ISNOTBLANK([SubProject]), [SupProject].[Project], [Project])

[Sully]

Thanks! Never occurred to me to define the field's initial value with a reference to itself. Counterintuitive, but it works!