ENUM Display Data based on another Column selection

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

0 12 1,708
12 REPLIES 12

Steve
Participant V

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

It displays results, but not those limited to the select criteriaโ€ฆ

Try putting your expression in Valid_If instead of Suggested values?

1X_2c7e17a124973ec29da35c16cfeb31ee5f9f3734.png

I placed the code in valid_if and this is the result where [level] = โ€œ40โ€

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

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

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

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

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

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

Both types of expression correctly identified matches and mismatches

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

LeventK
Participant V

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

Top Labels in this Space