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

[cboteros]

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

[Marc_Dillon]

help.appsheet.com

ORDERBY()

Sort row references

[cboteros]

Thank you @Marc_Dillon ,

I’m just not sure how to use this ORDERBY() function so the drop-down list in the form view will be displayed in the order I’m expecting.

Could you please elaborate a bit more on how to use this fiction to achieve the result that I’m trying to get with the form view?

[Marc_Dillon]

help.appsheet.com

Dropdown from Valid_If

How to create a dropdown that depends on a particular list of values.

[cboteros]

Hello @Marc_Dillon I tried to follow the advice on the page you had linked, but it didn’t seem to have worked.

This is what I did:

On the TRANSACTIONS table, I went to edit the [transactionAccount] column which is a reference of the ACCOUNTS table (which has the [accountId] column).

Then, I added this formula under the “Data Validity → Valid If” field of the TRANSACTIONS[transactionAccount] column:

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

I saved the changes, but when I open the form view to add a new transaction, and I click on the Accounts field, the dropdown still displays in the order the accounts are in the source spreadsheet (not even in alphabetical order).

What would I be missing?

[cboteros]

To better show the issue that I’m having, I had created a virtual column which I called ACCOUNTS[accountLongName] and that I’m using as the “Label” which has this formula:

[_RowNumber]&" - "&[accountName]&" ("&[accountTransactionsCount]&")"

So, as you can see from the screenshot below, the dropdownlist in the form view still is ordered by the [_RowNumber] instead of the [accountTransactionsCount]

[cboteros]

My case is quite similar to @Diogenes_ASBL_VZW case in here:

Hi appsheet community, I've an ORDERBY() cha... Questions

Hi appsheet community, I’ve an ORDERBY() challenge: I want to organize my dropdown column [places] according to their popularity. For this purpose, in the table next to the column [places] I created a virtual column [popularity] with a count formula that adds up the number of times that space has been selected. For the dropdown I wanted to use this formula: ORDERBY (Brussels [ID], [places]) => dropdown is ordered alphabetically in places. Is there a way I can arrange [places] via [populari…

I’ve tried using the ORDERBY as @Grant_Stead had suggested in there, in this way:

On the column TRANSACTIONS[transactionAccount], which is of type “Ref” to the ACCOUNTS table, I’ve added this formula under the “Data Validity → Valid If” formula field:

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

But I’m still not getting the result that I’m expecting.

I’m sure there might be something simple that I’m missing in the whole process, but I’m not able to figure out what it is…

[Steve]

Your ORDERBY() expression is wrong. Please review the docs and correct the second argument, ACCOUNTS[accountTransactionsCount].

[cboteros · Accepted solution]

Marc_Dillon:

ORDERBY()

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
)

[Steve]

Well done!