Re: Using Ref in dependant drop down lists

Mike_VK

I am creating a facilities management app and am using three tables.

1) The Room Repair Request table (relevant columns: Repair ID (key), Building - Ref Type, pointed to Buildings Table, Room - Ref Type, pointed at Rooms table

2) The Building table (relevant columns: Building ID (key) and Building Name (Label)

3) The Rooms Table (relevant columns: Room ID (key) the Room_Space =Name or Number of Room (label) and Building-contains the building the room is in.

In my Room Repair Request form, I have created a column for Building (Type Ref, Ref Table "Buildings"),. This works great, the drop down shows the values from the referenced table

I want the Rooms column to show only the values associated with the Building selected. I have set this column up as (Type Ref, Ref Table "Rooms") and then used the

SELECT(
Rooms[ROOM_SPACE],
[BUILDING] = [_THISROW].[Building]
)

My dropdown list for Room keeps coming back blank. I'm at a total loss and have spent hours on this. I'm sure it is a simple fix but I have tried every permutation I can find online and still the same.

Any help would be greatly appreciated

Aurelien

Hi @Mike_VK

Let's say you have:

1) Table ROOM with columns:

- id

- label

- BUILDING, Type Ref, source table BUILDING

2) Table BUILDING, with columns:

- id

- label

==> you probably have a virtual column named [Related ROOMS]

This columns contains all the room id that are part of the current building.

3) Table ROOM REPAIR REQUEST, with columns

- id

- BUILDING, Type Ref, source table BUILDING

- ROOM, type Ref, source table ROOM

==> You need to add a valid_if condition with this expression:

[BUILDING].[Related ROOMs]

Said otherwise:

You need to change your expresssion with:

SELECT(
Rooms[ROOM_ID],
[BUILDING] = [_THISROW].[Building]
)

where [ROOM_ID] is the key-column of your table Rooms.

Mike_VK

Hi @Aurelien,

The drop down list for "Room" in the Room Repair Request remains blank. Are there any specific screenshots I could post that would help this process? Like the data source or any specific column settings?

Aurelien

Can you try my suggested expression instead? This would get us rid of specific columns names.

It would probably be (with additional "s")

[BUILDING].[Related ROOMSs]

Mike_VK

HI @Aurelien Sorry. I misinterpreted. I have tried your formula and still get the same result (no values in the Room Column).

I have tried to do a practice app with all of the non-relevant columns removed to just focus on getting this to work. Here is a screenshot of my tables and their column settings. There are 3 screenshots below. 1) description of tables and column settings in each and a shot of the "Building" dropdown and "Rooms" dropdown (once a building is selected)

In the end, I could just set up the "Rooms" column to show all the rooms across all the buildings. It's not ideal and its more the principle of the matter. I'm sure this is something that will come in very handy down the road if I can get it figured out.

Aurelien

Hi @Mike_VK

key-columns values are supposed to go in the Ref type columns.

Here is how it is supposed to be:

So, in the ROOMS table, column BUILDING:

You should NOT have "WESLEY", which is not in the key-column of BUILDING, but rather "612CF0e4", "2e4706fe", and so on.

For reference:

Drop-down on a Ref column - AppSheet Help

Drop-down from Valid_If - AppSheet Help