Wow!

Thanks alot Helen. It never crossed my mind to try a subquery instead of a
LEFT JOIN.

Also, prior to this the use of KeyDescLinks had not been clear. Thanks once
again for shedding light into this void that is my head.

James

----- Original Message -----
From: Helen Borrie
To: IBObjects@yahoogroups.com
Sent: Wednesday, March 22, 2006 4:53 PM
Subject: Re: [IBO] LookupCombo On Grid + JOIN


At 12:14 AM 23/03/2006, you wrote:
>Hi!
>
>I am new to this newsgroup and to IBObjects. I have been struggling
>with LookpCombo on a grid, but after searching the NG archives, I
>finally got it to work.
>
>I am now trying to move a step further so that that the combo can
>display a non-primary key. Here is what I mean:
>
>I have two tables:
>
>Contacts (CID, CTITLE, FNAME, LNAME, SEX, EMAIL ... CATID) as qryCont
>Categories (CATID, CATNAME) as qryCat
>
>If I use CATID for the KeyLinks and set KeySource to qryCat, then
>drop the combo on a grid, it works like clock-work... this however
>means that the grid displays CATID and CATNAME is only visible when
>the combo is dropped-down.
>
>A previous post sighted that the DisplayField must also be included
>in both sides of the KeyLink/Lookup, so I tried to set qryCont to:
>
>
>
>The dataset comes as readonly and I think this is where the problem
>lies so that even when I change the DisplayField of the LookupCombo
>it still fails.
>
>I know the field catname in the dataset is a "virtual" field but is
>it possible to allow "virtual" changes in catname to propage to
>catid? I've seen behaviour like that somewhere... just wondering. Any ideas?

Well, you have a few things mixed up here.

SELECT contacts.*, categories.catname
FROM contacts
LEFT JOIN categories ON contacts.catid = categories.catid
FOR UPDATE

is no good for a scrolling dataset, since it fetches rows one by
one. Take out FOR UPDATE. You don't need it anywhere in IBO. If
you want a one-by-one scrolling dataset, use tib_cursor.

You don't want a join here at all.

So let's get the Keysource set right first. The Keysource set is the
main set, NOT the lookup set (you have these wrong). The lookup set
names the datasource of the main set as its Keysource. The Keylinks
link a unique key in the lookup set to a matching field in the main set.

First, the query for the main set (never use select x.* PLEASE).

select
contacts.CID,
contacts.CTITLE,
contacts.FNAME,
contacts.LNAME,
contacts.SEX,
contacts.EMAIL,
contacts.CATID,
(select categories.CATNAME
from categories
where categories.CATID = contacts.CATID) as CategoryName
from contacts

Set the Keylinks for this query (qryCont) to CID; set RequestLive
true and this will be an updatable query.

Now, the category query qryCat is
select
CATID, CATNAME from categories

Set the Keylinks of qryCat to
category.CATID=contacts.CATID
Set the Keysource to dsCont, i.e. qryCont's datasource.
Set the KeyDescLinks of qryCat to
categories.CATNAME=CategoryName

In qryCat, set the Visible attribute of CATID to False.

You must make sure of two things:
1. that the matching key in the parent set has a value that is
present in the lookup set. NULL is not a value, so it will break the
relationship. If you are using a default of empty string or a blank
character, then you must have a record in categories for the empty
string or a blank character and a corresponding CatName for it.
2. that you have no duplicate keys in the lookup set.

IBO does the rest.

Helen




___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !



SPONSORED LINKS Membership database software Oracle database software Inventory database software
Database management software Database development software Resume database software


------------------------------------------------------------------------------
YAHOO! GROUPS LINKS

a.. Visit your group "IBObjects" on the web.

b.. To unsubscribe from this group, send an email to:
IBObjects-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


------------------------------------------------------------------------------



[Non-text portions of this message have been removed]