This feature takes SharePoint Lookup Fields a big step forward by enabling filters to be applied within Flex lists.
To understand the power of this new feature review the Situations presented below. It will be of great assistance to your understanding the value of this feature if you are already familiar with SharePoint Lookup fields and column fields in general.
Note: This is a complex feature with a set of functions and configuration possibilities to meet specific objectives. It may not address every lookup filtering situation required. Please review these directions carefully to determine whether the current functionality is fitting for your requirements.
There are a few parameters to consider:
- An Advanced Flex Setting must be added that contains all the filters required for Flex lists.
- Each value in Flex Setting must have a corresponding Lookup field set up in Flex list.
- Lookup fields must have corresponding fields and filters assigned to the correct list.
- Syntax errors can create errors in forms.Ensure the syntax is correct when using this Setting.
Setting Name: FilteredLookupFields
Setting Value= ListName|FilteredListFieldName|Field items that match the criteria=Field Values (separated by comma)
Considerations
- Each row will have its own definition for List Name, Lookup field Name, and Value filtering items.
- Each row starts with the List Name that will contain the Lookup field.
- The Lookup field is added to the List that you want to filter the Lookup field from.
- The Lookup field must then be added to the New/Edit form.
- There can be more than one filter value applied to each situation, separate them with a comma.
Situation 1:
In my Opportunities list I want a field that shows Account records that are listed as Partners but I have more than one Partner type that I want to show in my Partner list.
In my Account records I have a field called Relationship Type and within that there are two values that I want to show in my Partner field for Opportunities. As an example, they are Integration and Consulting Partners.
- In the Opportunity list, add a Lookup field called “Partners” and point it to the Accounts list field called “Relationship Type”.
- Add the field called “Partner” field to the New / Edit forms of the Opportunity.Modify the values in the following setting as indicated below:
Value= Opportunities|Partner|Relationship Type= Consulting Partner, Relationship Type=Integration Partner
Situation 2:
You have a new list called Products and in this list, you want to reference two pieces of information from the Account record that will identify a new field called “Competitor”. The fields that you want to use are from the Account Record called Account Type= Competitor and Status=Active.
Now in my new list I will have a Lookup field called Customers which points to the Account list. In the Account list I have the two fields that I want to reference, Account Type and Status.
Add a Lookup field to the new list called Customers and connect it to the Account list.
Do not select any Column values because the setting will override and provide the necessary filters required.
Situation 3: Row Limit
You are using a Lookup field called Product Type within New/Edit forms that doesn’t need filtering, but the field is showing the value item choices slightly down the page.
Add a row to the Setting as shown below to change the behavior of the Lookup field’s value item display.
Value= List Name|Field Name|RowLimit=##
For ##, enter the number of items that should be displayed. (If your list of items could be 500, then add 500 as the ##.)
Situation 4:
You maintain a Yes/No field called “Parent(Y/N)” to designate whether an Account record is a Parent or not. This is useful for being able to easily pull a list of all Parent companies from the Account list.
Ensure the following highlighted value item is added to the “FilteredLookupField” setting.
Value: Accounts|Parent|Parent(Y/N)=Yes
Note: Page launch will be slower when a form is opened that contains a SharePoint lookup field with many items.