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]
))
Solved! Go to Solution.
Could you try this:
INDEX(
ORDERBY(
FILTER(
โNOTEโ,
([note_REF_table] = [_THISROW])
),
[note_datetime],
TRUE
),
1
)
Is the NOTE row being added as part of the SCOPE row addition?
No, the SCOPE record already exists. and I have a prominent action to create a note, using LINKTOFORM, where I capture the SCOPE keyโฆ
I wish we could do something like MAXROW(โฆ)[note_text]
I know right, And I canโt figure out why wrapping maxrow in a lookup isnโt workingโฆ
Did you try :
INDEX(SELECT(NOTE[note_text], [note_key]=[_THISROW])), 1)
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โฆ
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โ
Yes sir, nailed it.
Could you try this:
INDEX(
ORDERBY(
FILTER(
โNOTEโ,
([note_REF_table] = [_THISROW])
),
[note_datetime],
TRUE
),
1
)
I wrapped that in a lookup, and it worked great!
thanks Jay!
You are welcome
It would still help to know why your original formula didnโt work.
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.
Wow!! Thanks for that Steve๐
Not sure why it didn't let me respond to Grant but how did this get rapped into a LOOKUP?
User | Count |
---|---|
40 | |
34 | |
28 | |
23 | |
17 |