I have two lists: List A and List B. List A has Project Number column (Single line of text) and List B has Project Number column (Lookup Column) pulling data from List A. For List B, I have SharePoint integrated form and in that form for Project Number(Lookup Column), I want to show only Project Numbers that are in List A but there is no existing item of same Project number in List B (i.e. the Project numbers that are in List A but not in List B). I need help in filetring the project Number.
Should I create a collection for List B Project Number column so filtering doesn't impact the form performance. I am also trying to avoid delegation in Filtering the Project Column.
Filtering List A for the items that are not in List B
Thank you for your response @Inogic. My List A has more than 2500 items. For step, 2 and 3 (Filter( ListA, !(ProjectNumber in colListBProjectNumbers.ProjectNumber) )) can we use delegable function? 'In' doesn't pull all the data from the list. Could you please help with Delegation?
Filtering List A for the items that are not in List B
Hi,
To achieve this in your Canvas App, you can:
Create a collection for List B's Project Numbers to improve performance and avoid delegation issues.
Filter List A to show only Project Numbers not used in List B.
Step-by-Step Solution
1. Create a Collection for List B Project Numbers This will store all existing Project Numbers from List B. ClearCollect( colListBProjectNumbers, ShowColumns(ListB, "ProjectNumber") // Assuming "ProjectNumber" is the lookup column's name ) Run this on the app's start (OnStart) or when the form loads.
2. Filter List A for Available Project Numbers Use this filtered data as the Items property of the dropdown or combo box for the Project Number lookup. Filter( ListA, !(ProjectNumber in colListBProjectNumbers.ProjectNumber) ) This filters out any Project Numbers already present in List B. 3. Set the Dropdown/Combo Box Items If you're using a ComboBox for the Project Number in List B’s form, set its Items property to: Filter( ListA, !(ProjectNumber in colListBProjectNumbers.ProjectNumber) ) For a Dropdown: Distinct( Filter(ListA, !(ProjectNumber in colListBProjectNumbers.ProjectNumber)), ProjectNumber )
Why Use a Collection?
It improves performance by reducing delegation issues.
Collections are loaded once and used locally, making filtering faster.
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.