Dynamically change SearchSQL for an SSA field client-side

Hints, Tips and Tricks

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

Dynamically change SearchSQL for an SSA field client-side

  • Comments 8
  • Likes

Recently I was asked if it is possible to restrict a Search Select Advanced field based on criteria that may change client-side.  The answer was no, it's not possible through standard customization.  However it's not impossible...

I want to be very upfront about this blog post: it is about an un-supported technique for hacking functionality into CRM using client-side javascript.  Before using any of the techniques described in this post please read Jack’s Hack FAQ to learn more about hacks and the risks involved in using them.

There are two functions that get invoked client-side when a user interacts with an SSA field, they are: window.NavUrl<<fieldname>> and window.ShowPopupOptionsWindow<<fieldname>>.  As an excercise in learning, go to a CRM screen that has an SSA field and view the source and find these functions. 

You will see that the SearchSQL is written in here.  It gets passed back to CRM when displaying the drop down list of values or when the user goes to the Find pop up screen. 

This means that we can change the SearchSQL client side... It's within our control.  The question is: how?

What we need to do is to re-write the function, replacing the SearchSQL value with what we want it to be.  We can then: replace the existing function with the re-written function, and voila! the SearchSQL has been effectively changed.  (Note server-side the SearchSQL has not been altered in the meta-data)

To do this we will be exploiting some very useful features of javascript:

  1. All functions are variables
  2. All functions can be turned into their string representation using toString().
  3. The eval method can be used to execute any string as if you had typed the javascript yourself.

So, first things first, we need to get the function into a variable so we can mess with it.  We could just say:

var s = window.NavUrlcase_primarypersonid.toString();

but we want to use this hack in multiple places and don't want to have to re-write it for each scenario.  So it'd be nicer if we could load the fieldname into a variable and then use that to reference the function.  So instead we will use:

var s = window['NavUrl' + strFieldname].toString();

So now our variable, s, has the following in it:

function NavUrlcase_primarypersonid(ID,url,fieldname){
var e='';if(ID){ if(document.EntryForm.case_primarypersonid.value == ID) { return false; } else e='&ID='+ID;}else{if(document.EntryForm.case_primarypersonid!=null && document.EntryForm.case_primarypersonid.value!=''){if(confirm('The field is already matched. Do you want to clear the results?')){Clearcase_primarypersonid();}return;}}var u='/jCRM62/eware.dll/Do?SID=111476224537364&Act=1275&Mode=6&CLk=&Key0=4&Key4=1&Key27=40&ViewField=,Pers_FullName,Pers_PhoneFullNumber,
&JumpReturnCol=case_primarypersonid&JumpIdField=Pers_PersonId&JumpNameField=Pers_FullName&SearchEntity=Person
&SearchTable=vSearchListPerson&SearchSql=&searchsqld=&SsDef=20&LinkedField=&TiedField=case_primarypersonid
&SearchText='+encodeURIComponent(document.EntryForm.case_primarypersonidTEXT.value)+e;document.getElementById('SEARCHFRAMEcase_primarypersonid').src=u;
}

Now all we need to do is replace the existing SearchSQL with our own.  Because the function is now a string this can be done very easily with regular expression:

s = s.replace(/&SearchSql=[^&]*/i, '&SearchSql=' + escape(searchSQL));

Note we use the built in escape function to put the searchSQL into the correct format.   This will handle all the appropriate delimiting for us.

Next we need to replace the existing function with our new function.  To do this we will use the eval function to make javascript interpret our string as an actual function definition.  But first we need to strip out the function name, as when you assign a function to a variable it should be an anonymous function. To strip the name out we use the replace method again:

s = s.replace('NavUrl' + strFieldname, '');

OK, so now we can use the eval statement like this:

eval('window[\'NavUrl\'+strFieldname]=' + s);

This is the equivalent to us typing out by hand the following:

window.NavUrlcase_primarypersonid = function (ID,url,fieldname){
var e='';if(ID){ if(document.EntryForm.case_primarypersonid.value == ID) { return false; } else e='&ID='+ID;}else{if(document.EntryForm.case_primarypersonid!=null && document.EntryForm.case_primarypersonid.value!=''){if(confirm('The field is already matched. Do you want to clear the results?')){Clearcase_primarypersonid();}return;}}var u='/jCRM62/eware.dll/Do?SID=111476224537364&Act=1275&Mode=6&CLk=&Key0=4&Key4=1&Key27=40&ViewField=,Pers_FullName,Pers_PhoneFullNumber,
&JumpReturnCol=case_primarypersonid&JumpIdField=Pers_PersonId&JumpNameField=Pers_FullName&SearchEntity=Person
&SearchTable=vSearchListPerson&SearchSql=pers_lastname%20LIKE%20%27s%25%27&searchsqld=&SsDef=20&LinkedField=&TiedField=case_primarypersonid
&SearchText='+encodeURIComponent(document.EntryForm.case_primarypersonidTEXT.value)+e;document.getElementById('SEARCHFRAMEcase_primarypersonid').src=u;
}

