gregmelson
Posts: 19
|
| Posted: 04/06/2007, 10:35 AM |
|
May it's not possible cause I've been goin round and round and round in circles, but I really need to get it right.
I have These tables:
Employeess
Tasks
LinkedEmployeesTasks
The LinkedEmployeesTasks table is there because, under the Tasks. Many employees can be assigned certain tasks. I can do that part. Also the task is assigned to the employee that is currently logged in
The catch comes when displaying the info on a grid i.e I need to have the Grid display something like
Task Name | Assigned by(text box only has one value) | Assigned to (This is a list box and has more than one value)
I've tried SQL in the Control source box. It worked but not properly I only got one record back
I tried under the list box query, but Couldn't put the ->f(task_id) thing in the sql where expression it kept returning "null"
Please Someone help!! Or tell me it's not possible or something! Anything! Please!
employees is linked to
_________________
greg
|
 |
 |
peterr
Posts: 5971
|
| Posted: 04/06/2007, 11:01 AM |
|
Hi Greg,
I'm not sure if I understand correctly that you need the "Assigned To" listbox to display different values for different tasks?
Such functionality is not available by default because for fast performance the listbox should be created just once and then included in each row. However, you can use the following workaround code to change listbox values for each row:
$Component->DataSource->Where = "something = " . $Container->DataSource->f("task_id");
$Component->Prepare();
(replace "something" with field value that you want to compare to the task_id field in each row)
This code requires the listbox to be already configured and displaying all values, while custom code is used only to narrow it to a subset of all values by filtering them.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 04/06/2007, 11:06 AM |
|
BTW, the above custom code can be placed in the "Before Show" event of the listbox, or the "Before Show Row" event of the editable grid.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/07/2007, 12:42 AM |
|
Hi Peter,
Thank you so much for you help it really did help. You understood perfectly. I’ve used your code. It filters the listbox correctly but only displays one row in the grid (the first record).
I also get the following error message: Warning: odbc_fetch_row():34 is not a valid ODBC result resource in C:\Program Files\ Apache Group\Apache2\htdocs\taskman\db_odbc.php on the line 121
I used the following to setup the list box: (the list box name is assigned_to btw)
Data Source Type : Table/View
Data Source: Employeess, LinkedEmployeesTasks, Tasks (where Emplyees emp_id is joined to LinkedEmployeesTasks emp_id and LinkedEmployeesTasks task_id is joined to Tasks task_id)
Bound column: Employeess.emp_id
Text column: emp_name
I’ve tried different ways of populating the listbox, but can’t seem to get it right, I doing something wrong I just don’t know what, and I don’t think I’m understanding the warning error message. I presume the sql query I’m running on the listbox is somehow interfering with the grid’s query ????
I really appreciate the help, Peter. You’ve already helped so much, if you know where I’m going wrong with the last bit, please let me know. But if not thank you so much for helping me this far.
Btw the code is under the Before Show of the listbox
$Component->DataSource->Where = " LinkedEmployeesTasks.task_id = " . $Container->DataSource->f("task_id");
$Component->Prepare();
Thanks again 
Best regards,
_________________
greg
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/07/2007, 3:51 PM |
|
Greg,
I'n not sure of the nature of the problem but first I would separate the two tasks: displaying a listbox from filtering it. Therefore first please create a listbox without any filtering and without using any event code, and check if multiple values are being displayed. If not, then there may be a problem with listbox configuration or table joins.
You can also try:
echo $Container->DataSource->f("task_id");
to make sure that this part works and task_id is being retrieved correctly.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/10/2007, 12:09 AM |
|
Hi Peter,
I get 3 task_id's printed at the top of the screen i.e 142, 144, 148 when I use echo $Container->DataSource->f("task_id"); in the before show event with the code.
So that part is definatly working 100% the query runs as well. As the Listbox is filtered with only the employees names that correspond with that task_id.
The problem is that it only brings back one task (i.e. One Row in the Grid). There are 3 unquie tasks(i.e Three Rows) that should have the various unquie empoyees assigned to them based on the Task_id. Something like:
Task ID | Task name | Assigned to (The list box with different names)
142 Fix stuff Greg;Lauren;Bill
143 Build stuff Eric, John, Anne
148 do stuff Low, Mel
I Tried moving the code to the Before Build Select. but forgot that I wouldn't be able to get the Task_id for the Record there (i.e $Container->DataSource->f("task_id"). The query runs there, but it doesn't help without a task_id lol..
It's somewhere in the $Component->Prepare(); that it gets that error which is why I presume it doesn't show the other Tasks. the error/warning is :
Warning: odbc_fetch_row():16 is not a valid ODBC result resource in C:\Program Files\ Apache Group\Apache2\htdocs\taskman\db_odbc.php on the line 121
I have no idea what this means, I've been looking at google, and trying all sorts of combinations to try get this thing to work, but it just won't bring up more than one record?
The are no other events that run
I created a whole new Page/Grid to test with, still does the same thing, If you can think of anything else I could try, I'll gladly try ;). What you said works, it filters the Listbox, but only returns one Row. ????? odd
_________________
greg
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/10/2007, 12:46 AM |
|
Hi Greg,
OK, I think I understand now what's happening. Try specifying a different database connection for the listbox, which can be a copy of the main connection you're using for the grid.
Otherwise I'd recommend contacting product support.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/10/2007, 2:00 AM |
|
Hi Peter.
Just tried it, Still not working . I'll try support. Thank you so much for you efforts though, and I will post the answer should I aquire it on this thread ;). Thanks again!
Best regards,
Greg
_________________
greg
|
|
|
|
materix
Posts: 161
|
| Posted: 04/10/2007, 6:30 PM |
|
I have been struggling with the exact same problem. So I would love the hear if you found a solution.
|
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/10/2007, 11:00 PM |
|
Hi Materix,
No Joy yet but the support is being good. This is what they've given me so far:
Please try to debug sql queries which are executed on this page.
Open db_odbc.php file and uncomment this code line
# printf("<br>Debug: query = %s<br>\n", $Query_String);
in query() function. Then republish db_odbc.php and reload the live page.
You should get all queries passed to database printed at the top of the page. Please test them in database query analyzer, it returns more detailed error message. That helps you to identify what sql fails.
you can debug your code using var_dump() function, please print the list of values before and after modifying Where clause. To get the list of values in listbox, use $ktemployees_kttasks->assigned_list->Values code.
Hope it helps you a bit. I'll Post any other info on if I get the full solution though. Still looking
_________________
greg
|
|
|
|
materix
Posts: 161
|
| Posted: 04/11/2007, 10:26 AM |
|
Hi Greg.
Thx for the answer.
The replies from support suggests that there is something wrong with some of your sql-queries. That is not the case.
I think Peter was on to the problem when he suggested to "specifying a different database connection for the listbox". Copying the database-connection did unfortunately not fix the problem.
|
|
|
|
materix
Posts: 161
|
| Posted: 04/11/2007, 2:11 PM |
|
Hello again.
I got it now working by creating an additional odbc-connection in the control-panel, and also adding an extra database connection in CCS just for the ListBox, as Peter suggested.
|
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/13/2007, 6:04 AM |
|
Hey Matrix, your a Genius! Thank you Thank you! It works must be the driver or something? I don't really care, cause it works!!!
_________________
greg
|
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/13/2007, 6:04 AM |
|
Matrix Rulz!
_________________
greg
|
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/13/2007, 6:10 AM |
|
Thank you all! for helping. Its starting to make more sense to me now, I think. I guess the only thing to do is have 2 connections pitty that means I have to buy the more expensive version. but at least it works! thanks guys
_________________
greg
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/13/2007, 10:43 AM |
|
This worked OK for me with a single connection, but it may depend on the environment. I tried MS Access with ODBC, and MySQL driectly without ODBC.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/14/2007, 4:10 AM |
|
Hmmm..... How do I connect directly to MySql ? - sorry for the silly question but I just don't know how?
_________________
greg
|
|
|
|
peterr
Posts: 5971
|
| Posted: 04/14/2007, 2:32 PM |
|
You'd select MySQL instead of ODBC/MySQL.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
materix
Posts: 161
|
| Posted: 04/17/2007, 5:33 AM |
|
Yes it seems there must be an error in the db_odbc.php file.
I just can not find it
|
|
|
|
gregmelson
Posts: 19
|
| Posted: 04/19/2007, 6:14 AM |
|
Yeah...looks like direct connection is the best option. Have you tried to count the records returned from this query? I tried but I always get 0 or ""
See Code
under the BeforeShow event
$tasks->assigned_list->ds->Where = "ktlinkeremployeesandtasks.task_id = " . $Container->DataSource->f("task_id");
$newvar = $tasks->assigned_list->ds->CountSQL;
$tasks->assigned_list->Prepare();
also tried
$newvar = $tasks->assigned_list->ds->RecordsCount;
in place of
$newvar = $tasks->assigned_list->ds->CountSQL;
I got 0 and "" Don't know how else you'd count the records returned, I check on other posts where some other people had a similar problem but they had workarounds I think. Have u run into this before?
_________________
greg
|
|
|
|
