x Want to participate? Join the group to interact
  • State Verified Answer
  • +1 person also asked this people also asked this
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 77 subscribers
  • Views 957 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
Nick@RBSolutions

Restricting SageCRM Quick find results to not show comp_type of suppliers

SOLVED
Posted By

Do you know if there is away on Sage 200 CRM (2018 R2) or SageCRM to restrict the result set that is returned by the  Quickfind function in SageCRM?

We need to remove "supplier" company types from the returned results. This is complete easily enough on standard Find company / Person screens by updating the SQL views but seem to be a challenge for the quikfind feature

The integrated S200 CRM integration being used at the moment and they need to be able to hide all supplier records in SageCRM from users but these synced over automatically. If we mark the supplier records as deleted in SageCRM this causes knock on effects with the standard S200 sync failing because it cannot find these records.

We have been through the suggestions in the below related link but have not been able to resolve this

https://www.sagecity.com/sage-global-solutions/sage-crm/f/sage-crm-general-discussion/141889/configure-quick-find

Below is a snippet of company entity section from the data-config.XML  in the following location: C:\Program Files (x86)\Sage\CRM\Services\QuickFind\Templates\instance\conf on the CRM server instance where we have tried to modfiy this with additonal logic and then deleting and rebuilding the quickfind folders

       <entity name="Company" pk="Comp_CompanyId" transformer="com.sage.crm.solr.transformers.SolrDataTransformer, ClobTransformer" query="SELECT Comp_CompanyId,LTRIM(RTRIM(Comp_Name)) AS index_descriptor,Comp_secterr,comp_ChannelId,Comp_PrimaryUserId,Comp_createdBy,Comp_Website,Comp_Name,Comp_LibraryDir FROM Company WHERE Comp_deleted IS NULL and COMP_TYPE !='supplier'" deltaImportQuery="SELECT Comp_CompanyId,LTRIM(RTRIM(Comp_Name)) AS index_descriptor,Comp_secterr,comp_ChannelId,Comp_PrimaryUserId,Comp_createdBy,Comp_Website,Comp_Name,Comp_LibraryDir FROM Company WHERE Comp_CompanyId='${dih.delta.Comp_CompanyId}'" deltaQuery="SELECT Comp_CompanyId FROM Company WHERE Comp_updateddate &gt;= DATEADD(MINUTE, -5, convert(datetime,'${dih.last_index_time}')) AND Comp_deleted IS NULL AND COMP_TYPE !='supplier'" deletedPkQuery="SELECT 'Company-' + convert(varchar, Comp_CompanyId) AS Comp_CompanyId FROM Company WHERE Comp_updateddate &gt;= DATEADD(MINUTE, -5, convert(datetime,'${dih.last_index_time}')) AND Comp_deleted IS NOT NULL AND COMP_TYPE ='supplier'">

 Has anyone out there had any luck modify the results from the quick find feature and is this possible?

Thanks

Nick           

Parents
  • 0
    SUGGESTED

    It looks like your view addresses the restriction. However, it does not look like the syntax is correct.  If this is an SQL query then " AND COMP_TYPE !='supplier' " should be AND COMP_TYPE <> 'supplier'.  Replace != with <>. 

    != is javascript syntax and <> is SQL syntax.

    It can be a bit tricky to know when to use which.

    If that is not the issues, I would check and see if your COMP_TYPE field is a multi select field.  If so, 'supplier' should be ',supplier,'.

    Hope that helps!

  • 0 in reply to Michele Gaw

    Hi Michele,

    Many thanks for the suggestion we have also tested this functionality with the <>'supplier in the SQL statements but to no avail. When the data-config.xml is recreated in the "C:\Program Files (x86)\Sage\CRM\CRM\QuickFind\instance\conf " location after deleting the quick find directory and restarting the service we are not seeing the update to the SQL statement which is entered in the C:\Program Files (x86)\Sage\CRM\Services\QuickFind\Templates\instance\conf.

    If we add the change in manually to data-config in the location  "C:\Program Files (x86)\Sage\CRM\CRM\QuickFind\instance\conf " this does not seem to have any effect either..

    Thanks

    Nick

Reply
  • 0 in reply to Michele Gaw

    Hi Michele,

    Many thanks for the suggestion we have also tested this functionality with the <>'supplier in the SQL statements but to no avail. When the data-config.xml is recreated in the "C:\Program Files (x86)\Sage\CRM\CRM\QuickFind\instance\conf " location after deleting the quick find directory and restarting the service we are not seeing the update to the SQL statement which is entered in the C:\Program Files (x86)\Sage\CRM\Services\QuickFind\Templates\instance\conf.

    If we add the change in manually to data-config in the location  "C:\Program Files (x86)\Sage\CRM\CRM\QuickFind\instance\conf " this does not seem to have any effect either..

    Thanks

    Nick

Children
  • +1 in reply to Nick@RBSolutions
    verified answer

    Is it possible that the field comp_type is a multi select field?  If that is the case, the data in the field would need to look like this ,supplier, rather than supplier.  Your where clause would need to be <> ',supplier,' rather than <>'supplier'

    Also, I would turn on full SQL logging so that you can see what the system is actually running.  I would recommend doing this when users are not in the system.  Otherwise you might have a lot of data to look at as full logging picks up every SQL statement that runs.

    Hope this helps!

*Community Hub is the new name for Sage City