JSand
Posts: 8
|
| Posted: 11/16/2011, 1:42 PM |
|
I have a boolean field in a database tied to a search form checkbox (called EXPERIMENTAL). The users want an option to search for Experimental (true), NonExperimental (False) or ALL (true or false). Essentially I need tri-state checkbox functionality.
My solution is remove the checkbox and instead, tie a List Box to the database that will have essentially 3 value pairs (1:Experimental), (0:NonExperimental) and finally if nothing is selected, that page should return all records (True OR false).
It works fine when EXPERIMENTAL or NONExperimental is selected in the list box. However, when nothing is selected, the form's SQL where statement is apparently converting null to 0 and only returning NONExperimental values.
Question 1. How can I echo the SQL Where statement that PHP/CCS actually uses so I can troubleshoot it?
Question 2. How can I manually edit the SQL Where statement sent to SQL Server?
I have searched the forums and tried to use the DataSource_BeforeBuildSelect but it didn't work. I have also tried to echo the $Component->DataSource->Where statement but it returns nothing.
Any help would be greatly appreciated. I'm kind of new to CCS so if there's a better way, I'm all ears.
Thanks
J
|
 |
 |
datadoit
|
| Posted: 11/16/2011, 4:13 PM |
|
First:
http://docs.codecharge.com/studio40/html/ProgrammingTec...Output.html?toc
Second:
http://docs.codecharge.com/studio40/html/ProgrammingTec...Clause.html?toc
As you've discovered, a 0 (zero) is the same as a null value. So what I
would do is change your listbox values to text (Experimental and
NonExperimental), then in your grid's BeforeBuildSelect:
if ($Container->YourListbox->GetValue()) {
if ($Container->DataSource->Where <> "") {
$Container->DataSource->Where .= " AND ";
}
if ($Container->YourListbox->GetValue() == "Experimental") {
$Container->DataSource->Where .= "EXPERIMENTAL = 1";
}
elseif ($Container->YourListbox->GetValue() == "NonExperimental") {
$Container->DataSource->Where .= "(EXPERIMENTAL = 0 OR EXPERIMENTAL
IS NULL)";
}
}
|
|
|
|
JSand
Posts: 8
|
| Posted: 11/17/2011, 9:13 AM |
|
Datadoit,
Thanks for the reply. I have tried to implement your code suggestion (with a minor change) but the very first line gives me an error. I get the error stating:
"Call to a member function GetValue() on a non-object ....."
I'm not entirely certain about the $Container variable's meaning. I assume that it refers to the page and its collection of controls? (Newbie question). Perhaps I am not referencing that correctly?
My list box in the search form is named s_Experimental. So I substituted that into the code.
My (incorrect) code is below:
***********************************************************************************************
if ($Container->s_Experimental->GetValue()) {
if ($Container->DataSource->Where <> "") {
$Container->DataSource->Where .= " AND ";
}
if ($Container->s_Experimental->GetValue() == "Experimental") {
$Container->DataSource->Where .= "EXPERIMENTAL = 1";
}
elseif ($Container->s_Experimental->GetValue() == "NonExperimental") {
$Container->DataSource->Where .= "(EXPERIMENTAL = 0 )";
}
else {
$Container->DataSource->Where .= "(EXPERIMENTAL = 0 or EXPERIMENTAL = 1 )";
}
}
**********************************************************************************************
Thanks for any guidance.
|
|
|
|
datadoit
|
| Posted: 11/17/2011, 10:17 AM |
|
You want to put the code in your results grid's BeforeBuildSelect event,
not in your search form.
$Container will always refer to the parent container. In this case your
parent container is the result grid itself. It's a handy-dandy
reference to use instead of using the control's name (ie: $MyGrid).
Then if you change the name of the grid control, you need not worry
about the custom code naming.
|
|
|
|
JSand
Posts: 8
|
| Posted: 11/17/2011, 11:39 AM |
|
Datadoit,
In regard to the last post, I actually had placed the code in the grid's BeforeBuildSelect event but figured I needed to tie the code back to the Search Form via the name of the drop list. For some reason I could not get things to work. But using your idea, I was able to modify it and make it work. (I still wish I knew why I couldn't make your idea work directly though.)
In my next post, I will put my working code with some notes in it so other poor souls like me may derive some guidance. Thanks for your help. It was invaluable!
|
|
|
|
datadoit
|
| Posted: 11/17/2011, 11:59 AM |
|
Ah, check the value of the submitted parameter in the URL instead of the
search form control's value:
if (CCStrLen(CCGetParam("s_Environmental", "")) > 0) {
....
that should be easier for you.
|
|
|
|
JSand
Posts: 8
|
| Posted: 11/17/2011, 12:13 PM |
|
RESOLVED:
Problem: Need a Search Form list box that is tied to a boolean database field that can pull records in an associated grid in 3 separate scenarios:
1. True
2. False
3. All records if NOTHING is selected in the list box.
Resolution:
***************** CHANGES TO THE SEARCH FORM ***********************************
-Delete the Search Form check box for this field (write down the name before deleting)
-Replace it with List box and name it the same as the previously deleted check box
-In the Search Form (Design Mode) for the new list box:
--DATA SOURCE TYPE: List of Values
--DATA SOURCE: Yes;True Condition ; No; False Condition
(ex: Yes; Experimental ; False; NonExperimental)
--DATA TYPE: TEXT (Not Boolean!)
Right click on the List Box and choose <SELECT> PROPERTIES
In the Options box, in the bottom of the screen, I only have one option. Double click it to see its settings. Settings are:
NAME: Select Value
VALUE: leave blank
SELECTED: Check it
Close out of the <SELECT PROPERTIES> screen.
********* END OF CHANGES FOR THE SEARCH FORM ***********************
******** CHANGES FOR THE GRID FORM *************************************
-Click on the Grid form and and go into its DATA SOURCE using the ... button
-This of course, brings up the Visual Query Builder (VQB)
-On the left side of the screen, scroll down till you get to the WHERE statement
-REMOVE any part of the WHERE statement that refers to your boolean field. (Leave the rest of the Where statement intact.) Close this box.
Now we will add some custom code to the grid to manually modify the SQL where statement on the fly depending upon what the user chooses in the Search Form Drop list.
Right click on the grid -> Events Tab -> BEFORE BUILD SELECT -> Right click, ADD CODE.
In the custom code area, add the following lines to the code. You will replace s_Experimental to whatever your search form Drop box is named. Also, you will have to replace EXPERIMENTAL with whatever your database boolean field is named.
************ CODE - BeforeBuildSelect in the Grid ***********************
//Append the 'AND' statement to the existing Where statement
if ($Container->DataSource->Where <> "") {
$Container->DataSource->Where .= " AND ";
}
//Handle the TRUE condition if that's what is selected in the List box
if (CCGetFromGet("s_Experimental") == "Yes") {
$Container->DataSource->Where .= " EXPERIMENTAL = 1 ";
}
//Handle the FALSE condition if that's what is selected in the List box
elseif (CCGetFromGet("s_Experimental") == "No") {
$Container->DataSource->Where .= " EXPERIMENTAL = 0 ";
}
//Handle the case where NOTHING is selected <==> Use both the TRUE and the FALSE conditions (all records!)
else {
$Container->DataSource->Where .= "(EXPERIMENTAL = 0 or EXPERIMENTAL = 1 ) ";
}
*************** END CODE - beforeBuildSelect *************************
************** POSSIBLE CHANGE - BeforeShow *****
If you have code in your BeforeShow event that references the boolean field, you may have to delete that code and replace it with the same code (or slightly modified) used in the BeforeBuildSelect.
************* END CODE - BeforeShow *******
Handy tip:
You can actually SEE the SQL WHERE statement your system is generating for the grid by inserting the following code into the Grid's BeforeExecuteSelect event and changing the $YourGridName to the appropriate name of your grid:
************ Code for BeforeExecuteSelect *****************************
echo "SQL:" . $YourGridName->DataSource->SQL . "<br>";
echo "ORDER BY:" . $YourGridName->DataSource->Order . "<br>";
echo "WHERE:" . $YourGridName->DataSource->Where . "<br>";
Good luck.
|
|
|
|
|
