OptionsConnector not working with renderSQL

Discontinued products Uncategorized discussions
#1

Hi,



I have a OptionsConnector to populate a coro box in a grid



$ContactOptions = new OptionsConnector($connection);



If I use the renderTable method like this -



$ContactOptions->render_table(“contacts”,“ContactID”,“ContactID(value),ContactName(label)”);

$resconn->set_options(“ContactID”,$ContactOptions);



it works fine, the options are inserted into the grid. However I need to filter the results from the table so I am trying to use a RenderSQ as



$ContactOptions->render_sql(“select contacts.ContactID, contacts.ContactName from contacts, jobs where jobs.ClientID=contacts.ClientID and jobs.JobID=$_GET[JobID]”,“ContactID”,“ContactID(value),ContactName(label)”);

$resconn->set_options(“ContactID”,$ContactOptions);



This however doesn work! To further investigate I tried this



$ContactOptions->render_sql(“select ContactID, ContactName from contacts”,“ContactID”,“ContactID(value),ContactName(label)”);





Which should be the same as the RenderTable, but it doesn’t work either which leads me to believe there is an issue with the RenderSQL statement when used in OptonsConnector object.

#2

Change your code as

$ContactOptions->render_sql(“select contacts.ContactID as value, contacts.ContactName as label from contacts, jobs where jobs.ClientID=contacts.ClientID and jobs.JobID=$_GET[JobID]”,“ContactID”,“ContactID(value),ContactName(label)”);

When you are using next syntax ContactID(value) it means that connector will use ContactID field for direct operation with db and “value” alias for all programmatic operations.

#3

Hi Stansilav,

I thought this was working, but now I have noticed that it isn’t quite working. If I load the grid with a certain set of results, e.g. $_GET[JobID]=5, the connector will load the [contacts.ContactID as value, contacts.ContactName as label from contacts] for that JobID correctly, however if I try to subsequently load it for another JobID, it doesn’t load the contacts for that Job ID, yet the contacts for the previous JobID remain in the coro box?

I need to actually refresh the page to clear the results of the coro…

heres the full piece of code…

case “jobcallouts”:

$GetCalloutsSQL = "select concat('jobdetail.php?action=crewcallouts&CalloutID=', t1.CalloutID) as SG, t1.CalloutID, t1.ClientID, t1.JobID, t1.CalloutTypeID, t3.CalloutName as JobCalloutName, t2.TaskID, t2.TaskDesc, t1.NumberCrew, t1.CalloutDate, t1.ChargeRate, t1.StartTime, t1.NumberHours, t4.lookuptext as COStatus, t1.Location, t1.ContactID, t5.ContactName from callouts t1, tasks t2, callouttypes t3, lookups t4, contacts t5 where t1.TaskID=t2.TaskID and t1.CalloutTypeID = t3.CalloutTypeID and t1.Status=t4.lookupvalue and t4.lookupkey=\"calloutstatus\" and t1.JobID=$_GET[JobID] and t1.ContactID=t5.ContactID order by CalloutDate, StartTime, TaskDesc asc";
				
$COoptions = new OptionsConnector($connection);
$COoptions->render_table("callouttypes","CalloutTypeID","CalloutTypeID(value),CalloutName(label)");
		$resconn->set_options("CalloutTypeID",$COoptions);
		
		$TaskOptions = new OptionsConnector($connection);
		$TaskOptions->render_table("tasks","TaskID","TaskID(value),TaskDesc(label)");
		$resconn->set_options("TaskID",$TaskOptions);
		
		$resconn->event->attach("beforeUpdate","calloutUpdate");
		$resconn->event->attach("beforeDelete","calloutDelete");
		$resconn->event->attach("beforeInsert","calloutInsert");
		
		$ContactOptions = new OptionsConnector($connection);
		$ContactOptions->render_sql("select contacts.ContactID as value, contacts.ContactName as label from contacts, jobs where jobs.ClientID=contacts.ClientID and jobs.JobID=$_GET[JobID]","ContactID","ContactID(value),ContactName(label)"); 
		$resconn->set_options("ContactID",$ContactOptions);
		
		$resconn->render_sql($GetCalloutsSQL, "CalloutID", "SG, NumberCrew, TaskID, CalloutTypeID, CalloutDate, StartTime, NumberHours, ChargeRate, Location, ContactID, t4.lookuptext(COStatus)");
		break;
#4

When you are reloading the grid - you are reloading the data only, configuration is preserved. And list of options for each column is preserved as well. So OptionsConnector on server side is triggered only once, during first loading.

If you want to force options reloading, you can try to use next code for data reloading

grid.clearAll(); grid._colls_loaded = false; grid.load("some.php");

#5

Hi Stanislav,

I tried replacing

jobCalloutGrid.clearAndLoad(“jobdetail.php?action=jobcallouts&JobID=” + jobid);

with

jobCalloutGrid.clearAll();
jobCalloutGrid._colls_loaded = false;
jobCalloutGrid.load(“jobdetail.php?action=jobcallouts&JobID=” + jobid);

as advised, however the problem still exists. The OptionsConnector does seem to be called on the server side for each call as options are added in based on the current Job selected, however the previous options are not cleared.

I then added

var combo = jobCalloutGrid.getCombo(9);
combo.clear();

before the load call and this now seems to work. I am wondering if this is the correct/best solution,

thanks
Cyril

#6

Yep, my original solution fixed only part of problem - it forced loading of options second time, but old collection of options need to be cleared as well.

Your solution is fully correct.

#7

Hi Stanislav,

I would like to know, is it possible to implement this concept in java? because I don’t see the required method in the api. For php they have given some samples but for java they did not. I don’t know how to implement this in java. Help!

Thanks and Regards,
Karthick K.

#8

because I don’t see the required method in the api
Which API do you mean?
Sample of options-in-grid, can be checked at
samples.war\WEB-INF\src\Grid_06_GridComboConnector.java