Skip to main content

Hi,

 

I am working on a project where we need to identify and inspect underground workplaces in a mine. To do this we need to work through at least five levels of lookups trying to find a specific record out of more than 200 000.

 

Here is an example of the lookup part of the form:

 

I created a list for the lookup data with only three single-line text columns: ViewName, ParentID and ChildID. I then created indices on the ViewName and ParentID columns. The last piece of the puzzle was the views. I created a view for every list control:

 

Every view displayed the three columns, but were filtered on the value of the ViewName column.

 

Back to the form.

 

I used a choice control displayed as a drop down control as I only had two values to choose from. Here is the control settings for the Level lookup:

 

The five lookups all point to the same list. The Source view is the first "filter" as it breaks the list into five parts. The Parent ID is filtered by a Calculated Value control called fltShaft:

 

The other Calculated Value controls refer to the preceding Calculated Value control and List Lookup control e.g. fltShaft+'|'+parseLookup(lstLevel). To make the form neater, you can change the font color and background fill colors of the Calculated Value controls to match the background fill color of the form. Don't change the Visible property as the control won't recalculate.

 

I hope this makes life easier.

Great post Leon,  This is helpful.  Will definitely use this.

interesting idea.

however, (if I've got it correctly) you based all the relations on text values (so not on IDs like ordinary lookup does).

ie. once any of your labels change (eg. level '05' is renamed to  level 'E') you have to keep in mind you need to update all the dependencies (both parent(s) and child(ren)), which need not to be so easy taking into account 200k records...

any thoughts why did you find this approach better then using ordinary lookups?

The data used in this list is interfaced to a SQL table from SAP, which gets it from a different system where the original codes and descriptions are maintained, after a lot of data cleanup is done. A stored procedure is used to identify all the changes to be made to the SharePoint list. Only these changes will be loaded from the SQL table to the SharePoint List.

I originally used the text and codes in my design, but because I couldn't use the lookup() runtime function in my mobile form, I decided to use only descriptions in the parent key. Luckily the different labels making up the hierarchy will only change if a capturing error is rectified in the original system. This doesn't happen too often to seriously affect the maintenance of the records.

When I started the design, I was using SharePoint 2010 with a limit of 8 000 records per list. At one point I was looking at maintaining more than 200 lists, trying to keep the records below the limit.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings