[tford89]

I am having issues using suggest box with PM 3.3.10, PHP 7.1.29, and SQLSRV using the N225 Stack.

Everything works correctly if i use "PM Database" but when i use an external MSSQL database, all filtering and variables stop working.





I am not receiving any error message in the browser console or in any logs from the server.

I am wondering if this is a bug for this version or if i am missing something within the setup.

I have validated filters are working with dropdown boxes without issue.

Example of working with the "PM Database"

[amosbatto]

If your SQL command includes LIMIT, then you will have problems with suggest fields.

MSSQL follows the SQL standard and treats values in double quotes as table and field names, whereas MySQL treats double quotes as strings and uses back ticks for table and field names.

Therefore, you need to change your query to:
EXEC KPS_Workflow_xxx '@#Filter'

The problem is if your users might put a value like O'reilly in the Filter field, because it won't be escaped.

To get around that problem, create another text field with the ID and variable named "FilterEscaped" and then use this SQL:
EXEC KPS_Workflow_xxx '@#FilterEscaped'

Then, add this JavaScript to your Dynaform:

Code: Select all

$("#FilterEscaped").hide(); //hide so the user doesn't see this text field

//function to escape strings for database queries 
function _escape(val) {
  val = val.replace(/[\0\n\r\b\t\\'"\x1a]/g, function (s) {
    switch (s) {
      case "\0":
        return "\\0";
      case "\n":
        return "\\n";
      case "\r":
        return "\\r";
      case "\b":
        return "\\b";
      case "\t":
        return "\\t";
      case "\x1a":
        return "\\Z";
      case "'":
        return "''";
      case '"':
        return '""';
      default:
        return "\\" + s;
    }
  });

  return val;
}

$("#Filter").setOnchange( function(newVal, oldVal) {
    var v = _escape(newVal);
    $("#FilterEscaped").setValue(v);
});

Make sure that "FilterEscaped" is a text field. If it is a hidden field, then dependent field queries won't work.

[tford89]

Unfortunately this didn't seem to help with any of the issues. The Filter variable passes in to the stored procedure as a blank parameter when using suggest. It seems to be an issue with the filter variable being set when the user enters a value.


From the wiki - https://wiki.processmaker.com/3.2/Suggest_Control


It looks like when using a source from MySQL, even if i do not pass a filter to the query, the box will filter but with MSSQL i am not seeing this functionality.

[amosbatto]

What happens if you use a dropdown box instead of a suggest box? Does it still not work?

[tford89]

Dropdown boxes seem to work without issue but are not using the @@Filter system variable.

[amosbatto]

What happens if you have a dropdown box which uses the SQL:
EXEC KPS_Workflow_xxx '@#Filter'

[tford89]

I am not fully understanding. My understanding per the wiki is Filter is a built in variable that is used for suggest boxes. Are you recommending using a suggest box and typing a filter in and see if it filters down the drop down box?

[amosbatto]

I assumed that you were using a dependent field query, which is why you spelled it as "Filter". If you want to use the built-in "filter" variable, then you have to spell it correctly in all lowercase.

[tford89]

I apologize, I am trying to use the system @@filter variable. I should have stated I have tried @@Filter, @@filter, @@FILTER.

When i had posted this, it was still uppercase for testing purposes and no luck with quotations on each case.

As stated in the original post, taking the filter variable out of the equation, when querying a MySQL table, the suggest box will filter the returned values automatically. With MSSQL, i am not seeing this functionality. This is what pushed me to the @filter variable but had no success with this too.

[tford89]

Amosbatto,
Seeing as the still seems to be an issue, what is the process for submitting a bug? I feel this is a bug as i can get everything to operate correctly for MySQL but once switched to MSSQL all functionality stops working as intended. I do not have this issue with other controls.

[amosbatto]

Fill out a bug report at:
http://bugs.processmaker.com

And pray that a client who has a support contract has the same issue as you, because very few bugs from the community are being fixed.

[tford89]

Thank you for the reply. I will submit this bug in the morning. We are looking to get a support contract soon with enterprise version but currently working in the community version.

I will also try to write a workaround for this and post it but not sure if its possible at this time.

[tford89]

When you type a value, all results are returned without filtering the results.

[chieunh]

I think in this case you can use the select2 library.
Reference links: https://select2.org/
Sample code:
1. Add libraries to external libs in dynaform
2. Add the following code to javascript in dynaform

Code: Select all

$(document).ready(function() {
	initSaleData();
});


function initSaleData(){
    $("#idYourControl").getControl().select2({
        ajax: {
            url: "your url",
            dataType: 'json',
            delay: 300,
            data: function(params) {                        
                return {
                    code: params.term.toUpperCase()
                };
            },
            processResults: function(data) {            
                //your code return data to drop down
            },
            cache: true
        },
        placeholder: 'Input data to search',
        minimumInputLength: 3,
        maximumInputLength: 20
    });
}

Currently I am using this method to replace the system's suggest control