The short answer is that an accent-insensitive lookup is currently not supported in either the LAN or Web.
There have been some that have attempted to accomplish an accent-insensitive lookup using an accent-insensitive collation for the fields they want to search against, but this is not supported, and will lead to SQL errors in groups and calculated fields.
In order to support an accent-insensitive query it's necessary to do one of two things as part of a customization:
[Options]
1. Execute a query using the native OLEDB provider (e.g. SQLNCLI.1):
MSSQL Example (Account Starts With):
SELECT ACCOUNT
FROM sysdba.ACCOUNT
WHERE (ACCOUNT LIKE 'A%' COLLATE SQL_Latin1_General_CP1_CI_AI)
This would execute a case-insensitive and accent-insensitive query to locate accounts beginning with: A, À, Á, Â, Ã, Ä, Å, a, à, á, â, ã, ä, etc.
This query will [not] execute through the SalesLogix OLEDB Provider.
2. Execute a query so that every possible accented character is added to the query:
Example (Account Starts With):
SELECT ACCOUNT
FROM sysdba.ACCOUNT
WHERE (ACCOUNT LIKE 'A%') OR
(ACCOUNT LIKE 'À%') OR
(ACCOUNT LIKE 'Á%') OR
(ACCOUNT LIKE 'Â%') OR
(ACCOUNT LIKE 'Ã%') OR
(ACCOUNT LIKE 'Ä%') OR
(ACCOUNT LIKE 'Å%')
[LAN]
1. You cannot fully override the Quick Find lookup for the main view, because the lookup button on the main view is not using function security. You can still override the Ctrl+Q (Lookup --> Quick Find) by creating a Global function handler for EditFindItem:
Function OnBeforeFunctionExecute_EditFindItem(functionNumber, functionName)
MsgBox("EditFindItem")
OnBeforeFunctionExecute_EditFindItem = True
End Function
This requires that you write your own lookup, using one of the options listed above.
2. You can override most of the regular lookups that are selected via the Lookup menu (e.g. Lookup --> Contacts --> etc.). This can be done using function security by overriding the function calls in a Global script.
Example of Global function handlers for the Contact lookups (run the SQL to build the VBScript):
SELECT 'Function OnBeforeFunctionExecute_' + NAME + '(functionNumber, functionName)' + CHAR(13) + CHAR(10) + ' MsgBox("' + NAME + '")' + CHAR(13) + CHAR(10)
+ ' OnBeforeFunctionExecute_' + NAME + ' = True' + CHAR(13) + CHAR(10) + 'End Function' + CHAR(13) + CHAR(10) AS GlobalLookupFunctionHandlers
FROM sysdba.SECFUNCTIONS
WHERE (PARENTNAME = 'LookupContact')
ORDER BY NAME
This requires that you write your own lookup, using one of the options listed above.
[Web]
1. It may be possible to customize sage-controls-lookup.js (regular lookups) and sage-controls-group-lookupmanager.js (group lookups) so that conditions are added to the WHERE clause based on option #2 above (i.e. adding all of the possible characters to the query). I haven't heard of anyone doing this, but it looks possible.
2. You could create a custom lookup control that implemented either option #1 or option #2.
Thanks
Mike
