cringwall
Posts: 11
|
| Posted: 11/09/2010, 10:24 AM |
|
I am trying to do a dynamic WHERE clause on a listbox in my editable grid - I want to filter the choices of my listbox based on a hidden field that is in the row of the grid:
$survey_cat_value = $survey_history->survey_cat_fk->Value;
$survey_history->survey_answer_lb->DataSource->Where .= "survey_resp_cat = $survey_cat_value";
I don't know where to put this code - any ideas on where I'm going wrong?
|
 |
 |
MichaelMcDonald
Posts: 640
|
| Posted: 11/09/2010, 8:05 PM |
|
is the listbox in the editable grid?
_________________
Central Coast, NSW, Australia.
|
|
|
|
cringwall
Posts: 11
|
| Posted: 11/10/2010, 6:49 AM |
|
Yes it is...
I want to capture a row-level value in the database query on which to base my filter of the list box. I can't do it with a hidden form value, because of the order of the events.
When the row loads, I need to be able to capture a value from that row and use that value to filter the list box WHERE clause. The list box is in the same row, however as the one I"m trying to capture from.
I just don't know where to put the code. It may just not be possible, but it would sure come in handy.
|
|
|
|
andrewi
Posts: 162
|
| Posted: 11/10/2010, 10:44 AM |
|
It can be done. RogerH & DonB showed the way ( http://forums.codecharge.com/posts.php?post_id=36472 ), and example below is largely pasted from an example I built some years ago (also a survey form) using his findings. It uses an undocumented feature, but it still works in 4.3
Apologies that this is in ASP. The principle should be the same in PHP, and there are some PHP references in the above thread.
You need to populate two events.
Assume the grid form is called "GridForm", the value you want to filter by is in a label control called "lblRowID" and the listbox you want to filter is called "cboList".
1. BeforeBuildSelect event of the list control
Use it to construct a Select and Where SQL statement to fill the list box, using values from the grid-row (in this example, lblRowID) as required.
Function GridForm_cboList_DataSource_BeforeBuildSelect(Sender) 'GridForm_cboList_DataSource_BeforeBuildSelect @21-77460F7A
' -------------------------
dim strWHERE, strSQL
strSQL = "SELECT ID, Name FROM Table2 "
strWHERE = " Table2.ForeignKeyID = " & GridForm.lblRowID.Value
GridForm.cboList.DataSource.SQL = strSQL
GridForm.cboList.DataSource.WHERE = strWHERE
2. BeforeShowRow event of the Grid
Codecharge will (sensibly) only run the code to fill the listbox once when building the page, so you need to force it to re-run the above code for each row of the grid.
Function GridForm_BeforeShowRow(Sender) 'GridForm_BeforeShowRow @12-0019DD0F
' -------------------------
GridForm.cboList.isPopulated = false
Notes: you can set the DataSource of the list box from the properties window if you want - it will help you select the bound and display fields - but this code overwrites it in any case.
You might think that you only have to set the WHERE value above, since that's the only thing that changes. However, it looks like setting isPopulated to false clears this value out, so you have to set WHERE and SQL properties for each row. I belive there's an ORDERBY value too if required.
|
|
|
|
katmanrocks
Posts: 8
|
| Posted: 02/14/2011, 7:51 AM |
|
Thanks for the undocumented tip. I needed to cause the listbox to be rebuilt on a "per row" of the grid change of cetain values in the record. It worked without entirely rebuilding the SQL query for the listbox. All I did was modify the "where" portion of the query and set ispopulated to false. Thanks!
|
|
|
|
JayEdgar
Posts: 77
|
| Posted: 02/15/2011, 6:38 AM |
|
You might also want to consider jQuery. I've just started using it, and find it works much more easily for some things than attempting to force CCS to do it.
Cheers.
|
|
|
|
|
