Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
New Member
Posts: 5
Registered: ‎05-05-2009

Searching contact names with accent marks

We have a large number of contacts outside the US, including France. Many of the French contact names contain accent marks, such as René. However, unless a user includes the accent mark, they will not find this name in SalesLogix.

 

Does anyone know of a customization/method that will return both Rene and René when the user searches for either variation?

 

Thanks,

Bruce

Highlighted
New Member
Posts: 5
Registered: ‎05-05-2009

Re: Searching contact names with accent marks

Now that I think about it, the real question is whether the search function in SalesLogix can be programmed to ignore accent marks.

 

Thanks, Bruce

Highlighted
Employee
Posts: 629
Registered: ‎04-24-2009

Re: Searching contact names with accent marks

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

 

 

Highlighted
New Member
Posts: 5
Registered: ‎05-05-2009

Re: Searching contact names with accent marks

Thanks for the thoughtful reply, Mike. Much appreciated.

Highlighted
Employee
Posts: 629
Registered: ‎04-24-2009

Re: Searching contact names with accent marks

Glad to help. With regard to the MSSQL SQL_Latin1_General_CP1_CI_AI collation, while we do not support [mixing] multiple collations, it would be possible to change your database collation to SQL_Latin1_General_CP1_CI_AI. This would give you what you want...but it would make all data entirely accent insensitive, with regard to query conditions, etc.

 

Thanks

 

Mike

Highlighted
New Member
Posts: 5
Registered: ‎05-05-2009

Re: Searching contact names with accent marks

I can't think of a situation where we currently need to query or build anything based on accent marks, so this seems a distinct possibility.

 

Thanks again,

Bruce

Highlighted
Bronze Super Contributor
Posts: 349
Registered: ‎03-24-2009

Re: Searching contact names with accent marks

We changed the collation to case insensitive for a customer and it works great.
Timmus Agersea
Black Moth CRM