Question:
Why doesn‘t AutoComplete work with one custom lookup?
How does AutoComplete know what fields to search?
Answer:
There are several reasons why a custom lookup might not exhibit expected AutoComplete behavior. For example, AutoComplete queries must be fast or they are pointless.This article is extremely technical in nature and refers to SQL Server Management Studio.
First an overview:
- Lookups, even custom ones, are stored in xsfLookup and xsfLookupField.
- AutoCompletes choices are provided by stored procedures named pacq_lookupname.
- Nearly all the pacq_ procedures are dynamically generated from the lookup definition by pu_AutoCompleteQueryGenerator. For the distributed lookups, 94% of the AutoCompletes are dynamically generated.
- A TSQL comment (or lack thereof) determines whether or not pu_AutoCompleteQueryGenerator will overwrite the pacq_ procedure if it already exists. Compare pacq_SubcontractorList with pacq_DocStatus.
- When necessary, you can tweak the default AutoComplete query to provide a faster, leaner, or even broader selection.
To troubleshoot your AutoComplete, answer these questions. For the sake of these examples, assume the custom lookup name is PizzaVendors.
- Does pacq_PizzaVendors exist?
- Run exec pu_AutoCompleteQueryGenerator ‘PizzaVendors‘; Does it generate an error?
- Run exec dbo.pacq_SubcontractorList @pv=‘a‘; Does this generate an error or a result set?
- How long does the query take?
- Does it include the results you expect?
If the query runs, but takes too long or does not have the result you expect given the ‘pv‘ input, you may need to provide a custom pacq_ procedure.
Additional Comments:
The pacq_ procedures have three major components
- The query parameters
- The field list
- the selection criteria (technically called query predicates in case you care)
The query parameters include
- @pProject – mandatory, but may be null if project is not known from context
- @pUID – mandatory, the key of the user in case your query wants to restrict choices by permission
- @pD1, @pD2, @pD3, @pD4 – should be provided, the significant of each is determined by the lookup definition. For example, sometimes one of these is used to specify the Doc type. Unused parameters are left null
- @pV – the partial value for which AutoComplete suggestions are being offered
The field list, when dynamically generated, is based upon the lookup result. This likely provides more columns than are actually necessary. The first column must be the ‘key value‘ — the result of the lookup or when an AutoComplete suggestion is chosen. The second column is the primary description.
The selection criteria are what it is all about. When dynamically generated, the query generously searches for any record where the presented value is found anyplace within the filterable string fields. You can improve performance by forcing the match to be at the start of the fields (increases odds of an index being used). You might also remove some fields from the selection criteria. For example, the automatically generated pacq_DocStatus includes ‘code‘ in the presented value comparison. While this does no harm, it also may not add any value.
KBA-01540; Last updated: October 27, 2016 at 11:49 am