LOOKUP with a MAXROW

[Grant_Stead]

I’ve got a table: SCOPE and a table: NOTE (These are not reference together, and I do not want a helper virtual column. My data sets are very large.)
I have an action on the SCOPE table to LINKTOFORM to create a note. On Save I am attempting to pass that note to a column on the SCOPE table “scope_current_mgt_note” I am using an action to “Execute an action on a set of rows” And then I have my data change action, When I simply put in the MAXROW expression, it injects the correct row ref number. However, when I wrap it in a LOOKUP() it pushes a null value.

ANY(SELECT(
NOTE[note_text], MAXROW(“NOTE”, “note_datetime”, [note_REF_table]=[_THISROW])=[note_key]
))

[Steve]

Is the NOTE row being added as part of the SCOPE row addition?

[Grant_Stead]

No, the SCOPE record already exists. and I have a prominent action to create a note, using LINKTOFORM, where I capture the SCOPE key…

[Bellave_Jayaram]

I wish we could do something like MAXROW(…)[note_text]

[Grant_Stead]

I know right, And I can’t figure out why wrapping maxrow in a lookup isn’t working…

[Bellave_Jayaram]

Did you try :
INDEX(SELECT(NOTE[note_text], [note_key]=[_THISROW])), 1)

[Grant_Stead]

Basically from the SCOPE table column “scope_current_mgt_note” I’m trying to return the most recent child note_text

The NOTE table has a column that caries the SCOPE key…

[Bellave_Jayaram]

Ok, maybe I’m not understanding your table structure. I guess you have multiple notes referring to the same scope and you want to fetch the latest note for that scope.
Can you verify if this is your setup?

SCOPE hasColumn:“scope_current_mgt_note”
SCOPE hasColumn:“scope_key” isKey:TRUE
SCOPE hasAction:UpdateNote type:”Set the value of a Column” column1Name:” scope_current_mgt_note” column1Value:
ANY(SELECT(
NOTE[note_text], MAXROW(“NOTE”, “note_datetime”, [note_REF_table]=[_THISROW])=[note_key]
))

NOTE hasColumn:“note_key” isKey:TRUE
NOTE hasColumn:“ note_REF_table”
NOTE hasColumn:“note_text” isLabel:TRUE
NOTE hasAction:setNoteinScope type: “Execute an action on a set of rows” targetTable:SCOPE fortheserows:”note_REF_table” actionToExecute:”UpdateNote“

[Grant_Stead]

Yes sir, nailed it.

[Bellave_Jayaram · Accepted solution]

Could you try this:

INDEX(
ORDERBY(
FILTER(
“NOTE”,
([note_REF_table] = [_THISROW])
),
[note_datetime],
TRUE
),
1
)

[Grant_Stead]

I wrapped that in a lookup, and it worked great!
thanks Jay!

[Bellave_Jayaram]

You are welcome
It would still help to know why your original formula didn’t work.

[Steve]

This how I suspect the expression expands:

ANY(
  SELECT(
    NOTE[note_text],
    (
      ANY(
        SELECT(
          NOTE[note_key],
          AND(
            ([note_REF_table] = [_THISROW]),
            (
              [note_datetime]
              = MAX(
                SELECT(
                  NOTE[note_datetime],
                  ([note_REF_table] = [_THISROW])
                )
              )
            )
          )
        )
      )
      = [note_key]
    )
  )
)

I see potential breakdowns if [note_REF_table] or [note_datetime] ever evaluate to blank, which would result in unexpected matches and thereby unexpected results.

Here’s how I suspect Jay’s expression expands:

ANY(
  SELECT(
    NOTE[note_text],
    (
      [note_key]
      = INDEX(
        ORDERBY(
          SELECT(
            NOTE[note_key]],
            ([note_REF_table] = [_THISROW])
          ),
          [note_datetime],
            TRUE
        ),
        1
      )
    )
  )
)

Jay’s is much more efficient, if nothing else.

[Bellave_Jayaram]

Wow!! Thanks for that Steve🙏

[CXIntegration]

Not sure why it didn't let me respond to Grant but how did this get rapped into a LOOKUP?