Defining a Valid if

@Steve Kindly assist me with the following.

I have table a read only look up table

Select Project Name (A) >> Phase (Platform) >> Stand Number (10) Capture20

Data will be captured in table Form Capture23

Select Project Name (A) again >> Phase (Platform) again >> Stand number ( Only 20 and 30) should show because 10 is already selected.Capture21

Select Same Project Name (A) >.> Different phase (Roofing) >> Stand number that should show is (10,20, 30) . I want stand numbers to show from 10,20,30 because its a different phase selected C1 .

You are looking for a dependent dropdown functionality. Please check this article how it’s built.

2 Likes

@Aleksi I have Used this expression:SELECT(Look up table[Stand Number],
AND(
[_THISROW].[Project Name] = [Project Name],
NOT(IN([Stand Number], form[Stand Number]))
)
)

what it does is showing the same values even when a different phase is selected.

Woud it be easier to use the dependent method? For the Phase column, you would need to use SourceTable[Phase] with the Valid_If. Then the Project name would be SourceTable[Project Name] and finally… Stand number would be SourceTable[Stand number].

@Aleksi This is how I want the above to do: Select Project Name(A) >> Phase(Platform) >> Stand Number(10)… Then you repeat these step the second time ( Select Project Name(A) >> Phase(Platform) >> Stand Number that will appear in the dropdown list will be (20 and 30) because ‘10’ is already selected. So the selected Stand numbers should not appear in the drop-down list. (This expression does exactly that: SELECT(Look up table[Stand Number],
AND(
[_THISROW].[Project Name] = [Project Name],
NOT(IN([Stand Number], form[Stand Number]))
)
))
What I want is when you select the same project name but different phase the stand numbers should contain all the values in the dropdown list: Select Project Name(A) >> Select a different Phase(Roofing) >> The Stand Number Drop-down list should be (10,20, 30) because it’s a different phase.

Then you should check the phase value as well.

SELECT(Look up table[Stand Number],
AND(
[_THISROW].[Phase] = [Phase],
[_THISROW].[Project Name] = [Project Name],
NOT(IN([Stand Number], form[Stand Number]))
)
))

You should also filter the form[Stand Number] in a same way. Now it will read all Projects and Phases.

1 Like

Thank you.

You’re welcome

@Aleksi Just a small problem I’m experiencing.
The above expression is working only when I use different stand number values for each Phase. I want to use the same stand number values(10,20,30) for different phase
e.g SELECT :Project A
SELECT :Phase (Platform)
SELECT :Stand Number ‘10’ (10.20,30). The selected stand number should not appear again in the dropdown list.
SELECT : Project A
Phase (Roofing) different phase
Stand Number(10,20,30) all the stand numbers should appear in the dropdown list because its a different phase. How do I do these?

You are probably looking something like…

SELECT(Look up table[Stand Number],
AND(
[_THISROW].[Phase] = [Phase],
[_THISROW].[Project Name] = [Project Name],
NOT(IN([Stand Number],
SELECT(form[Stand Number],
AND(
[_THISROW].[Phase]=[Phase],
[_THISROW].[Project Name]=[Project Name]
)))))))

1 Like

@Aleksi Thanks a lot. Really appreciate.

You’re welcome