Restricting SageCRM Quick find results to not show comp_type of suppliers

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
  • 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!

  • 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

  • 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!

Reply
  • 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!

Children
No Data