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.

[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.

[Paras_Sood]

Thank you for your prompt reply. The formula for machine id works wonderfully

However. Departments in the Items table is an enum list of base type ref (to satisfy the condition that an item can belong to many departments)

I tried:

FILTER("Items", IN([_THISROW].[Department ID],[Department ID]))

but it returns me with an error: 

Parameter 2 of function IN is of the wrong type

[TeeSee1]

Your formula seems correct.

Need to see the exact definitions of your columns.

[Paras_Sood]

This is the item ID from the daily log table:

This is from the Items table:

This is the department column in the Items table

[Paras_Sood]

This is the backend of the daily log table

This is the backend of the items table:

[TeeSee1 · Accepted solution]

FILTER("Items", IN([_THISROW].[Department ID],[Department]))

Should be just [Department], not [Department ID]

[Paras_Sood]

Hahahaha I guess it's time to go home now, should have spotted that mistake by myself. Thank you mate.