Browse Form Not Showing

[ernesttan1976]

Hi,

I ran this 3 part query in BeforeBrowse of a Browse form.

$s = "DROP TABLE IF EXISTS certificate_covid";
$t = nuRunQuery($s);

$s2= "CREATE TABLE certificate_covid (
SELECT employee.employee_id, employee.user_field_01, employee.emp_name,
(SELECT COUNT(certificate_type_id) FROM certificate WHERE certificate.employee_id=employee.employee_id AND certificate.certificate_type_id=36) BESAFE,
(SELECT COUNT(certificate_type_id) FROM certificate WHERE certificate.employee_id=employee.employee_id AND certificate.certificate_type_id=38) TraceTogetherApp,
(SELECT COUNT(certificate_type_id) FROM certificate WHERE certificate.employee_id=employee.employee_id AND
certificate.certificate_type_id=37) TechnipDeclaration,
(SELECT COUNT(certificate_type_id) FROM certificate WHERE certificate.employee_id=employee.employee_id AND certificate.certificate_type_id=31) SMO,
(SELECT COUNT(certificate_type_id) FROM certificate WHERE certificate.employee_id=employee.employee_id AND certificate.certificate_type_id=32) SDO
FROM employee WHERE emp_active=1 ORDER BY user_field_01 ASC,emp_name ASC
)";
$t2 = nuRunQuery($s2);

$s3 = "ALTER TABLE `certificate_covid` ADD PRIMARY KEY(`employee_id`)";
$t3 = nuRunQuery ($s3);

The table was generated successfully. However the browse form shows blank like this

Empty Browse.jpg

When I open the preview of the browse form it shows the table perfectly.

Preview of Browse.jpg

What should I do to fix this?

[kev1n]

Hi,

You could create a tmp table as shown here:
https://forums.nubuilder.cloud/viewtopic. ... %23#p19320

But actually, you can do without. Change the SQL like this and use it directly (no PHP required)

Code: Select all

SELECT *
FROM
  (
	SELECT employee.employee_id,
		   employee.user_field_01,
		   employee.emp_name,

	  (SELECT COUNT(certificate_type_id)
	   FROM certificate
	   WHERE certificate.employee_id=employee.employee_id
		 AND certificate.certificate_type_id=36) BESAFE,

	  (SELECT COUNT(certificate_type_id)
	   FROM certificate
	   WHERE certificate.employee_id=employee.employee_id
		 AND certificate.certificate_type_id=38) TraceTogetherApp,

	  (SELECT COUNT(certificate_type_id)
	   FROM certificate
	   WHERE certificate.employee_id=employee.employee_id
		 AND certificate.certificate_type_id=37) TechnipDeclaration,

	  (SELECT COUNT(certificate_type_id)
	   FROM certificate
	   WHERE certificate.employee_id=employee.employee_id
		 AND certificate.certificate_type_id=31) SMO,

	  (SELECT COUNT(certificate_type_id)
	   FROM certificate
	   WHERE certificate.employee_id=employee.employee_id
		 AND certificate.certificate_type_id=32) SDO
		 
	FROM employee
	WHERE emp_active=1
	ORDER BY user_field_01 ASC,
			 emp_name ASC
) T	WHERE 1	 

[ernesttan1976]

Thanks Kevin,

I realized where I made the mistake

RecordID was set to "-1" on the run button.
Its ok now