Visit Sage CRM Main Website  |  About Sage CRM
Visit Sage CRM Main Website  |  About Sage CRM

How Restrictor field works on a Search Select Advanced field

Hints, Tips and Tricks

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

How Restrictor field works on a Search Select Advanced field

Sage CRM Team
24 Apr 2009 4:08 PM
  • Comments 9
  • Likes

One of the neat features in CRM is the ability to restrict a Search Select Advanced (SSA) field by another field.  An example of this behavior can be seen in the Web Picker screens where the Person SSA is restricted to the people in the company that is selected in the Company SSA.

You can even create your own SSA fields and have one restrict the other.  (See Jeff's blog post for instructions on how to setup your own SSA here)

This may seem as a pretty straight forward feature and you might never stop to ask how it actually works until... it doesn't.

To begin to understand how it works behind the scenes let us consider this example:

You have 2 fields xxx_manufacturerid and xxx_modelid.  The xxx_modelid field has the restrictor set to be Manufacturer.

When you use the SSA field CRM does a select like the following:

SELECT * FROM Model WHERE mode_xxx = xxx_manufacturerid

The problem is how does CRM know what the mode_xxx field is?

What CRM does is to try and work it out based on the field names - so if your restrictor is Manufacturer - the look up family for this is 'Manufacturer' so it tries to find a field on the Model table that has 'Manufacturer' in the name.  E.g. mode_manufacturerid would match.  If it finds a field that matches then that field must either be an Integer type or a SSA with the same lookup family (Manufacturer)

This stuff may be a bit hard to get your head around at first but if you read over it a few times it should make sense.  If it still doesn't, post a comment!

Comments
  • John Wiles
    2 Aug 2009 11:54 PM

    I there a way to add a restrictor field through metadata.  I have a custom entity called service on a communications screen.  Service shows up a entity for the search select advanced field and it has a field called company (serv_companyid).  But Company does not show up in the restrictor field options for ssa field on the cummunications screen.

  • Sage CRM Team
    10 Aug 2009 2:55 PM

    It should just work...  Is the serv_companyid a Search Select Advanced field?

  • DarntonB
    13 Jan 2010 3:42 AM

    Is there any way of restricting the SSA that doesn't involve CRM guessing what I'm trying to do?

    I have a custom entity that has a submitted date and a collection of events that have start and end dates. I want the event SSA to only show those events that are current on the submitted date. Is this possible?

  • Sage CRM Team
    13 Jan 2010 3:02 PM

    Hi DarntonB,

    Yes you want to use the SearchSQL property of the SSA field.  You can set it globally on the field itself or specify it manually in a screen's create script.

    CRM uses the SearchSQL in the WHERE clause when selecting from the SSA's table/view.

    So you could do something like:

    SearchSQL = 'evnt_startdate>GETDATE() AND evnt_enddate < GETDATE()';

    Note that the GETDATE function is a MS SQL function, you would need to use SYSDATE in an Oracle back end (I think).

    Jeff has an article on SearchSQL here: dpp.sagecrm.com/.../using-searchsql-to-limit-data-returned-by-selection-lists.aspx

    Regards,

    Jack

  • DarntonB
    14 Jan 2010 4:14 AM

    Thanks Jack.

    Following on, is there any way to do this on the client? For example, instead of using GetDate(), which is fixed when the page is rendered, can it respond to changes in another field on the page, along the lines of

    Where Envt_StartDate < Appl_SubmittedDate And Evnt_EndDate > Appl_SubmittedDate

  • Sage CRM Team
    15 Jan 2010 3:25 PM

    This is not a feature provided by CRM, however it is possible using undocumented/unsupported methods.  I will be posting a blog article on how to do this in the next day or so.

  • DarntonB
    25 Jan 2010 6:03 AM

    I look forward to learning the secret!

  • Sage CRM Team
    25 Jan 2010 5:11 PM

    Here is the new blog post on how to change SSA SearchSql client-side:

     https://community.sagecrm.com/blogs/hints_tips_and_tricks/archive/2010/01/25/dynamically-change-searchsql-for-an-ssa-field-client-side.aspx

  • Jonathan
    20 Jun 2014 12:50 PM

    Hello,

    I have a problem, because i have a table with and id name dlis_departamentosociedadId, and the interested fielt dlis_sociedadId. The problem is that i want fo filtrate by a field pers_sociedadId, and the SSA restrict with dlis_departamentosociedadId = pers_sociedadId, and not with dlis_sociedadId = pers_sociedadId.

    I changed the view used in the SSA, changing the order of the fiuelds, but the CRM choose the first field that match with que rules explained by Jack.

    Any idea without using the clientside hack?