ProcessMaker Forum | BPM Workflow Simplified

Posted: **Mon Jul 08, 2019 11:35 am**

Hello,
I'm exeperiencing and issue when loading a Form with a SQL statement from PM database. In this form I have a grid with a DropDown loaded with the departments:
[SELECT CON_ID, CON_VALUE FROM CONTENT WHERE CON_CATEGORY='DEPO_TITLE'
AND CON_LANG = @@SYS_LANG
AND CON_ID NOT IN ("7405700685b864cf8efba20081648989", "4685363765b864e003c7ef4099718443", "1815795895b864e13a30438030841764", "8353271485b8651f410efe5000584241")
ORDER BY CON_VALUE]
and another dependent DropDown with the equipment on the selected department.
When I want to access this "entity", I have a trigger with a SQL to select the values, including the Department:

Code: Select all

$var = @@SerialNumber;
$var1 = @@CustomerID;
$result = executeQuery( "SELECT * FROM PMT_CUSTOMER_MI WHERE SERIALNUMBER = '$var' AND APP_STATUS = 'COMPLETED' AND 
    CUSTOMERID = '$var1' ORDER BY APP_NUMBER DESC LIMIT 1");
if (is_array($result) and count($result) > 0) {
    @@CalibrationLast = $result[1]['CALIBRATIONLAST'];
    @@CalibrationNext  = $result[1]['CALIBRATIONNEXT'];
    @@CalibrationProcedure  = $result[1]['CALIBRATIONPROCEDURE'];
	@@RecalibrationInterval  = $result[1]['RECALIBRATIONINTERVAL'];
	@%Category  = $result[1]['CATEGORY'];
	@@Manufacturer  = $result[1]['MANUFACTURER'];
	@@MeasuringQty  = $result[1]['MEASURINGQTY'];
	@@Name  = $result[1]['NAME'];
	@@RangeInterval  = $result[1]['RANGEINTERVAL'];
	@@Remark  = $result[1]['REMARK'];
	@@SoftwareIncluded  = $result[1]['SOFTWAREINCLUDED'];
	@@Status  = $result[1]['STATUS'];
	@@Validity  = $result[1]['VALIDITY'];
	@@YearProduction  = $result[1]['YEARPRODUCTION'];
	@@YearStart  = $result[1]['YEARSTART'];
	@@TypeApprovalNumber  = $result[1]['TYPEAPPROVALNUMBER'];
	@@DateCreated  = $result[1]['DATECREATED'];
	@@Accessories  = $result[1]['ACCESSORIES'];
	@=Documentation  = $result[1]['DOCUMENTATION'];
	@@AccuracyClass  = $result[1]['ACCURACYCLASS'];
	@@MiID  = $result[1]['MIID'];
	@@Model  = $result[1]['MODEL'];
	@@OldAppNumber  = $result[1]['APP_UID'];
}
//lOAD GRID OF REFERENCE STANDARDS
$miid = @@MiID;
//This query loads the DropDown with pre-saved values
$query = "SELECT DIVISIONID AS DivisionID, REFSTD as RefStd, CUSTOMERMIID AS CustomerMiID 
   FROM PMT_CUSTOMER_MI_REFSTD WHERE CUSTOMERMIID='$miid'";

$result = executeQuery($query);
if (is_array($result) and count($result) > 0)
    @=gridRefStd = $result;

But the form renders with this image:

(The first grid row is how should be - become like that after I click over the dropdownw!!) The both rows appear like the second.
Any tip about what is happening?
Thanks.

Posted: **Mon Jul 08, 2019 8:23 pm**

Strange.
If you are using PM 3.1 or later, you can simplify your first SQL query like this:
SELECT DEP_UID, DEP_TITLE FROM DEPARTMENT

How are you executing your trigger?

Posted: **Thu Jul 11, 2019 8:38 am**

Hi Amos,
Thanks again.
Selecting the Departments with your code worked fine (I was suspecting that the issue was with having the filer by LANG).
But I still have the issue sincronizing the dependent column (see picture).

In this process I have 1 Task with 2 forms. On first form the user selects the Customer and the Serial Number of the equipment. If ht Serial Number existe, it opens the form with with its data; otherwise it opens empty for creating a new record. This situation is when the equipment already exists at database. I'm firing up the trigger on "After Dynaform" of 1st form. The equipment data is shown on 2nd dynaform.
Thanks.

Posted: **Thu Jul 11, 2019 9:48 pm**

How are you populating the list of options in the "Name" dropdown in the grid? Is the @@gridRefStd variable populating the grid?

Posted: **Fri Jul 12, 2019 4:15 am**

The column id is: RefStd
I'm populating the 'Name' column with this SQL:
SELECT CODE, CONCAT(SERIALNUMBER, ' | ', NAME) AS NAME FROM PMT_GEOSTM_MI
WHERE APP_STATUS = 'COMPLETED'
AND STATUS = 1
AND DIVISION = @@DivisionID

DivisionID is the id of the 1st column (DEP_UID).

Posted: **Fri Jul 12, 2019 9:01 pm**

I don't see anything wrong with your code. I recommend that you turn on MySQL query logging and then check your log file to see what is happening. Also copy the SQL SELECT command from the log file and execute it directly in MySQL (from the command line or from phpMyAdmin) to see what it returns.

Posted: **Mon Jul 15, 2019 11:25 am**

The queries work fine.
I'm suspecting that it is because the first column ID is a CHAR (DEP_UID) and the dependent column cannot syncronize for CHAR field!!!
When I'm doing "AND DIVISION = @@DivisionID", this doesn't work as expected - it shows the 2nd column ID but not the Description (NAME).
I'm just guessing...
Thanks.

Posted: **Mon Jul 15, 2019 10:12 pm**

Is PMT_GEOSTM_MI.DIVISION a char or varchar field? If it is a number field, then you will have problems with MySQL.

If you post your PM Table as a .pmt file and your Dynaform as a .json file, I will look at it for your.

Posted: **Tue Jul 16, 2019 5:16 am**

Hello Amos,
The DIVISION is the system DEP_UID (Department). Please, find the required files attached. PMT_GEOSTM_MI is a report table.
Thank you.

ProcessMaker Forum | BPM Workflow Simplified

DropDown inside Grid - Issue on loading pre-saved values

DropDown inside Grid - Issue on loading pre-saved values

Re: DropDown inside Grid - Issue on loading pre-saved values

Re: DropDown inside Grid - Issue on loading pre-saved values

Re: DropDown inside Grid - Issue on loading pre-saved values

Re: DropDown inside Grid - Issue on loading pre-saved values

Re: DropDown inside Grid - Issue on loading pre-saved values

Re: DropDown inside Grid - Issue on loading pre-saved values

Re: DropDown inside Grid - Issue on loading pre-saved values

Re: DropDown inside Grid - Issue on loading pre-saved values