Using a Create Script to control a Search Screen

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Using a Create Script to control a Search Screen

  • Comments 4
  • Likes

The screen below shows the default company search screen.  You can see that a user can search for "Inactive" companies.


A customer might have the need to stop users from including "Inactive" companies in their searches.

The companies should still be accessible in parts of the system - so for example if some one searches for an opportunity, they should still find that even though the company is 'Inactive'.  

So Inactive companies aren't like records that have been flagged as deleted.  For a discussion of Hard and Soft Deletion within Sage CRM, please see the article "Some Thoughts about the Hard Deletion of Records". 

If I did want Inactive company records to be completely suppressed then I could change the views to exclude them.  The companygrid list block is based on the view "vSearchListCompany".  This has the SQL

SELECT RTRIM(ISNULL(Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(Pers_LastName, '')) AS Pers_FullName, epd_pers.epd_EmailAddress as Pers_EmailAddress, epd_pers.epd_PhoneCountryCode as Pers_PhoneCountryCode, epd_pers.epd_PhoneAreaCode as Pers_PhoneAreaCode, epd_pers.epd_PhoneNumber as Pers_PhoneNumber, epd_pers.epd_PhoneFullNumber AS Pers_PhoneFullNumber, epd_pers.epd_FaxCountryCode as Pers_FaxCountryCode, epd_pers.epd_FaxAreaCode as Pers_FaxAreaCode, epd_pers.epd_FaxNumber as Pers_FaxNumber, epd_pers.epd_FaxFullNumber AS Pers_FaxFullNumber, epd_comp.epd_EmailAddress as Comp_EmailAddress, epd_comp.epd_PhoneCountryCode as Comp_PhoneCountryCode, epd_comp.epd_PhoneAreaCode as Comp_PhoneAreaCode, epd_comp.epd_PhoneNumber as Comp_PhoneNumber, epd_comp.epd_PhoneFullNumber AS Comp_PhoneFullNumber, epd_comp.epd_FaxCountryCode as Comp_FaxCountryCode, epd_comp.epd_FaxAreaCode as Comp_FaxAreaCode, epd_comp.epd_FaxNumber as Comp_FaxNumber, Company.*, Person.*,  Account.*, Address.*, (SELECT CASE WHEN CompanyCount = 0 or AccountCount = 0 THEN NULL ELSE 'Y' END FROM vSharedAddress WHERE vSharedAddress.AdLi_AddressId = Addr_AddressId) as Addr_IsSharedAddress FROM Company LEFT JOIN CRMEmailPhoneData epd_comp ON epd_comp.epd_EntityID = 5 AND epd_comp.epd_RecordID = Comp_CompanyID LEFT JOIN Person ON Comp_PrimaryPersonId = Pers_PersonId LEFT JOIN CRMEmailPhoneData epd_pers ON epd_pers.epd_EntityID = 13 AND epd_pers.epd_RecordID = Pers_PersonID LEFT JOIN Address ON Comp_PrimaryAddressId = Addr_AddressId LEFT JOIN Account ON Comp_PrimaryAccountId = Acc_AccountId WHERE Comp_Deleted IS NULL

So I could alter final predicate from

where comp_deleted is not null


where comp_deleted is not null and comp_status != 'Inactive';

But a change to the view would have potentially much larger implications across the entire system and would require much greater testing.

It is much easier for me to change the properties of a fields used in the search screen to limit the search in the way that I want.

Within the CompanySearchBox screen I can add the following code to the create script of the comp_status field.

DefaultValue= "Active";

Like this:


  • AllowBlank has been set to false to prevent all companies being searched for, which would include Inactive companies.
  • RemoveLookUp does exactly what is says and removes the option for Inactive companies
  • DefaultValue sets the value for which users should usually use for searches.

This produces the following screen

  • Hello Jeff, it works fine on Search Screen. I would like using defaultvalue and CreateScript in a filter screen (Company Library). I set a defaultvalue to the category field depending sector and current user team, so the default value should be different when i visit a company or another. The problem i encountered is : when defaultvalue has been set a first time, it doesn't refresh when you visit an other company. Have you ever met this case ?

  • Excellent! That is exactly what I needed!

    I also just realized that if we only change the search box to search active only, then they could still create new opportunities and type in the company name and select it from the drop-down menu.

  • Thanks Jeff.  This is most useful to the search screen!

  • Jeff,

    I have a currency field I would like to search on. I would like the search operator to be 'All' instead of 'Equal To'. So I don't want to set the default value of the currency field but the search operator. In my case this field is called "SearchNumericOperatorsprjt_value". How can set that to 'All' using a create script? Thanks!