I'm using a dependent drop down for room and ...

[Tammi_Canelli]

I’m using a dependent drop down for room and cot assignment based on need (i.e. oxygen, set of rooms; memory, set of rooms, etc).

The drop down works great except I can’t figure out how to have the “assigned” cots removed the list of available.

I created a separate table view of “available” cots and the user will be able to click on an action button to mark it unavailable but even though it falls off that list it still shows in the drop down.

Any ideas how to remove from drop down?

I thought about a show if expression but was afraid that was for the entire column not the items within the column if that makes sense.

[Levent_KULACOGL]

TableName[ColumnName] - [AssignedValuesColumn]

[Tammi_Canelli]

@Levent_KULACOGLU

stupid question - where am I putting this expression?

In the data-columns tab, show-if?

[Steven_Coile]

@Tammi_Canelli A possible reason why everything disappeared when you changed the key away from the row number is that any references you had in your tables were still set to the row number. So AppSheet was trying to find the row number in a different column, but couldn’t because that different column has unique IDs, not row numbers!

So the challenge in changing keys is finding a way to keep all those stored references working even though you’re using a new column. One way is to update all the stored references to their new values. That’s very tricky, difficult, time-consuming, and will break your app during the process.

To move to a new KEY column, try this process:

1. Add the new column in your spreadsheet that will eventually become the KEY column, but do not mark it KEY yet. 2) Regenerate the table’s column structure in AppSheet to pick-up the new column. 3) Configure the new column in AppSheet as desired, e.g. with an App formula of =uniqueid(). SAVE these changes. 4) In the spreadsheet, fill in the cells of new column with their corresponding row numbers. 5) In AppSheet, change the KEY column from [_RowNumber] to this new column and SAVE the change.

That should complete your switch. If any rows were added in the time between steps (4) and (5), they may not have the right key value in the spreadsheet. Try your best to complete these two steps quickly, before a user has time to add any rows.

What this process does is sets up a new KEY column, but reuses existing key values to avoid breaking existing references that use them. We can do this because, in this case, there are no special requirements for the content of a key value other than it be unique. We are not required to provide key values 8 characters long composed of random letters and numbers (though that’s what uniqueid() provides), so providing a series of digits that happens to correspond to the row number at the time of the KEY column switch is perfectly acceptable. Note, too, that once the switch is complete, the fact that the number was once a row number becomes immaterial: if a row number changes, its KEY value should not change.

[Levent_KULACOGL]

@Tammi_Canelli to Valid_if

[Tammi_Canelli]

It didn’t like that expression - i’m guessing because I have a text col and a y/n column.

Is there an expression to work with what’s below or do I need to change the columns to numbers?

If I do that am I changing Assigned to just the same cot number?

[Steven_Coile]

@Tammi_Canelli The idea is to “subtract” the list of used assigned rooms for the list of all rooms: (all rooms) minus (assigned rooms) leaves (available rooms). To do this, you need an expression that produces a list of all rooms, and an expression that produces a list of assigned rooms.

If the name of your table of (all) rooms is Rooms, and the KEY column is [ID], the list of all rooms would be: Rooms[ID] (or select(Rooms[ID], true), or filter(Rooms, true))

If the [Assigned] column indicates (as a Y/N value) whether the room is currently assigned, the list of all assigned rooms can be expressed as: select(Rooms[ID], [Assigned]) (or filter(Rooms, [Assigned]))

We can then produce a list of available rooms by subtracting assigned rooms from all rooms: Rooms[ID] - select(Rooms[ID], [Assigned])

An alternative to subtracting assigned from all is to just directly generate the list of unassigned: select(Rooms[ID], not([Assigned]))

Put whatever expression from above that you use to list available rooms in the Valid_If field to produce a drop-down menu that includes only available rooms.

[Levent_KULACOGL]

Super descriptive +Steve Coile !

[Tammi_Canelli]

Thank you +Steve Coile!

I will try this.

[Tammi_Canelli]

+Steve Coile I did run into this issue:

when I made the ID column the key, even though it was set to uniqueid, i wasn’t able to see any of the rooms.

I went back to rownumber being key but that is not preferable.

Any suggestions?

[Steven_Coile]

@Tammi_Canelli Changing the KEY column is not usually something to be done lightly. You’re right that using the row number as key is not ideal, but might be best to stick with that for now while you work out these other issues.

[Tammi_Canelli]

I guess I don’t understand why making ID the key would essentially remove everything from view.

Unless have ID set to unique is only good for new row entries and since I have existing data I should establish a unique ID in my table first?