List View Threshold - Related Items

[karl.lehnert]

Hi,

I have some large lists, and we have planned around these large lists with the use of indexed columns where query's need to be made but in the related items sections of our forms, despite telling forms designed to query the related items via an indexed column we get the list view threshold issue. I gather that the form is querying the entire list, and then filtering that view which creates a significant problem for us.

Being that the related items, are related to the current item, we cannot put that filter into the view used by forms designer. Is there any solution where we can build a query for that view of which the only results returned are based on the indexed columns we need to query them with?

We do not want to extend the list view threshold due to performance constraints.

Thanks,


Karl

[karl.lehnert]

Can anybody assist with this?

[Nikita Kurguzov]

Dear Karl,
What you first need to do, is to create view with filtration by indexed column which contains less than 5000 elements. Then choose this view in Forms Designer and filter Related Items by form's field.

[karl.lehnert]

We built a Jquery table framework which works inside of the forms designer forms by adding a html element like this to the form :-
<table
class="display compact order-entrires-table"
data-dt-list="Report Billing"
data-dt-column="JobID"
data-dt-queryString="ID"
Data-dt-view="Job Form"
style="width:100%; min-width: 985px"
data-dt-readonly="true"
data-dt-hidebuttons="true"
data-dt-hidesearch="true"
></table>

it retrieves the view and filters using a parameter in the query string to filter against a nominated lookup column and only queries the exact number of required items from the database.

We had half a dozen tables in a form, querying up to 30,000 items per table to filter based on the ID (had to keep increasing the threshold to keep everything going). Obviously this has a flow on effect to performance throughout the system and redesigning the entire system around this was not an option.

We use these tables everywhere now, it looks more polished than standard SP tables, and it has sped up our forms over 3 fold.

PM me if interested.