Expression for Valid If dropdown constraint w...

[Jason_Spicer]

Expression for Valid If dropdown constraint with conditional to show only rows without related items?

I have a table called Drywall and pullls data from two more tables (Property and Rooms) which are referenced to each other. In the drywall form, there is a column that pulls the Property ID from adding a new drywall row from the related drywalls inline from the property detail, and trying to only show Rooms for that property that already don’t have a related drywall row. Pretty much, only want each room to have only one related drywall row and any new drywall forms will not show in the room dropdown rooms that already have related drywall. Using valid if in the property ID to filter the room ID to rooms related to that property. Hope this all makes sense. I have been beating my head with different commands, variations, save and tests.

Thanks for any help

[tony1]

@Jason_Spicer Maybe something like this?

SELECT(YourTable[Column to Select], COUNT([Related Whatever Rows]) = 0)

YourTable[Column to select] <== the values in your dropdown

[Related Whatever Rows] <== the “related rows” virtual column in your referenced table

[tony1]

You may also be interested in this app: https://www.appsheet.com/samples/Only-allow-values-that-havent-been-chosen-in-dropdown?appGuidString...

[Jason_Spicer]

@tony It kinda works and so does the expression from the suggested app but it seems to break the preceding Valid if column before it that selects the Property and the Rooms column I am trying to constrain shows all the rooms without related items for all properties and not just the selected property above it. Close to it. Thanks for the steps forward

[Jason_Spicer]

@tony I got it to work by adding another condition to the expression and after many different variations, I was able to get it to work and reflect the rooms relative to what I pick for the property column before it.

I used your expression with the added condition instead of the expression from the suggested app. Your expression to me is cleaner without having to add unnecessary timestamp or other variation column to trigger it. Here it is if you are interested in checking it out.

=SELECT(Rooms[Room ID], AND((COUNT([Related Drywalls]) = 0), [Property ID] = [_THISROW].[Property ID]))

Thank you very much!