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!

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])
      )
    )
  )
)
3 Likes