I am having trouble filtering lookup column dropdown to show only those values which are not already present in current list.
I have list A of ChangeRequest# and another list B of Release where I am looking up ChangeRequest# and assigning them to different Releases.
For Example
List A
ChangeRequest#
Change 1
Change 2
Change 3
Change 4
Change 5
List B
Release# ChangeRequest# (lookup from list A)
Release 1 Change1
Release 1 Change2
Release 2 Change4
I created a secondary data connection to current list (list B) and created a filter on the dropdown field - ID of List A is not equal to ChangeRequest# of List B. Because the lookup column stores ID rather than value.
In the InfoPath form, ChangeRequest# lookup (dropdown) should not show values (changerequests) already assigned to a release in the list. So when creating a new item in list B, ChangeRequest# dropdown should only show values Change 3 and Change 5. But it is showing all. When I change condition to ID of List A is not equal to ChangeRequest# of List B, it works and only shows Change 1,2 and 4 in dropdown.
Please Help!!