A strange problem

[Pratyay_Rakshit]

I have an app functioning as ERP to our construction firm.

It has become too big, so I’m now dividing it up according to various roles and functions.

A table is for users to input expenses.

It has a reference column to ‘Cost Centers’, which is a table for holding the nature of expenses. It has a name column, an ‘Available to:’ column (for hiding / showing the Cost center to various users based on role), a ‘Cost center Bengali’ column (for showing it in our native language).

It also has a ‘Count of cost centers’ column for counting the number of times a particular cost center has been used by users.

COUNT(select(User expense submissions[Cost Center (কিরম খরচ?)], [Cost Center (কিরম খরচ?)]=[_THISROW].[Cost center Bengali]))

What I want, and what has been implemented in the big app is to show the cost centers as dropdown in Bengali, sorted in descending according to count, shown/hidden according to the list in [Available to:].

For that I made a virtual column in the user expense submission table, namely [Cost Center Key] with formula :

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

And the column is set as type ‘List’, Element type Ref, and referenced table name as ‘Cost Centers’.

Then I used a bulk dereference in the valid if field in the ‘Cost Center’ column in user expense submissions as

[Cost center key][Cost center Bengali]

The column is set as type ‘enum’ with base type ‘text’.

This scenario has worked for me in the big app (which is the current workable app of our company).

But, in the current app which I’m working on, using exactly the above set of conditions, the column “cost Center” doesn’t show up in the user expense submission table. I removed the bulk derference from the valid if and used only [Cost Center Key] and then it showed up in all english terms.

STRANGELY ENOUGH, if I use nothing, no reference to cost center table, no valid if constraint, the cost center column does show up (showing up is inevitable) AND IT DISPLAYS ALL THE BENGALI COST CENTER NAMES, just not sorted according to count.

I’m going nuts over the problem. Maybe this app is pulling hints from the big app. I don’t know. Please suggest solutions.

[Steve · Accepted solution]

Pratyay_Rakshit:

But, in the current app which I’m working on, using exactly the above set of conditions, the column “cost Center” doesn’t show up in the user expense submission table. I removed the bulk derference from the valid if and used only [Cost Center Key] and then it showed up in all english terms.

STRANGELY ENOUGH, if I use nothing, no reference to cost center table, no valid if constraint, the cost center column does show up (showing up is inevitable) AND IT DISPLAYS ALL THE BENGALI COST CENTER NAMES, just not sorted according to count.

This suggests that [Cost center key][Cost center Bengali] is producing an empty list. When the Valid If expression produces an empty list, it’s indicating there are no valid values for the column. If there are no valid options for the user to choose from, AppSheet hides the column altogether from the form.

Make sure the Cost center key is of type Ref to the Cost Center table.

[Pratyay_Rakshit]

The ‘cost center key’ was a ‘list’ type with base type ref, with reference to the cost center table.

Changing the type from ‘list’ to ‘enumlist’ did the trick.