ENUM Display Data based on another Column selection

(Steve Fuller) #1

I have an App that uses floor level data in a column called [Level].
This has the text values 00, 10, 20 etc for Ground, Level 1 = (10), Level 2 = (20) etc
I am forced to use this naming convention.
On each level there are rooms with numbers related to which floor level they are on.
[Room Number] is text based and looks like this 0123, 1324, 211A where the first digit is the floor level.
I’d like the ENUM to display only the room numbers for the floor level that the user selects as they add or edit records. For example, Level 20 will display all Room Numbers beginning with 2, eg 2012, 2034, 2055

I tried this code in a Suggested Values for the ENUM [Room Number] but was unsuccessful

SELECT(Level 01[Room Number], LEFT([Room Number],1) = LEFT([Level],1), FALSE)

Anyone help? Many thanks in advance :smile:

(Steve Coile) #2

How does it not work? Error, or just no result?

(Steve Fuller) #3

It displays results, but not those limited to the select criteria…

(Steve Coile) #4

Try putting your expression in Valid_If instead of Suggested values?

(Steve Fuller) #5

I placed the code in valid_if and this is the result where [level] = “40”

(Levent Kulacoglu) #6

You are trying to compare numbers with text values. Hence; TEXT(0) <> NUMBER(0) and that’s way the expression do not filter the records in the dropdown. Try with this:

SELECT(Level 01[Room Number], NUMBER(LEFT([Room Number],1)) = NUMBER(LEFT([Level],1)), FALSE)

(Steve Fuller) #7

Hi, thank you for taking the time to reply.Unfortunately, it gave the same result.
However, taking on board what you said I entered SELECT(Level 01[Room Number], (LEFT([Room Number],1) = 4), FALSE)
This does work! BUT I cannot get it to work dynamically :frowning:

(Levent Kulacoglu) #8

Is your [Room Number] and [Level] columns are TEXT columns both in the gSheet and the app’s column structure?

(Steve Fuller) #9

I have set the columns to Text.
However, I’ve just noticed that some data is stored as '40 and others as 40

I also added a column just to check gSheet formula and it worked

(Steve Fuller) #10

Hi. I’ve noticed that the Valid_If is restricting data input to the single character,which I suppose it is designed to do. BUT not restricting the display of data to the single character

(Levent Kulacoglu) #11

I may advise selecting the whole column and format it as Plain Text in the gSheet first. The evaluation in the gSheet and the AppSheet might be different. Provided your columns’ type are also set as Text in the column structure, to test it, create a VC and first test with:

LEFT([Room Number],1) = LEFT([Level],1) and then test the same with enclosing both sides with NUMBER() expression. I believe the results shall be different.

(Steve Fuller) #12

Both types of expression correctly identified matches and mismatches

40 4015 T
40 4212 T
50 4001 F
50 4021 F

(Levent Kulacoglu) #13

Please check this out. You are free to copy the Community Samples app from my portfolio > https://www.appsheet.com/portfolio/245151