How to make certain values/entries appear first in an enum dropdown

Getting down to brass tacks, there are ‘Cost Centers’ from which an employee chooses while making an expense, like materials, labor payment, transport, foods and hotels etc etc. The cost centers are kept in a separate table, and is referenced into the expense table. There are certain cost centers which are used very often, like purchases and labor payments.

What I want is to automatically assemble the cost center list according to the frequency of the values that are being used. The most used ones are to be on the top. That makes it easier for people to choose from.

Is there any Appsheet way of doing it, rather than manually assembling and sorting every one of them?

Solved Solved
1 3 190
1 ACCEPTED SOLUTION

Here’s again my brains wired up in the right direction after I posted the request:

  1. In the cost centers table, I made a virtual column that counts the number of times a cost center is used in the expense table, named [Count of cost centers]

COUNT(select(User expense submissions[Cost Center], [Cost Center]=[_THISROW].[Cost center Name]))

  1. In the user expense submissions table, I used orderby and filter to sort the key value according to the count.

orderby(filter(“Cost Centers”, (IN(any(Profile Slice[Role]), [Available to:]))), [Count of cost centers], TRUE)

The IN() statement is to filter the availability of cost centers according to the user role.

N.B. Potential solution seekers, TRUE is necessary in the expression of orderby if you want to sort in descending order. Orderby orders the list in ascending order by default.

  1. Then finally used a bulk dereference in the valid if of the cost center column to get the list in the desired order.

View solution in original post

3 REPLIES 3

Here’s again my brains wired up in the right direction after I posted the request:

  1. In the cost centers table, I made a virtual column that counts the number of times a cost center is used in the expense table, named [Count of cost centers]

COUNT(select(User expense submissions[Cost Center], [Cost Center]=[_THISROW].[Cost center Name]))

  1. In the user expense submissions table, I used orderby and filter to sort the key value according to the count.

orderby(filter(“Cost Centers”, (IN(any(Profile Slice[Role]), [Available to:]))), [Count of cost centers], TRUE)

The IN() statement is to filter the availability of cost centers according to the user role.

N.B. Potential solution seekers, TRUE is necessary in the expression of orderby if you want to sort in descending order. Orderby orders the list in ascending order by default.

  1. Then finally used a bulk dereference in the valid if of the cost center column to get the list in the desired order.

Happens to me all the time.

Wow, I’ve thought the saying was “brass tax”… mind:blown

Assuming you have a system-generated [Related User expense submissions] column, this can be simplified to:

COUNT( [Related User expense submissions] )

Wow, thanks.

Top Labels in this Space