maxhugen
Posts: 272
|
| Posted: 04/15/2008, 8:18 PM |
|
I'm having a problem working out the WHERE clause for a dependent listbox.
My page is like a questionnaire:
Select something in Listbox1, it filters Listbox2
Select something in Listbox2, it filters Listbox3
Select something in Listbox3, it filters Listbox4
(etc)
I will use AJAX to do the filtering of each dependent Listbox as the record is being entered (next job!).
However, when I go to a previously entered record, the Listboxes have not been filtered. Using the Query Builder for the Data Source, I added a WHERE Parameter to Listbox2 like this:
Field: EL_Element_ID (this is the 'parent id' field in 'Listbox2' data source)
Condition: equals(=)
Parameter: EL_Element_ID (this is the actual name of 'Listbox1' in the page form)
Type: Form
I can't figure out what I'm doing wrong... can anyone point me in the right direction pls?
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
 |
 |
maxhugen
Posts: 272
|
| Posted: 04/17/2008, 8:35 PM |
|
I'm still having major headaches with this, and YesSoftware Support has gone very quiet.
There are 2 problems:
1. In a New Record, the dependent Listboxes display all options - they should show none.
2. In an Existing Record, the dependent Listboxes display all options - they should be filtered.
I've even gone so far as to recreate the problems in the CCS Example Pack 2 which I've zipped and emailed to Support, but so far, nothing...
Has anyone used AJAX Dependent Listboxes within a Record?
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
maxhugen
Posts: 272
|
| Posted: 04/20/2008, 12:32 AM |
|
It seems that I need to modify the Where clause of the dependent Listbox in the Before Build Select event. In this example, I have a Listbox 'section_id' which is dependent on 'department_id'.
I've tried the following, but as I don't know php, the syntax may be wrong. The Record name is 'employees1', and contains fields 'department_id' and 'section_id'. I'm trying to apply a Where clause so that the section_id Listbox is filtered by field department_id.
function employees1_section_id_ds_BeforeBuildSelect(& $sender)
...
//Custom Code @48-2A29BDB7
if ($employees1->DataSource->department_id->GetValue() == "") {
$employees1->section_id->DataSource->Where = "department_id = 0";
} else {
$employees1->section_id->DataSource->Where = "department_id = " . $employees1->DataSource->department_id->GetValue();
}
//End Custom Code
...
}
Can anyone suggest what is wrong with this pls?
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
DonP
|
| Posted: 04/20/2008, 11:06 AM |
|
I don't have CCS available on this system to give you a specific answer
but in general, this syntax is correct for a regular Before Build Select
of a record or a grid. It might need to be tweaked for use on a listbox.
If you are using CCS 3.x, the Help will clarify but, if you are using
CCS 4.0, I understand that the Help might be incomplete in some areas:
if ($employees1->department_id->GetValue() == "") {
$employees1->ds->Where = "department_id = 0";
} else {
$employees1->ds->Where = "department_id = " .
$employees1->department_id->GetValue();
}
If the values are coming from the datasource, try this:
if ($employees1->ds("department_id") == "") {
$employees1->ds->Where = "department_id = 0";
} else {
$employees1->ds->Where = "department_id = " .
$employees1->ds("department_id");
}
In any event, the word "DataSource" in the code is not correct and I am
not sure why it is often included in the CCS Help as it only confuses
things.
Don (DonP)
maxhugen wrote:
> It seems that I need to modify the Where clause of the dependent Listbox in the
> Before Build Select event. In this example, I have a Listbox 'section_id' which
> is dependent on 'department_id'.
>
> I've tried the following, but as I don't know php, the syntax may be wrong. The
> Record name is 'employees1', and contains fields 'department_id' and
> 'section_id'. I'm trying to apply a Where clause so that the section_id Listbox
> is filtered by field department_id.
>
> function employees1_section_id_ds_BeforeBuildSelect(& $sender)
> ...
> //Custom Code @48-2A29BDB7
> if ($employees1->DataSource->department_id->GetValue() == "") {
> $employees1->section_id->DataSource->Where = "department_id = 0";
> } else {
> $employees1->section_id->DataSource->Where = "department_id = " .
> $employees1->DataSource->department_id->GetValue();
> }
> //End Custom Code
> ...
> }
>
> Can anyone suggest what is wrong with this pls?
>
> _________________
> Max
> (99.9% CCS Newbie)
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
maxhugen
Posts: 272
|
| Posted: 04/20/2008, 6:29 PM |
|
Hi Don
Thanks for your suggestion, but it didn't work. I have tried every syntax permutation that I can think of, but I haven't cracked it yet.
If I can't resolve a way to do this, then my questionnaire application is a dead duck. 
As mentioned in my posts above, I've put together a simplified example, which consists of one CCP page, one Service, and a slightly modified Intranet.MDB (from CCS Example Pack 2). The page includes a precise explanation of what I've done.
Is there any chance that I could send you this in a zipped file to have a look at? I know I'm asking a lot in a forum, but I don't know where else to turn. Yes Support doesn't seem able to offer a solution.
If you would take a look, please PM me with an email address.
Cheers
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
DonP
|
| Posted: 04/21/2008, 12:55 AM |
|
Unfortunately I am moving and am limping along on a temporary PC without
CCS installed (nor much else) so I cannot help you in that manner.
Sorry! I can say that what you are trying to do is quite easily do-able
so maybe someone with CCS handy and more time can help. Unless I had a
typo in the code I posted, and I don't see one, the syntax was proper
although I wasn't sure if you needed to also specify the element name
for a listbox.
Just to be sure where you are adding the code, you need to use a Before
Build Select for the listbox itself and not for the record.
You can also use your own function in CCS to loop through the records
and present the listbox dynamically although that method should not be
necessary.
One thing, be sure that the Query Builder itself has a Where clause for
the listbox even if you are overwriting it in your custom function.
Maybe something like IS NOT NULL. I have not seen it documented anywhere
but it seems to need one before any custom code-based Where clauses work.
By the way, my first response was only intended as an answer to the
question to which I posted it, which was your third in this thread, to
correct the syntax. I was not replying to the original post nor the
second one as I have not used AJAX in CCS.
Don
maxhugen wrote:
> Hi Don
>
> Thanks for your suggestion, but it didn't work. I have tried every syntax
> permutation that I can think of, but I haven't cracked it yet.
>
> If I can't resolve a way to do this, then my questionnaire application is a
> dead duck.
>
> As mentioned in my posts above, I've put together a simplified example, which
> consists of one CCP page, one Service, and a slightly modified Intranet. MDB
> (from CCS Example Pack 2). The page includes a precise explanation of what I've
> done.
>
> Is there any chance that I could send you this in a zipped file to have a look
> at? I know I'm asking a lot in a forum, but I don't know where else to turn. Yes
> Support doesn't seem able to offer a solution.
>
> If you would take a look, please PM me with an email address.
>
> Cheers
> _________________
> Max
> (99.9% CCS Newbie)
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
maxhugen
Posts: 272
|
| Posted: 04/21/2008, 2:55 AM |
|
Thanks Don
Yes, I'm using the BeforeBuildSelect for the Listbox itself. But for reasons I haven't yet figured out, I can't get a value for the Record field 'department_id', which I need in order to filter the listbox 'section_id'. <sigh> Must be doing something totally stupid...
Hope your move goes well. Cheers.
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
wkempees
Posts: 1679
|
| Posted: 04/21/2008, 4:42 AM |
|
DataSource-> vs. ds->
Old vs new
@D, happy move.
@M you have been PM(p)'ed
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
DonP
|
| Posted: 04/21/2008, 10:24 AM |
|
One thing I just thought of that sometimes causes problems. Did you try
a space between the quote and the field name on the WHERE clause? I am
wondering if CCS adds one automatically but if not, it would not work.
if ($employees1->department_id->GetValue() == "") {
$employees1->department_id->ds->Where = " department_id = 0";
} else {
$employees1->department_id->ds->Where = " department_id = " .
$employees1->department_id->GetValue();
}
Also, there is a difference between:
$employees1->department_id->ds->Where = " department_id = 0";
and
$employees1->department_id->ds->Where = " department_id IS NULL";
One has a value (0), the other does not.
Don (DonP)
maxhugen wrote:
> Hi Don
>
> Thanks for your suggestion, but it didn't work. I have tried every syntax
> permutation that I can think of, but I haven't cracked it yet.
>
> If I can't resolve a way to do this, then my questionnaire application is a
> dead duck.
>
> As mentioned in my posts above, I've put together a simplified example, which
> consists of one CCP page, one Service, and a slightly modified Intranet.MDB
> (from CCS Example Pack 2). The page includes a precise explanation of what I've
> done.
>
> Is there any chance that I could send you this in a zipped file to have a look
> at? I know I'm asking a lot in a forum, but I don't know where else to turn. Yes
> Support doesn't seem able to offer a solution.
>
> If you would take a look, please PM me with an email address.
>
> Cheers
> _________________
> Max
> (99.9% CCS Newbie)
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
maxhugen
Posts: 272
|
| Posted: 04/21/2008, 4:02 PM |
|
Hi Don
Thanks for the thoughts... CCS does include a space in the Select SQL, the syntax looks like:
SELECT section_id, department_id, section_name FROM sections {SQL_Where} {SQL_OrderBy}
Also, I'm aware of the Zero/Null differences.
Problem is that 'department_id' doesn't return a value:
$employees1->department_id->GetValue()
I can filter the listbox using a value I provide, but can't seem to grab the Record's department_id value.
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
wkempees
Posts: 1679
|
| Posted: 04/22/2008, 4:14 AM |
|
As to the original problem, after receiving the project and viewing the code:
Two listboxes on a record, one: department, other : section.
The section listbox should be dependant on the department listbox.
The problem as reported is :
in Editmode the section listbox is not filtered on the selected (shown) department.
in Addmode the section listbox is showing all values where it should be showing none or only the ones related to the selected department.
Solution (again CCS scores in the 'simple' department):
Deleted all code in the BeforeBuildSelect of the section listbox.
The New Record problem: Section listbox showing values while no department is selected.
Listbox section_id -> properties click the DataSource [...] invoking VQB.
Next in the left pane click the WHERE , press the '+' to add a condition
the condition is :
Type Parameter,
Field name : department_id,
condition : equals (=),
Parameter: department_id,
Type:Form,
Use Default Value .... empty: -1
Click OK twice to save and close VQB
Press F9 regenerate, that solves both the problems.
What you are actualy doing is priming the SQL send to the service (Ajax is enabled, Features:PTDependantListbox) to default to empty when no parameter is available.
This will work on any listbox you need to default to empty.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
maxhugen
Posts: 272
|
| Posted: 04/23/2008, 8:36 PM |
|
Thanks Walter, but this doesn't work for an existing record - there are no options displayed for listbox section_id. It appears that the DataSource parameter 'department_id', type 'Form', is not returning a value. Why not, I wonder?
Once again, I find the documentation sorely lacking - there is no explanation of the DataSource parameter 'type', such as when they can/can't be used, code snippets, etc. Syntax alone is just not sufficient.
Since the simple and logical way didn't work, I did finally figure out a somewhat clumsy work-around:
I changed the listbox datasource to:
SELECT sections.section_id AS sections_section_id, section_name
FROM employees INNER JOIN sections ON
employees.department_id = sections.department_id
WHERE employees.emp_id = {emp_id}
ORDER BY sections.section_name
The Where parm is getting emp_id from the URL, and I set the 'Is Null' condition to ON.
So... it can only return sections where the record's dept matches the dept in the sections table, and is further limited to the dept of the specified employee.
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
wkempees
|
| Posted: 04/24/2008, 1:28 AM |
|
Still working on this for you.
Meanwhile make sure that the Service, the part that handles the Ajax
request, also reflects the correct SQL and the default value: -1, as
described.
My current status with your problem is:
New Record functioning correctly, apart from the fact that when form is
initialy displayed, both listboxes displaying 'Select Value', the second
listbox will display all possible values.
If the first listbox value is changed from Select valu to Administration and
back to Select Value, the second lisbox is correctly primed and empty.
Edit Record, functioning ok, with the original SQL.
Will report back if solution available.
It must be in the initial value of the first listbox not being passed
correctly.
Walter
|
|
|
|
maxhugen
Posts: 272
|
| Posted: 04/24/2008, 6:33 PM |
|
Hi Walter
Just a brief note: the ajax Service doesn't need the same convoluted SQL as the listbox, as its being successfully passed the the Form's department_id value - unlike the listbox's datasource.
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
|
