Dynamically setting View Query

[mac_xpert]

Hi I'm using your Related items Lookup on a large list of contacts. Each contact in the list has an associated project. Im trying to lookup the contacts associated with a project.

This works fine for lists of less than 5000 items but on large lists i get no results.

My thought is that if i could in someway dynamically set the Query used in the view of this lookup to match the project name in the form to the project name in the list it would dynamically create a view of less then 5000 items and the related items lookup would work.

I can view the code of the view in SPForms Designer under Extended>View:

<View>
<ViewFields>
<FieldRef Name="Edit" />
<FieldRef Name="Title1" />
<FieldRef Name="FirstName" />
<FieldRef Name="LastName" />
<FieldRef Name="JobTitle" />
<FieldRef Name="ol_Department" />
<FieldRef Name="CompanyLookup" />
<FieldRef Name="ClientOffice" />
<FieldRef Name="Created" />
<FieldRef Name="Modified" />
</ViewFields>
<ViewData />
<Query>
<Where>
<Eq>
<FieldRef Name="FirstName" />
<Value Type="Text">Matthew</Value>
</Eq>
</Where>
</Query>
<Aggregations Value="Off" />
<RowLimit Paged="TRUE">30</RowLimit>
<Mobile MobileItemLimit="3" />
<Toolbar Type="Standard" />
<XslLink Default="TRUE">main.xsl</XslLink>
<JSLink>clienttemplates.js</JSLink>
</View>


But is it possible to change the value of <Value Type="Text">Matthew</Value> in the query to match the content of a field in the form?


Thanks for your help.



Matt.

[Dmitry Kozlov]

Unfortunately, you cannot modify the View dynamically. So, i'd recommend you to create an index for the field by which you're going to filter the list or split it into multiple lists with less than 5000 items.

[Dan_C]

Is there still no way to do this? It would be great if we could use a field to add to the filter of the view query.

How does the "filter by field" work in Forms Designer related items?

Is it accurate that "Related Items" using view returning more than 5,000 items but with an indexed field in the Forms Designer "filter by field" will not encounter the 5,000 item threshold error (so long as the filtered result is less than 5k items)?

[Dmitry Kozlov]

Hi,

How does the "filter by field" work in Forms Designer related items?

Forms Designer creates a connection between web parts. Unfortunately, the connection cannot be set up if a view contains more than 5k items. So, first you need to define a static CAML that returns less than 5k items and then configure the connection. Say, you can filter items by date and include only those created within last month or year.

[Dan_C]

Perhaps someday you could programatically add in part of the "static" CAML query? That way if we have an indexed field relating to the ID of the current item we could choose that field and it would become part of the actual CAML query for the view?


In the meantime, I'm doing like you said and have a view with a -365 day offset... but that won't work if anyone wants to look at an item that's over a year old and expect to see the related items.

[Dmitry Kozlov]

You can filter the Related Items control by ID of the parent item using the ID parameter from query string. See the first reply in the thread:

http://sharepoint.stackexchange.com/que ... ery-string

[Dan_C]

Nice. So ... does this eliminate the 5000 limit so long as there aren't 5000 items sharing the shared id? Or does the selected "data source" still have some impact even though the "view" is customized?

[Dmitry Kozlov]

If the result view contains less than 5k items, everything will work fine. Please, note that the field that you use in the CAML-query must be indexed.