[nicola]

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 (18.01 KiB) Viewed 2656 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