Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderator: amosbatto

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
By tford89
#824857
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.

Image

Image

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"
Image
User avatar
By amosbatto
#824867
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.
By tford89
#824880
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
Image

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.
User avatar
By amosbatto
#824882
What happens if you use a dropdown box instead of a suggest box? Does it still not work?
User avatar
By amosbatto
#824885
What happens if you have a dropdown box which uses the SQL:
EXEC KPS_Workflow_xxx '@#Filter'
By tford89
#824886
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?
User avatar
By amosbatto
#824888
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.
By tford89
#824892
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.
By tford89
#825098
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.
By tford89
#825106
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.
By chieunh
#825340
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

Hi Support, I think this is good. I too have fixe[…]

Hi, I'm facing the same error and when I run : […]

Routing Logic

Hi All I am doing a process, where i have 3 tasks […]

Hi @amosbatto, thanks a lot. at least the message[…]