Solved: Sorting a reference column in a form view by the m... - Page 2

cboteros · 05-20-2021 06:35 AM

Hello everyone,

I have a form view for adding a new record on the table TRANSACTIONS where there is a column TRANSACTIONS[transactionAccount] which is a reference of another table ACCOUNTS.

In the form view, the TRANSACTIONS[transactionAccount] column appears as a dropdown list.

I want to know if there is a way to display such list in a different order, by the “most used” accounts.

For instance, in the ACCOUNTS table, I was able to add a “read-only” virtual column [accountTransactionsCount] like this:

COUNT(
  SELECT(TRANSACTIONS[transactionKey],[transactionAccount]=[_THISROW].[accountId])
)

So this virtual column tells me the number of occurrences of the [accountId] in the TRANSACTIONS table ,and so, I can tell from that which ones are the accounts that are “most used”.

I just don’t know how I could use this ACCOUNTS[accountTransactionsCount] virtual column for sorting the dropdown list in the form view.

Would anyone know if this is possible and if so how? Thank you! Carlos

cboteros

Thank you Steve!

If anyone is having a similar issue than me, the problem was that in the second argument I was including the name of the table “ACCOUNTS” which seems that is not needed (and it even was causing problems).

So, in the end, this is what I have, and now it works as expected!

ORDERBY(
  ACCOUNTS[accountId],
  [accountTransactionsCount],
  TRUE
)

View solution in original post

Sorting a reference column in a form view by the most used record?