[fibo2358]

Hello,

I have three fields in a Dynaform: two dropdowns (field A, field B) and field C.
The field C has a sql query defined:
SELECT CVALUE
FROM PMT_DTABLE
WHERE AVALUE= @@fieldA AND BVALUE= @@fieldB

When I select a menu item from the field A dropdown, the query related to the field C is sent to the database despite the field B menu item has not been chosen.
Is it possible to avoid executing this query? I would like to execute the query only if both menu items (field A and field B) are set - the AVALUE and BVALUE columns in the PMT_DTABLE do not accept null values.

Thank you for your advice.
fibo2358

[amosbatto]

Use this query:
SELECT CVALUE
FROM PMT_DTABLE
WHERE @@fieldA<>'' AND @@fieldB<>'' AND AVALUE= @@fieldA AND BVALUE= @@fieldB

[fibo2358]

Hello Amos,

I have used your query, however the number of query executions doesn't seem to decrease.
Would it be possible not to execute the query at all?

Best Regards,
fibo2358

[amosbatto]

You can create a stored procedure in MySQL to handle this.

Another option is to use a query like this, but it only returns 1 field:

Code: Select all

SELECT CASE WHEN ( @@fieldA='' OR @@fieldB='' ) THEN ( SELECT '' ) ELSE 
( SELECT CVALUE FROM PMT_DTABLE WHERE AVALUE= @@fieldA AND BVALUE= @@fieldB ) END

If you need two fields, you can use this SQL which doubles the number of queries to the database:

Code: Select all

SELECT CASE WHEN ( @@fieldA='' OR @@fieldB='' ) THEN ( SELECT '' ) ELSE 
( SELECT CVALUE FROM PMT_DTABLE WHERE AVALUE= @@fieldA AND BVALUE= @@fieldB ) END, 
CASE WHEN ( @@fieldA='' OR @@fieldB='' ) THEN ( SELECT '' ) ELSE 
( SELECT CVALUE FROM PMT_DTABLE WHERE AVALUE= @@fieldA AND BVALUE= @@fieldB ) END

Why are you worried about the number of database queries? You are probably talking about a difference of milliseconds.

[amosbatto]

You can create the following stored procedure (from the command line of your server):

Code: Select all

mysql -u root -p
use wf_workflow;
DELIMITER $$;
CREATE PROCEDURE GET_FIELDS (IN FIELDA VARCHAR(64), IN FIELDB VARCHAR(64)) 
BEGIN 
    IF FIELDA = '' OR FIELDB = '' THEN 
        SELECT '', '';
    ELSE
         SELECT CVALUE, CVALUE FROM PMT_DTABLE WHERE FIELDA=AVALUE AND FIELDB=BVALUE;
    END IF;  
END $$;
DELIMITER ;
EXIT;

Then set the "sql" property of your Dynaform field:
CALL GET_FIELDS(@@FIELDA, @@FIELDB)

[fibo2358]

Hello Amos,

I have tried SQL queries from your post #822106 but unfortunately the menus show no values.
Please help.

[amosbatto]

I have tried SQL queries from your post #822106 but unfortunately the menus show no values.
Please help.

Execute the command in your MySQL server to figure out what is the problem.

[fibo2358]

Hello Amos,

I have corrected the syntax errors and now it works fine!
Thank you very much.
The key issue was that the subquery must return one row.

[programerboy]

Hi Amos,

We have low speed in calling grid data with drop-down

When you use drop-down or suggest field in a grid and assign that a query, the data calls from database for each row and executed by the corresponding queries, thereby reducing the speed of the data call in the rather high number of records.

This way, may be necessary when we are using dependent fields. Otherwise, I do not think it's necessary to run queries for every row.

Thanks

[amosbatto]

Hi Amos,

We have low speed in calling grid data with drop-down

When you use drop-down or suggest field in a grid and assign that a query, the data calls from database for each row and executed by the corresponding queries, thereby reducing the speed of the data call in the rather high number of records.

This way, may be necessary when we are using dependent fields. Otherwise, I do not think it's necessary to run queries for every row.

Thanks

Programerboy, You are right, but it won't get fixed in version 3 unfortunately. I haven't figured out how to install version 4 (https://github.com/ProcessMaker/nayra), so I'm not sure if this will also be a problem in version 4.

[programerboy]

Thanks Amos,

When does version 4 come out?

[amosbatto]

When does version 4 come out?

I have no idea. They have alpha versions running, but it still needs a lot of work. I tried to install it, but I couldn't figure out how to do it, and we still don't have clear instructions how to do it. It doesn't support version 3 processes, so it won't be an easy upgrade.