How to configure Quick Find to search for custom fields

2 minute read time.

We received a case where a customer wanted to configure Quick Find to return the result of a search on a custom field that was added to the Company entity.

When a user searches through the entities it can only display a maximum of 30 results based on which entities are indexed. This can be done from the screen:

Administration -> System -> Quick Find

The customer added a field/column to this entity/table that contains data related to an integrated Sage 300 database. The data in this column is in text format and contains 4 to 7 characters.

The problem they were having was that all the columns in the company entity were not marked as ‘Exclude from Quickfind’. So Quick Find was searching through all the columns but could only display up to 30 results. QuickFind might have found what they were looking for but couldn’t display it.

For this example, I have added a column called Comp_Code to the company entity. I set all the values to reflect the company id. Each field will have a ‘CO’ at the start followed by the company ID.

What the customer was looking for was a way to input the comp_code in Quick Find and return the corresponding company name.

So, When I search for ‘CO18’ in Quick Find I don’t get ‘Magnetic Software Ltd.‘.

So, I found a way for this to work how the customer wanted.

I used the column in the Custom_Edits table called ‘Colp_ExcludeFromIndexing’. If these fields in this column are set to 1 then they are excluded from the Quick Find search. Instead of doing this through the UI by changing each individual column, I did this through the SQL Management Studio to speed up the process.

Firstly, I found all the records in the Custom_Edits table that relate to the Company entity and took note of the id for this table:

select Colp_ColPropsId from Custom_edits where ColP_Entity = 'Company'

With this I then set all these fields to 1:

Update custom_edits set Colp_ExcludeFromIndexing = '1' where ColP_ColPropsId in (select Colp_ColPropsId from Custom_edits where ColP_Entity = 'Company')

Then I set the Comp_name and Comp_code entries to have Colp_ExcludeFromIndexing set to null (To include these two columns in Quick Find search)

For this I had to find the Colp_ColPropsID for the Comp_name and Comp_Code entry in the Custom_Edits table. Comp_name is usually 77:

And my new field (Comp_Code)  has the id of 12094:

With this I then set these fields back to Null. So, only these two field are being searched by Quick Find.

Update custom_edits set Colp_ExcludeFromIndexing = null where ColP_ColPropsId in (12094, 77)

Then to get this to work I did a meta-data refresh and I rebuild the QuickFind service using the replication steps for this article: https://help.sagecrm.com/on_premise/en/2020R1/Administration/Content/Troubleshooting/TS_RecreatingQuickFindIndex.htm

To restart the Quick Find service won’t do. You have to Rebuild the entire Quick Find directory.

I also did an IISRESET for good measure.

Then when the Quick Find service restarted I could then search for ‘CO18’ and it would return the corresponding company name.

Like for ‘CO19’ and so on.

Parents
  • Thanks Sean.

    How about the other way around i.e. How to configure Quick Find to restrict results e.g. a Customer wants to exclude Companies whose Status is Inactive?

    Would adjustment of C:\Program Files (x86)\Sage\CRM\Services\QuickFind\Templates\instance\conf\data-config.xml be the way to go e.g. by adding "Comp_Status <> 'Inactive'" to the WHERE clause of the query, deltaImportQuery, deltaQuery and deletedPkQuery SQL statements?

Comment
  • Thanks Sean.

    How about the other way around i.e. How to configure Quick Find to restrict results e.g. a Customer wants to exclude Companies whose Status is Inactive?

    Would adjustment of C:\Program Files (x86)\Sage\CRM\Services\QuickFind\Templates\instance\conf\data-config.xml be the way to go e.g. by adding "Comp_Status <> 'Inactive'" to the WHERE clause of the query, deltaImportQuery, deltaQuery and deletedPkQuery SQL statements?

Children