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.