Lookup for optional fields?

[pistantan]

Hi all,

This may be a really noobish doubt, but still I need to know. I have this table which is something like:
tableCat
-----------
catID, primary key
catName
parentID

Which is something like, if the record doesn't have a parentID (which refers an catID), then the record is a category. If it does have a parentID, then it's a sub category. An example of a few records:

catID catName parentID
------------------------------
1 Vehicle (NULL)
2 Computer (NULL)
3 Books (NULL)
4 Car 1
5 Motorcycle 1
6 Hard-drive 2
(...)

Something like this. The thingy is, I want to put a lookup field for the parent's name, BUT since some of its values are NULL, then it will crash and burn... Is this possible at all? Is there an event I can treat so it works? Is there some hoodoo for it ? Help please, thnx in advance

 

[lespaul]

Do you mean you want a combo box on a form that just displays the CatNames where ParentID is null?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[pistantan]

I just want that when the record does have parentID instead of appearing parentID, to appear the parents name. Like in my example from above, instead of showing:

5 - Motorcycle - 1

To appear:

5 - Motorcycle - Vehicle

It can be on a dbcombo box, dbEdit whatever, I don't really care and I can do that part. My problem is that when it tries to do the lookup for instance the catID 1, it's parentID is null and it pops an error (which is kind of obvious because it'll be looking for null). I want to do the lookup when there is a parentID and just ignore the lookup field when it doesn't. Sorry about my lame english

 

[lespaul]

catID catName parentID
------------------------------
1 Vehicle (NULL)
2 Computer (NULL)
3 Books (NULL)
4 Car 1
5 Motorcycle 1
6 Hard-drive 2

so you are looking for a query like this:

SELECT A.CatID, A.catName, B.catName As ParentName
FROM tablename A
INNER JOIN tablename B on A.CatID = B.CatID
WHERE parentid not null

should return
4 Car Vehicle
5 Motorcycle Vehicle
6 Hard-drive Computer

you may have to tweak the null check in the where clause based on your database SQL requirements, but this should point you in the right direction. You are basically creating another instance of the table and joining it to itself.

HTH

Leslie

 

[pistantan]

Humm, sort of. I may have put the problem the wrong way. Here's what I want:

catID catName parentID
------------------------------
1 Vehicle (NULL)
2 Computer (NULL)
3 Books (NULL)
4 Car Vehicle
5 Motorcycle Vehicle
6 Hard-drive Computer

Something like this... :| With that command it'll ignore the parent Ids and I'll need 2 ADOdataSets or 2 ADOquery, I just want one with a unified result. Does the lookup call the onCalcFields event? How could I treat it if so?

 

[lespaul]

SELECT CatID, CatName, "" From TableName WHERE ParentID is Null
UNION
SELECT A.CatID, A.catName, B.catName As ParentName
FROM tablename A
INNER JOIN tablename B on A.CatID = B.CatID
WHERE parentid not null
ORDER BY 1

will return:

catID   catName       parentID
------------------------------
1       Vehicle       (NULL)
2       Computer      (NULL)
3       Books         (NULL)
4       Car            Vehicle
5       Motorcycle     Vehicle
6       Hard-drive     Computer