rado
Posts: 221
|
| Posted: 05/16/2009, 8:31 AM |
|
Hi good people,
I have need to join first and last name of the user (two separate fields in DB) and list them together in one list box control. How I can accomplish this with CCS4.
Thanks a lot,
Rado
|
 |
 |
Gena
Posts: 591
|
| Posted: 05/16/2009, 8:44 AM |
|
use
concat ()
or
concat_ws()
sql functions in VQB
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 05/16/2009, 9:11 AM |
|
Thanks Gena
Since I need the full name (first and last name) in most cases, I'm thinking to go towards solution where I create separate field in DB ("full_name") that will be result of concatinating "first and last name". So I have to do this in initial user registration record but since I'm not the expert, I'm afraid to use "custom insert" funcion. Searching the forum I found this topic :
http://forums.yessoftware.com/posts.php?post_id=64481
, and at the end I got idea how to do it but I don't know exactly. At the end of the topic there is one proposal from Peter and another one from Walter.
Thank you so much in interest to help me.
Rado
|
|
|
|
Gena
Posts: 591
|
| Posted: 05/16/2009, 9:42 AM |
|
I think easiest way is from post:
Posted: 10/02/2005, 3:25 PM
--------------------------------------------------------------------------------
Äfter dining:"
.....
_________________
Gena |
|
|
|
rado
Posts: 221
|
| Posted: 05/16/2009, 9:47 AM |
|
Thanks Gena for your opinion.
Rado
|
|
|
|
rado
Posts: 221
|
| Posted: 05/16/2009, 12:14 PM |
|
This is what I did:
I made additional control "Fullname" as a Hidden Field on the RecordForm, and created Event On Validate for control "Fullname" :
//emps_Fullname_OnValidate @89-F792A0C9
function emps_Fullname_OnValidate(& $sender)
{
$emps_Fullname_OnValidate = true;
$Component = & $sender;
$Container = & CCGetParentContainer($sender);
global $emps; //Compatibility
//End emps_Fullname_OnValidate
//Custom Code @90-2A29BDB7
// -------------------------
global $emps;
// Write your own code here.
$emps-> FullName ->SetValue($emps->first_name->GetValue() .' '. $emps->last_name->GetValue() );
// ------------------------- //End Custom Code
//Close emps_Fullname_OnValidate @89-DAD2B614
return $emps_Fullname_OnValidate;
}
//End Close emps_Fullname_OnValidate
And I when I run registration I got:
"Fatal error: Call to a member function on a non-object in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\kw4rent_intranet\EmpsRecord_events.php on line 112"
The line 112 is "$emps-> FullName ->SetValue($emps->first_name->GetValue() .' '. $emps->last_name->GetValue() ); "
Any idea what is wrong!!
Thanks,
Rado
|
|
|
|
Waspman
Posts: 948
|
| Posted: 05/16/2009, 12:20 PM |
|
in Before insert...
$Container->Fullname->SetValue($Container->first_name->GetValue()." ".$Container->last_name->GetValue());
T
_________________
http://www.waspmedia.co.uk |
|
|
|
rado
Posts: 221
|
| Posted: 05/16/2009, 12:29 PM |
|
Yes I have a hidden field called Fullname and based on your sugestion I added in "before Insert" for record a custom code:
$Container->Fullname->SetValue($Container->first_name->GetValue()." ".$Container->last_name->GetValue());
and still getting same error pointing me to the same line.
Thanks for reply.
Rado
|
|
|
|
Waspman
Posts: 948
|
| Posted: 05/17/2009, 2:29 AM |
|
non object?
_________________
http://www.waspmedia.co.uk |
|
|
|
rado
Posts: 221
|
| Posted: 05/17/2009, 6:51 AM |
|
Yes, this error:
"Fatal error: Call to a member function on a non-object in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\kw4rent_intranet\EmpsRecord_events.php on line 112"
Thanks
|
|
|
|
melvyn
Posts: 333
|
| Posted: 05/17/2009, 8:10 AM |
|
Well, I arrived!
Watching your code I understand this:
- The entire record form is named emps.
- One field inside is named Fullname.
You have this code:
Quote rado://emps_Fullname_OnValidate @89-F792A0C9
{
$emps_Fullname_OnValidate = true;
$Component = & $sender;
$Container = & CCGetParentContainer($sender);
It do not work and will not work because your event "on validate" is fired over the hidden field only. That's the scope of that's event. So, when you use $Container->X you're refering to the object X inside the container. In this case the container is only the field "Fullname" with no childs.
Your code will work if you place it in the event OnValidate (for the entire record). So $Container is the record and $Container->Fullname is a child of that record (while right $Container is Fullname, not the entire record form).
Try:
$Container->SetValue($emps->first_name->GetValue() .' '. $emps->last_name->GetValue() );
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
melvyn
Posts: 333
|
| Posted: 05/17/2009, 8:28 AM |
|
Anyways this is going further, since you want to solve another problem, which is a single solution.
Your original goal is concatenate the two fileds in order to use them in a listbox. Please forget all the previous post and go over concat_ws.
See the screenshot below:
http://purocodigo.com/melvyn/issues/vqb_concat_ws_rado.jpg
In the above shot a new field is defined. You only need that in the form where you're going to use the listbox. The above generates the SQL below:
http://purocodigo.com/melvyn/issues/vqb_concat_ws_rado_sql.jpg
Easy and elegant 
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
rado
Posts: 221
|
| Posted: 05/17/2009, 9:28 AM |
|
Thanks Melvyn a lot,
Since this is the request for a single text box (my grid has already select statement), how I can run sql statement in something like "before show" event for "full_name" textbox control. Right now if I add your suggestion in my select for grid it doesn't work. This is the statement:
SELECT
tasks.*,
project_name,
priority_name,
concat_ws(" - ",user_first_name,user_last_name) AS FullName
FROM
((tasks LEFT JOIN task_priorities ON tasks.priority_id = task_priorities.priority_id) LEFT JOIN task_projectsON
tasks.project_id = task_projects.project_id) LEFT JOIN users ON
tasks.user_id_assign_by = users.user_id
WHERE
users.user_id = {user_id} AND
tasks.task_id = {task_id}
Again I would like to run this sql statement in before_show event for "full_name" textbox control:
===========================================================================
SELECT *, concat_ws(" - ",user_first_name,user_last_name) AS FullName
FROM users
WHERE user_id={user_id}
===========================================================================
but I don't know which action to use. If it's custom code oculd you give me example how it suppose to look like.
Thanks, this is really BIG help for me.
Rado
|
|
|
|
melvyn
Posts: 333
|
| Posted: 05/17/2009, 9:54 AM |
|
No.
Forget the events. Forge the before show, on validate and everything from there.
Since you have a new filed name (called FullName) you don't need any events. Simply: point your field to use that name.
I guess you're doing that in your listbox record form. Forget the events. Go to your listbox control, click on it, go to the properties on the bottom right, point at Text Column and select from the fields the new one FullName.
You don't need an event.
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
melvyn
Posts: 333
|
| Posted: 05/17/2009, 11:14 AM |
|
Quote rado:SELECT
tasks.*, project_name, priority_name, concat_ws(" - ",user_first_name,user_last_name) AS FullName
FROM
((tasks LEFT JOIN task_priorities ON tasks.priority_id = task_priorities.priority_id)
LEFT JOIN task_projectsON
tasks.project_id = task_projects.project_id) LEFT JOIN users
ON tasks.user_id_assign_by = users.user_id
WHERE
users.user_id = {user_id} AND
tasks.task_id = {task_id}
Check task_projectsON all together and replace with task_projects ON . There an space missed there. Don't work because error in sql. Anyways, try the another solution.
_________________
Melvyn Perez
Puro Codigo
http://purocodigo.com |
|
|
|
rado
Posts: 221
|
| Posted: 05/17/2009, 1:38 PM |
|
It's typo when I paste text into this body. Original query is correct, however in the mean time I got another place ( in the label of my grid) where I need "FullName" (joined first and last name). So this things getting more complicated since my record in this case contains more tables then just one (tasks) and when I finish building of query I had to switch to Custom insert since the publishing of page complains. So it looks like that I'm getting into same situation like person from this post:
http://forums.yessoftware.com/posts.php?post_id=64481
Thanks Rado
|
|
|
|
rado
Posts: 221
|
| Posted: 05/17/2009, 3:14 PM |
|
Hi,
I removed "concat_ws(" - ",user_first_name,user_last_name) AS FullName...." statement from my sql for record and move it to "Data Source" in the property of my listbox where I changed Data Source Type to "SQL" instead of "Table". Once I build query I tested it and got new field "FullName". SO the query is OK. I moved back to property of listbox and set "Text Column" to FullName (available from drop-down list), published the page and unfortunately the listbox was empty.
Where could be the problem since the query is 100% correct?
Rado
|
|
|
|
