Format based on double dereference not working

[Philip_Guin]

Hey folks,

I’m a programmer with some familiarity with SQL, but I’m having trouble getting this format expression to do what I want:

ISNOTBLANK(SELECT([Blockers][Blocking Task ID], [Status] <> "Complete"))

Basically, I have two tables, Tasks and Blockages. Blockages has two columns linking rows of Tasks to other rows of Tasks, one as “Blocked” and the other as “Blocker”. “Blockers” is a virtual column generated on Tasks, returning a list REF_ROWS("Blockages", "Blocked Task ID") I want to format blocked tasks differently, which works just fine… until I try to account for “Complete” tasks not truly blocking as in the above. The above actually gives the exact same result as:

ISNOTBLANK([Blockers])

…which of course is wrong. I’m pretty sure the Status comparison is correct, as I do similar comparisons elsewhere, which work as expected. Anything obvious I’m doing wrong here? Maybe something to disambiguate with a special keyword? Thanks.

EDIT: based on my experimentation, [Status] is getting scraped off the formatted row, not whatever value should be queried by the select expression. My understanding is that [_THISROW] references the formatted row, which doesn’t help me here (seems like it shouldn’t even be necessary.) Is [Blocking Task ID] not returning a row itself? It is of type Ref and otherwise works as expected.

EDIT 2: This appears to be working for me:

ISNOTBLANK(SELECT([Blockers][Blocking Task ID], LOOKUP([Blocking Task ID], "Tasks", "ID", "Status") <> "Complete"))

I’m not sure why its necessary to repeat [Blocking Task ID], but hey I’ll take it. Do let me know if there’s a better way of accomplishing what I want here, maybe by reorganizing my data or caching some state, etc. Thanks for reading.

[WillowMobileSys · Accepted solution]

Philip_Guin:

Anything obvious I’m doing wrong here? Maybe something to disambiguate with a special keyword?

Using a REF_ROWS list, `[Blockers][Blocking Task ID]’, like you have in the first expression does not work in the way you have it implemented when using a selection criteria. I know it probably tests as a valid expression but unfortunately doesn’t work that way.

You will need to change the implementation to run against the Blockages table, like so:

ISNOTBLANK(SELECT(Blockages[Blocking Task ID], 
                                      AND([Blocking Task ID] = [_THISROW].[Blocked Task ID], 
                                               [Status] <> "Complete")))

Secondly, ISNOTBLANK() might not give expected results when the values returned are blank(i know this probably doesn’t apply in this use case - but you know…habits). I prefer to use COUNT() > 0, when testing for presence of rows to avoid any ambiguity.