pr3mium
Posts: 31
|
| Posted: 02/05/2004, 5:02 AM |
|
I have a table of employees, where their proffessions, locations, etc are defined with prof_id, loc_id, etc.
I made a grid using the main table. The employees' names come from another table.
An example line from "before show row" event on a grid is like that:
Personal.emp_code.value = CCDLookUp("PA04003","emp_view","PA04002='" & Form.emp_code_id.value & "'",DBdata)
I have a hidden employee code value on each row by what I query the required information from another table. Grid shows the information correctly, but I don't know how to configure the search, so that it would search by the value of employee name (in other table) not by the value of its code?
I'd like to do something like:
SELECT emp_name FROM table2 WHERE emp_code = " & form.emp_code.value & "
I hope I made the point - not very good at expressing myself in English.
Another question is, that how to do the same thing with sorters, so that they wouldn't sort by the emp_code, but by the emp_name in another table that corresponds to the emp_code.
Any help would be appreciated...
Thanks in advance...
|
 |
 |
Nicole
Posts: 586
|
| Posted: 02/05/2004, 6:37 AM |
|
Hello,
Use CCS Visual Query Builder to build the source query of the Grid form that join three tables and includes all the fields you want to show on the form, search and sort on. This way let you avoid event coding
_________________
Regards,
Nicole |
|
|
|
peterr
Posts: 5971
|
| Posted: 02/05/2004, 5:52 PM |
|
Here is some information on how to specify multiple tables for your Grid, so that you don't need to use any events:
http://docs.codecharge.com/studio/html/QuickStart/Creat...eryBuilder.html
http://docs.codecharge.com/studio/html/QuickStart/Creat...eryBuilder.html
I also recommend that you go through this whole tutorial to learn how to work with CCS:
http://docs.codecharge.com/studio/html/QuickStart/Creat...eDirectory.html
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
pr3mium
Posts: 31
|
| Posted: 02/05/2004, 10:58 PM |
|
Thanks, I got the point now and the grid was a success. However, there's some kind of anomaly.
I used the query builder to build the grid. I have a employee table, where each employee's proffession, name, structure, office, etc are defined by ID from other tables. OK, here's the SQL query that CCS generated:
SELECT *
FROM emp_view INNER JOIN ((((Personal INNER JOIN Personal_prof ON
Personal.prof_id = Personal_prof.prof_id) INNER JOIN Personal_haldus ON
Personal.haldus_id = Personal_haldus.haldus_id) INNER JOIN Personal_office ON
Personal.office_id = Personal_office.office_id) INNER JOIN Personal_struct ON
Personal.struct_id = Personal_struct.struct_id) ON
emp_view.PA04002 = Personal.emp_code
WHERE PA04003 LIKE '%{s_PA04003}%'
AND office LIKE '%{s_office}%'
AND haldus LIKE '%{s_haldus}%'
AND struct LIKE '%{s_struct}%'
AND prof LIKE '%{s_prof}%'
AND Personal.phone LIKE '%{s_Personal_phone}%'
AND cell LIKE '%{s_cell}%'
ORDER BY pri
The grid by above query results names (PA04003), haldus, struct, cell OK, but it somehow doesn't show professions (prof), phone and office.
What might be wrong?
|
|
|
|
pr3mium
Posts: 31
|
| Posted: 02/05/2004, 11:03 PM |
|
Oh, I forgot to mention that the above SQL query shows everything correctly, when I click "Review data" button in Data Source window, but it doesn't show the mentioned fields on the grid.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 02/05/2004, 11:08 PM |
|
Try changing the "Data Type" property of these fields. Possibly it is set to Integer but should be Text or vice versa (should match the database).
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
pr3mium
Posts: 31
|
| Posted: 02/05/2004, 11:17 PM |
|
I checked the data types both in database and the fields in CCS - they seem to be OK. I even played with them, but the grid didn't care of the changes.
I also discovered that the grid contains 101 records, while the table has 171.
I guess the joins have ignored some rows.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 02/05/2004, 11:28 PM |
|
If joins have ignored some rows then you definitely need to use Left Joins (sometimes Right). Double-click n Visual Query Builder on the line the connects one table to another, then specify that you want to include all records from one of the two tables.
You can also review the Data Source/Query for other examples in CCS. For example the Employee Directory and Task Manager. Both of them use multi-table queries on the main/default page.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
pr3mium
Posts: 31
|
| Posted: 02/06/2004, 12:34 AM |
|
Thanks Peterr!
Hope you understand the rookie 
OK, I had some trouble solving this one, but I found that some of the employees have null values in haldus or struct fields, so this messed things up a bit, I added null rows in primary key tables and replaced <null> values in main table with proper ID-s and now the grid shows everything fine.
BUT, grid still doesn't show phone numbers. If I search for phone number, it finds the correct rows, but it just doesn't show it. I see the phone number in query results. By the way, the phone number is in the main table and it's not joined from any other table. It does show the cellular number, that is the next column of phone number. If I replace the phone label's datasource with other random field, it still doesn't show - strange...
|
|
|
|
peterr
Posts: 5971
|
| Posted: 02/06/2004, 4:07 PM |
|
What is the data type of the Phone field in your database? Is it text?
Is Data Type in CCS also specified as Text?
Otherwise, possibly try deleting the Label control used for this, then add it again. Or try adding the phone label to another Grid column (next to other controls) to see if maybe something's wrong with that Grid column.
I'm not really sure what else to check since I haven't see such problem.
Since this is not a support forum, I actually recommend contacting our support and they can take a closer look at your specific case. The link is below.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
pr3mium
Posts: 31
|
| Posted: 02/08/2004, 10:08 PM |
|
Well, I haven't solved the phone number thingy, yet. I've tried re-adding the label, both the label and database field are text and I created several other grids, but still it had something against phone numbers. Even if I changed the source field within same database table, it didn't show up. But if I changed the source field to some other table, it did show.
Anyway, because I was in hurry, I added a CCDLookUp code in "before show row" event and my problems are over. Of course, it's not the solution, so if any ideas pop up, let them out in this thread
|
|
|
|
Mama
|
| Posted: 02/16/2004, 11:42 PM |
|
Quote pr3mium:
I have a table of employees, where their proffessions, locations, etc are defined with prof_id, loc_id, etc.
I made a grid using the main table. The employees' names come from another table.
An example line from "before show row" event on a grid is like that:
Personal.emp_code.value = CCDLookUp("PA04003","emp_view","PA04002='" & Form.emp_code_id.value & "'",DBdata)
I have a hidden employee code value on each row by what I query the required information from another table. Grid shows the information correctly, but I don't know how to configure the search, so that it would search by the value of employee name (in other table) not by the value of its code?
I'd like to do something like:
SELECT emp_name FROM table2 WHERE emp_code = " & form.emp_code.value & "
I hope I made the point - not very good at expressing myself in English.
Another question is, that how to do the same thing with sorters, so that they wouldn't sort by the emp_code, but by the emp_name in another table that corresponds to the emp_code.
Any help would be appreciated...
Thanks in advance...
Ya here's my advice..... use the multi-table joins querries. Is ure DB in Access? If it use use the querry generator to get the reqd values...it's too tough for me to tell anything wihtout lookin at ure database. Let me know if u need any further assistance.
Mama
|
|
|
|
|
