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

Moderator: ArturoRobles

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
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"
User avatar
By 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 '""';
        return "\\" + s;

  return val;

$("#Filter").setOnchange( function(newVal, oldVal) {
    var v = _escape(newVal);
Make sure that "FilterEscaped" is a text field. If it is a hidden field, then dependent field queries won't work.
By 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 -

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.
By 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?
User avatar
By 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.
By 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.
By tford89
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
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
I think in this case you can use the select2 library.
Reference links:
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() {

function initSaleData(){
        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

sí, resolví esto creando un usuario […]

Error when importing any process

I still have the same error, need help please

Thanks very much.

thanks for your reply