A warning to everyone - this no longer works after the below patches

• Sage CRM 2021 R2.5
• Sage CRM 2022 R2.4
• Sage CRM 2023 R2.2

I expect this will no longer work in 2024 too.

The required "SearchSQL" parameter has been removed as it can be abused to perform SQL injection attacks, so filtering dynamically client side is now impossible.

https://help.sagecrm.com/on_premise/en/2023R2/2023R2.2/Content/Patches/2023R2.2/Patch-ReadMe.htm
CRM-2133

This removal has/will break a number of customisations we have made for customers and will also affect current versions of Qnect 200 (.e.g. Account selection on quotes/orders).

Which version of Qnect? As we have customers on 2023 R2.2 and all is working OK with Qnect. 

All versions. If you create a quote/order, you will see that it will display all accounts in the accounts list.

I stand corrected, the highest we have gone is 2023 R2.1 

If things break if we go to v2024 then we can't upgrade customers. 

I have added my voice to it and raised it with Sage support

I believe we now have a solution.

The SearchSQL parameter can still be used, however if there is no "SearchSQL = 'abc'" in the CreateScript of the field, it will not be present in the URL.

So we have modified the code to add the parameter if it is missing.

We will be doing a bit more testing, then will get some proper patches out.

In the meantime, in WWWRoot/js/Custom/z99_99qnectsystem.js, replace the SetSSASearchSQL method with the following two methods:

    SetSSASearchSQL: function (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
        var inlineFuncString = window['NavUrl' + strFieldname].toString();
        eval('window[\'NavUrl\'+strFieldname]=' + this.ModifySearchSQLInJs(inlineFuncString, searchSQL).replace('NavUrl' + strFieldname, '') + ';');

        // replace SearchSql in the ShowPopup function
        var popupFuncString = window['ShowPopupOptionsWindow' + strFieldname].toString();
        eval('window[\'ShowPopupOptionsWindow\'+strFieldname]=' + this.ModifySearchSQLInJs(popupFuncString, searchSQL).replace('ShowPopupOptionsWindow' + strFieldname, '') + ';');
    },
    ModifySearchSQLInJs: function (js, searchSQL) {
        if (js.indexOf("&SearchSql=") < 0) {
            //SearchSQL is not in the URL, we need to add it.
            //Best way is to find another parameter we know exists and add the SearchSQL parameter to the end.
            var searchTableRegExp = /&SearchTable=([^&]*)/i;
            var searchTable = searchTableRegExp.exec(js)[1];
            return js.replace(/&SearchTable=[^&]*/i, '&SearchTable=' + searchTable + '&SearchSql=' + escape(searchSQL));
        }
        else {
            return js.replace(/&SearchSql=[^&]*/i, '&SearchSql=' + escape(searchSQL));
        }
    },

Hi John

I have done as instructed but my demo system still doesn't work correctly (cleared browser cache as well) 

You are missing the final few closing braces

I would be dangerous if I had my head screwed on correctly. 

I would be dangerous if I had my head screwed on correctly.