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