Questions and discussion about using ProcessMaker 2: user interface, running cases and functionality

Moderator: amosbatto

Forum rules: Please search to see if a question has already been asked before posting. Please don't ask the same question in multiple forums.
By nicola
#783984
I have 2 tables; Statutory & Payroll and have a trigger to pre-populate a grid with values from one of these tables. This is working fine when it's a one-to-one match.
In some instances I need to populate a grid field with data from the Payroll table, unless there is no data, then it should populate with data from the Statutory table. I can't seem to get this to work. It is always populating with data from the Payroll table.
I've attached a screenshot of the data in the Payroll table, and the part I keep changing and testing against is FilingComments - FILING_COMMENTS
Payroll PM Table.JPG
Payroll PM Table.JPG (18.01 KiB) Viewed 693 times
The trigger is currently set as follows as I believe the data is empty rather than NULL
Code: Select all
$isoCountry = @@ISOCOUNTRY;
$payrollId = @@PAYROLL_ID;

$query1 = "SELECT 
PMT_PAYROLLFILING.PAYROLL_FILING_UID, 
PMT_STATUTORYFILING.FILING_UID, 
PMT_STATUTORYFILING.FILING_NAME, 
PMT_STATUTORYFILING.FILING_DESCRIPTION, 
IF(PMT_PAYROLLFILING.FILING_ROLE = "",PMT_STATUTORYFILING.FILING_ROLE, PMT_PAYROLLFILING.FILING_ROLE) as FILING_ROLE, 
IF(PMT_PAYROLLFILING.FILING_FREQUENCY = "",PMT_STATUTORYFILING.FILING_FREQUENCY, PMT_PAYROLLFILING.FILING_FREQUENCY) as FILING_FREQUENCY, 
IF(PMT_PAYROLLFILING.FILING_FORMAT = "",PMT_STATUTORYFILING.FILING_FORMAT, PMT_PAYROLLFILING.FILING_FORMAT) as FILING_FORMAT,
PMT_PAYROLLFILING.FILING_ASSIGNED, 
IF(PMT_PAYROLLFILING.FILING_COMMENTS = "",PMT_STATUTORYFILING.FILING_COMMENTS, PMT_PAYROLLFILING.FILING_COMMENTS) as FILING_COMMENTS 
FROM PMT_STATUTORYFILING 
LEFT JOIN PMT_PAYROLLFILING 
ON PMT_STATUTORYFILING.FILING_UID = PMT_PAYROLLFILING.FILING_UID AND PMT_PAYROLLFILING.PAYROLL_ID = $payrollId
WHERE PMT_STATUTORYFILING.ISOCOUNTRY = '$isoCountry'
ORDER BY PMT_PAYROLLFILING.FILING_ASSIGNED DESC, PMT_PAYROLLFILING.FILING_FREQUENCY, PMT_STATUTORYFILING.FILING_UID";

$result = executeQuery($query1);

@@RESULT = $result;

@=PAYROLL_FILING_GRID = $result;
I've also tried this with COALESCE and CASE WHEN but I still get the data from the Payroll table, even if this data is empty.

Is there something I'm missing, or something that Process Maker doesn't support that is stopping this from happening?
Thanks

Hi, Is there any way to show my custom column only[…]

Send for clarification

Hi, I know this is an old post but i could not fin[…]

add dropdown to search process

Please replay if there is any solution!!!!

Amosbatto, PMUsers.com is down.

Hi Amos, I appreciate you setting up a tips and t[…]