Dependent dropdown for enum list

[Paras_Sood]

Dependent dropdowns are doing my head in!

I have two use cases, I managed to implement one of them, but I am not sure how it works.

I have 4 tables

Items, Machines, Department and Daily Log with the following relationships

A Department can have many machines

A Machine can only belong to one department

An item can be made in many departments

In the daily log table, I want the users to be able to choose the department.

Based on the department chosen, I have the following two use cases:

1. Based on the department, I want users to be able to choose from a list of machines that belong to that department.

How I achieved this:

In the Department ID, I added a valid_if constraint: Machines[Department ID] and in machine ID, I added another valid_if constraint: Machines[Machine ID]

This works as expected but I am not sure why this works to be honest.

2. Based on the department, I want users to be able to choose from a list of items that belong to that department.

I tried the following valid_if constraint for item ID:

IN( 
	[_THIS], 
	SELECT(
		Items[Item ID], 
		IN(
			[_THISROW].[Department ID], 
			Items[Department]
		)
	)
)

However, this gives me a list of all of the products and does not narrow it down by department.

I believe that my solution to my use case #1 might be hindering use case#2

Do you have any suggestions to fix this? @TeeSee1 

[TeeSee1 · Accepted solution]

Dept ID: No valid-if required because you are already referencing the Dept and you do not want to constrain values here because this is the first field for the user to select

Machine ID:  FILTER("machines", [Dept ID] = [_THISROW].[Dept ID]

ITEM ID:  FILTER("items", [Dept ID] = [_THISROW].[Dept ID]

I am assuming that Machines and Items both have a ref col to dept.