ENUM Display Data based on another Column selection

AppSheet AppSheet Q&A
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

2

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

3

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

4

Try putting your expression in Valid_If instead of Suggested values?

5

1X_2c7e17a124973ec29da35c16cfeb31ee5f9f3734.png
1X_2c7e17a124973ec29da35c16cfeb31ee5f9f3734.png344×644 15.3 KB

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

6

Steve_Fuller:

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

@Steve_Fuller
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)

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

8

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

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

1X_f237d7bece669f63aa8c4d8acfbec3adc126c047.png
1X_f237d7bece669f63aa8c4d8acfbec3adc126c047.png390×378 13.6 KB

10

LeventK:

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

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

11

@Steve_Fuller
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.

12

Steve_Fuller:

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

Both types of expression correctly identified matches and mismatches

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

13

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