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