List of maxrows

Hi, hope everyone is doing well.

I have different child tables all feeding from one and only parent table. I want for the REF column of one of those child tables to:

1). Retrieve the difference between two lists.
2). That result should also contain only the latest values.
3). Be a dependent dropdown according to the place where the form is being filled.

I already made the first objective by substracting one list with another in a virtual column, but I cannot figure out how to make an expression that takes that list but only with the latest values.

Thanks in advance to anyone willing to help me on this one!

0 1 243
1 REPLY 1

Steve
Participant V

Try:

SELECT(
  [ref-list-column][key-column],
  (
    [max-column]
    = MAX(
      SELECT(
        [ref-list-column][max-column],
        ([_THISROW-1].[match-column] = [match-column])
      )
    )
  )
)

replacing ref-list-column (twice) with the name of the column containing the List or EnumList of Ref values from which you want the latest rows; key-column with the name of the key column in the table containing the referenced rows; max-column (twice) with the name of the column whose value identifies the desired maximum row; and match-column (twice) with the name of the column whose value identifies the rows of a maximum particular grouping. The result would be a list of key-column values for rows having the maximum max-column column value for each match-column column value.

For example, the following would give a list of Event ID column values for rows having the most-recent (maximum) Event Date column values for each Event Type column value::

SELECT(
  [Pre-Filtered Rows][Event ID],
  (
    [Event Date]
    = MAX(
      SELECT(
        [Pre-Filtered Rows][Event Date],
        ([_THISROW-1].[Event Type] = [Event Type])
      )
    )
  )
)
Top Labels in this Space