Dropdown need to avoid to show existing records

[ursNani]

Hi 

I am pulling data from "project WBS Element" list in dropdown and saving into "Projects forcaster" list .

my dropdown need to show only not saved records in forcaster list only.

here is the my dropdown item property code : Filter('Project WBS Elements',Status.Value="Active" ).'Active WBS Elements'

lets assume i have records in project WBS Element , AAAA , BBBB,CCCC,DDDD,EEEE

my dropdown need to show all of them. if i have forcaster list AAAA item, then dropdown need to show only BBBB,CCCC,DDDD,EEE records only .

please give me code . here is the my dropdown item property :  Filter('Project WBS Elements',Status.Value="Active" ).'Active WBS Elements'

Thanks 

[BCBuizer · Accepted solution]

Hi @ursNani ,

A quick, but non-delegable, way to do this is using the in operator:

Filter(
	'Project WBS Elements',
	Status.Value="Active",
	Not(ID in 'Projects forcaster'.ID)
).'Active WBS Elements'

[v-qiaqi-msft · Accepted solution]

Hi @ursNani,

Do you want to exclude records that exists in "Projects forcaster" list from dropdown items?

Could you please tell me that if both of "project WBS Element" list and "Projects forcaster" list share the same element like AAAA,BBBB?

@BCBuizer provides a direction that both of two lists share the same ID, so you could compare ID in them to exclude existing records in "Projects forcaster" list. However, if you just have the same column storing the  AAAA,BBBB,..., please modify your formula as below:

Filter('Project WBS Elements',Status.Value="Active",!(ColumnYouSavingAAAA in 'Projects forcaster'. ColumnYouSavingAAAA)).'Active WBS Elements'

This is just a different format, the key to this issue is the "Column1 in AnotherList.Column2", Column1 and Column2 are different columns sharing the same items, just like the AAAA, BBBB in both "project WBS Element" list and "Projects forcaster" list.