OptionsConnector returning null:null pairs

Suite and Optimus Suite 5
#1

I have a weird issue going on where I’m getting null:null pairs (but always the correct number of pairs) when I’m trying to load options into a Scheduler lightbox’s select. I know the SQL query works and returns valid data. And since the correct number of pairs are returned, that tells me that the SQL query is working, just returning “null” for the value and label.

Here’s the PHP code for the scheduler load call:

$resources = filter_input(INPUT_GET,"resources");
$sql =	"SELECT EmployeeID,CONCAT(LastName,', ',FirstName) AS Name FROM ".$employeeDB.".EmployeeMaster "
	.	"WHERE EmployeeID IN "
	.	"(SELECT EmployeeID FROM ".$infosysDB.".ResourceScheduler_Permissions "
	.	"INNER JOIN ".$infosysDB.".ResourceScheduler_Resources ON ResourceScheduler_Permissions.ResourceID = ResourceScheduler_Resources.ResourceID "
	.	"WHERE ResourceGroup NOT IN (SELECT GroupID FROM ".$infosysDB.".ResourceScheduler_Groups WHERE GroupText = 'Administration'))"
;
$organizer = new OptionsConnector($mysqli,"MySQLi");
$organizer->render_complex_sql($sql,"EmployeeID","EmployeeID,Name");
		
$sql = "SELECT reservations.*, "
	.	"resources.ResourceName,resources.Location,resources.EventBackColor,resources.EventTextColor "
	.	"FROM ResourceScheduler_Reservations reservations "
	.	"INNER JOIN ResourceScheduler_Resources resources ON reservations.ResourceID = resources.ResourceID "
	.	"WHERE resources.ResourceID IN (".$resources.") "
	.	"AND resources.Status = 'Active' "
;
$scheduler = new SchedulerConnector($mysqli,"MySQLi");
$scheduler->set_options("organizer",$organizer);
$scheduler->event->attach(new EventDataHandler());
$scheduler->render_sql($sql,"ReservationID","StartDate,EndDate,Title,ReservedBy,Notes,ResourceName,Location,EventBackColor,EventTextColor");
#2

Try to change your code like next

$sql = "SELECT EmployeeID as value,CONCAT(LastName,', ',FirstName) AS label FROM ".$employeeDB.".EmployeeMaster " . "WHERE EmployeeID IN " . "(SELECT EmployeeID FROM ".$infosysDB.".ResourceScheduler_Permissions " . "INNER JOIN ".$infosysDB.".ResourceScheduler_Resources ON ResourceScheduler_Permissions.ResourceID = ResourceScheduler_Resources.ResourceID " . "WHERE ResourceGroup NOT IN (SELECT GroupID FROM ".$infosysDB.".ResourceScheduler_Groups WHERE GroupText = 'Administration'))" ; $organizer = new OptionsConnector($mysqli,"MySQLi"); $organizer->render_complex_sql($sql,"value","value,label");

Option connector need to have the data fields named as value and label to work correctly.

#3

That worked. Thank you!

Could I make a suggestion to show an example of a custom query in the OptionsConnector documentation? The documentation does say that it requires a value and label, but I didn’t understand from that statement that the field names had to actually be “value” and “label”, since that’s not a requirement for any of the other connectors that I’ve worked with so far.

Thanks again!

#4

Yep, we will update the related section of the doc.