Dependent dropdowns and data validity

[tcanelli]

Working on a check in app for our activations. Have 3 smartsheets:

1. Personnel roster
2. Locker number list
3. Bunk number list

For the bunk number list we want to only show bunks based on power need, and preference of top/bottom bunk.

Is there a way to set this up so that the list of bunks filters first for power (y/n), then top/bottom (y/n), while at the same time only showing bunks not already assigned?

I’ve been able to use the data validity successfully for assigned/not assigned - but not three items like this. Any suggestions would be greatly appreciated. They’ve given me until Wednesday to come up with version1 , yay?!

[Mike_A]

@tcanelli

Maybe I got you use case wrong, but from what i read, why don’t you just use a slice to filter only “not assigned” and the sort by what ever columns you want?

[LeventK]

@tcanelli
Is that BunkID a ref column? If yes, it will be easy with ORDERBY(…)

ORDERBY(
	SELECT(
		TableName[Bunk ID],
		AND(
			[Power Accessible]="Yes",
			OR(
				[Bunk Position]="Top",
				[Bunk Position]="Bottom"
			),
			[Assigned]="No"
		)
	),
	[Power Accessible],TRUE,
	[Bunk Position],TRUE
)

OR you can simply use a FILTER(…) function in the Valid_if:

ORDERBY(
	FILTER(
		"TableName",
		AND(
			[Power Accessible]="Yes",
			OR(
				[Bunk Position]="Top",
				[Bunk Position]="Bottom"
			),
			[Assigned]="No"
		)
	),
	[Power Accessible],TRUE,
	[Bunk Position],TRUE
)

[Steve]

This expression is suitable for use in Valid_If to control the drop-down menu for the Bunk ID column:

SELECT(
  Bunk number list[Bunk ID],
  AND(
    NOT([Assigned?]),
    ([Power Accessible] = [_THISROW].[Power Accessible]),
    ([Bunk Position] = [_THISROW].[Bunk Position])
  )
)

1. SELECT(Bunk number list[Bunk ID], ...) gathers a list of Bunk ID column values from rows of the Bunk number list table that match the given criteria (...; see below).

2. AND(..., ..., ...) matches only those rows that match all of the given criteria (see below).

3. NOT([Assigned?]) limits the identified bunks to only those that aren’t currently assigned. You didn’t indicate how a bunk assignment is indicated, so I’ve assumed a column named Assigned? of type Yes/No. Adjust this expression as appropriate.

4. ([Power Accessible] = [_THISROW].[Power Accessible]) further limits the results to only include those rows with a Power Accessible column value that matches that in the form for the new row.

5. ([Bunk Position] = [_THISROW].[Bunk Position]) further limits the results to those rows with a Bunk Position value that matches the form’s.