Now when the CRM UI goes to call the NavUrl function it will call ours, with the modified SearchSql, instead of the built-in one. 

Using all the techniques described above we can create a re-usable function like this:

function SetSSASearchSQL(strFieldname, searchSQL) {
    if (!window['NavUrl' + strFieldname]) return; // if the function does not exist we must not be in edit mode
    // replace SearchSql in the NavUrl function
    eval('window[\'NavUrl\'+strFieldname]='
        + window['NavUrl' + strFieldname].toString().replace(/&SearchSql=[^&]*/i, '&SearchSql='
        + escape(searchSQL)).replace('NavUrl' + strFieldname, '') + ';');
    // replace SearchSql in the ShowPopup function
    eval('window[\'ShowPopupOptionsWindow\'+strFieldname]='
        + window['ShowPopupOptionsWindow' + strFieldname].toString().replace(/&SearchSql=[^&]*/i, '&SearchSql='
        + escape(searchSQL)).replace('ShowPopupOptionsWindow' + strFieldname, '') + ';');
}

We can then test this function using code like this:

window.attachEvent('onload',
    function () {
        SetSSASearchSQL('case_primarypersonid', "pers_lastname LIKE 's%'");
    }
);

So there you have it.  Using this technique you can dynamically change SearchSql client side on Search Select Advanced fields.

Comments
  • Superb. This is just what I needed. Thanks Jack.

  • Hi Jack,

    Using SSA field when I select any one of the value, i need to populate any one of the field from that entity automatically.

    For Eg: In Case Summary page i use Company Name as a SSA field.

    One more field is Company Type in case page.

    When i select company name in SSA field i wants to populate the company type value from the company entity.

    Can you help me on this..?

    Thanks in Advance,

    Raja M

  • @Raja

    You can actually achieve this using an old built-in featured called linked fields.

    For an example take a look at the case_primarycompanyid field.  Here you can see that the linked field is set to "comp_slaid=case_slaid" this causes the case_slaid field to change to be whatever the selected company's comp_slaid is set to.  You should be able to put multiple associations in here by separating with a comma but I have just tested this and it is not working...  Also there is a bug that stops this from working with fields that are also used as view fields on the SSA field.  So hopefully this will work for you (provided you only need one linked field and don't want to link a field that is already used as a view field on the SSA).  

    If this does not meet your needs you could also refer to this blog post for a way you can achieve similar functionality using AJAX:  community.sagecrm.com/.../using-ajax-to-accessing-server-side-objects-client-side.aspx

    Regards,

    Jack

    Note I have logged two new support cases to get these bugs addressed:

    Sage case: 416-122469 -- eWare -- linked field doesn't work with view fields

    Sage case: 416-122467 -- eWare -- Can't have multiple linked fields

  • I know this is an old post but I am trying to use the linked fields feature to bring some fields from the company record to the case entity and as Jack pointed out I can't seem to have multiple linked fields as the linked field text box does not provide enought character spaces to type more than 1 linked field.  Has this method of linking fields changed?

  • Hi Chris,  I just checked those cases and they are still outstanding. In the meantime you can use the technique described here to achieve a similar result: community.sagecrm.com/.../using-ajax-to-accessing-server-side-objects-client-side.aspx

  • I've just been pointed to this post as it gives me exactly what I'm looking for...however I'm struggling to get past the starting post, let alone getting anything to work.  Am I correct in understandkng that I need to add this code into my custom asp page.....if so does it matter where?  

    I have, for a test, added the first bit of code "var s = window.NavUrlcase_primarypersonid.toString();",chnanging the fieldname to the correct one,  but when I run the page I get the error window is undefined....

  • Hi Steve,

    You really only need the function that is listed at the end of the article.  This needs to be client-side.  If you are using it from an ASP page then you will need to use something like:

    Response.Write("<script>function goes here</script>");

    Once it is client-side then you can use a client-side event to change the SearchSQL like the following:

    SomeElement.attachEvent('onchange',

       function () {

           SetSSASearchSQL('case_primarypersonid', "~SOME DYNAMIC SQL HERE~");

       }

    );

    Regards,

    Jack

  • Thanks Jack....