[stevensi1018]

I had a dropdown in a grid with an SQL Server query and it worked well but I tried to change it to a suggest control in my grid with the same query but it doesn,t work at all. No suggestion.

I tried to change from a database conenction to an array of options but I still have another error. It seems like the data is too big (I only select 1000 for example but I would like an array of 10-15 000 elements (see image for error).

Ideally, I want to use the database connection, not the array defined in a trigger before my Dynaform

[amosbatto]

When you use a "data variable" as your Data Source, you are creating a giant case variable which is too large to be saved in the APP_HISTORY table in the database. It is much better to use an SQL query because you avoid creating huge case variables, but suggest boxes are designed to start querying the moment you enter the first character, which means that the database query is returning a huge data set.

Turn on your web browser's debugger (press F12 on most browsers) and check whether you see an JavaScript errors under the "Console" tab.
If you don't see any errors, go to the "Network" tab and check what is being returned by the REST endpoint when you enter text into the suggest box:
POST project/{prj_uid}/process-variable/{var_name}/execute-query-suggest

Is the REST endpoint returning results from the database query?

[stevensi1018]

This is my query :

Code: Select all

SELECT *
FROM
(
SELECT
IH.OrderId,
IH.OrderId + ' - ' + CONVERT(nvarchar(1000),IH.x00198464_ProdDesc) AS Label
FROM InvoiHdr IH
INNER JOIN ProjInfo P ON P.InProjId=IH.x00198464_ExProjId
WHERE (P.Inactive IS NULL OR P.Inactive=0) AND IH.OrderId IS NOT NULL AND IH.x00198464_ProdDesc IS NOT NULL AND IH.x00198464_ProdDesc <> 'Facturation' AND IH.OrderId NOT LIKE '%-F'
UNION ALL
SELECT
IH.OrderId,
IH.OrderId + ' - ' + CONVERT(nvarchar(1000),IH.x00198464_ProdDesc) AS Label
FROM DYN_QualtechSolutions.dbo.InvoiHdr IH
INNER JOIN DYN_QualtechSolutions.dbo.ProjInfo P ON P.InProjId=IH.x00198464_ExProjId
WHERE (P.Inactive IS NULL OR P.Inactive=0) AND IH.OrderId IS NOT NULL AND IH.x00198464_ProdDesc IS NOT NULL AND IH.x00198464_ProdDesc <> 'Facturation' AND IH.OrderId NOT LIKE '%-F') Jobs
ORDER BY Jobs.OrderId

It works in SQL Server but there's a REST ERROR 400 in Processmaker:
{"error":{"code":400,"message":"Bad Request: Could not execute query [Native Error: Incorrect syntax near '-'.]"}}

Is there something not compatible with this type of control ?

[stevensi1018]

Any news on this ? Would really like to know if there's a way to fix this bug or what I'm doing wrong because it's preventing me from developping a new process so far.

Thanks

[amosbatto]

Propel or MySQL doesn't like something in your query.

First, turn on query logging in MySQL to find out if you are sending the same SELECT command to MySQL as you think you are sending. Propel could be transforming it in some way

Second, since we don't know which "-" is causing the problem, eliminate them until you can figure out what is the problem.
Eliminate the ' - ' and use CONCAT:

Code: Select all

SELECT * FROM (
SELECT
IH.OrderId,
CONCAT(IH.OrderId, ' - ', CONVERT(nvarchar(1000), IH.x00198464_ProdDesc)) AS Label
FROM InvoiHdr IH
INNER JOIN ProjInfo P ON P.InProjId=IH.x00198464_ExProjId
WHERE (P.Inactive IS NULL OR P.Inactive=0) AND IH.OrderId IS NOT NULL AND IH.x00198464_ProdDesc IS NOT NULL AND IH.x00198464_ProdDesc <> 'Facturation' AND IH.OrderId NOT LIKE '%-F'
UNION ALL
SELECT
IH.OrderId,
CONCAT(IH.OrderId, ' ', CONVERT(nvarchar(1000),IH.x00198464_ProdDesc)) AS Label
FROM DYN_QualtechSolutions.dbo.InvoiHdr IH
INNER JOIN DYN_QualtechSolutions.dbo.ProjInfo P ON P.InProjId=IH.x00198464_ExProjId
WHERE (P.Inactive IS NULL OR P.Inactive=0) AND IH.OrderId IS NOT NULL AND IH.x00198464_ProdDesc IS NOT NULL AND IH.x00198464_ProdDesc <> 'Facturation' AND IH.OrderId NOT LIKE '%-F') Jobs
ORDER BY Jobs.OrderId

Then, try changing '%-F' to '%F'.

Does the error message change when you change the query?

[stevensi1018]

It's in MS SQL, not in MySQL so I can't use CONCAT.

I already tried to remove the '-' and it didn't work unfortunately. There seems to be something else.

[amosbatto]

If MSSQL accepts the query, then the problem is probably Propel, so you have to figure out what Propel doesn't like.
Try eliminating parts of your query until you figure out what is causing the problem. For example, try eliminating the UNION to see if that is the problem.

[stevensi1018]

The UNION was the problem. I created a VIEW in MSSQL that contained the union and I used a SELECT * FROM VIEW in my suggest control and it worked.

[amosbatto]

We are using an ancient version of Propel that has a lots of problems like this. We are getting rid of it in version 4.