At 02:20 PM 15/03/2006, you wrote:

> > > > Something in this manner:
> > > >
> > > > mainquery:
> > > > sql='select gl, sl, dramount, cramount from rp_tbsl(:period)';
> > > >
> > > > lookupquery:
> > > > sql='select gl, description from accounts';
> > > > keylinks='accounts.gl=?????? // what goes here?
> > > > keysource=ib_datasource1
> > > >
> > > > ib_datasource1:
> > > > dataset=mainquery;
> > > >
> > >
> > >If this kind of lookup relationship is not possible, what is the
>IBO
> > >way of achieving this effect?
> >
> > It's more a question of whether it makes any logical sense.
> >
> > The purpose of the keylinks relationship is to provide update
>values
> > for the linked key in the keysource. But here ---
> >
> > 1) your main set is not an updatable dataset, it is a virtual
> > dataset (doesn't exist in the database)
> > 2) the linked value is the closest thing you have got to a keylink
> > in the main set. What are you trying to do? - use the lookup value
> > to change the gl value in a table somewhere? Do you have
> > parameterised EditSQL, InsertSQL and DeleteSQL statements in main
> > dataset to achieve this? And, if you have, do you understand how
> > this will affect the validity of your virtual output?
> >
> > So it would assist greatly if you would just explain exactly what
> > you're trying to achieve here.
> >
> > Helen
>
>Thank you Helen.
>
>There are no edits involved in these queries. In fact, mainquery is a
>tib_cursor that pulls data from an sp intended for a report. The
>actual query is --
> select gl, sl, description, begbal, curdr, curcr, endbal
> from rp_tbsl(:period)
>-- description here is pulled from the sl table (not gl). Since I
>need description from the gl table as well, might have written
>mainquery.sql like so --
> select gl, (select g.description from gltable g where g.gl=r.gl)
> as gl_description, description, begbal, curdr, curcr, endbal
> from rp_tbsl(:period) r
>-- it works, except that I have just pulled a 40 char long column
>repeatedly for the total number of rows returned. I said to myself a
>secondary scrollable query keylinked/keysourced to mainquery might be
>more efficient, but I realized that such is allowed only for real
>tables in the db. At the moment, I am using these two components but
>have to manually locate the gl code from the gltable.
>
>I have also considered a master-detail relationship, but then there
>is no unique identifier returned by rp_tbsl sp.
>
>Can you say, please, if I am on the right track?

Well, clearly, if there is no editing involved then a drop-down
selector is no good to you at all.

Master-detail wouldn't work, since the "master" in that relationship
is the table you want to look up. That would then require you to
change the SP so that it would take the gl as an additional
input; and each scroll of the "lookup" table would invoke the SP for
that gl code.

I still don't see why you want the gl description in the UI. If you
want it in the report, it should be in the dataset. Some report
tools let you define run-time lookups, though.

If you want to avoid a run-time hit like "select description from
accounts where gl = :gl" (which is what is behind the
tib_lookupcombo) then pull all the gl codes and descriptions over
into a stringlist before you run the SP for the report, using an
ibcursor. Declare an internal stringlist variable and publish it as
a property, let's say fAccountList and Accountlist respectively.

Initialise fAccountList somewhere, perhaps at FormCreate, and
remember to destroy it somewhere!

At whatever point you need to load fAccountList, call a procedure to do it:

procedure MyDM.LoadAccountList;
var
cr: tib_cursor;

begin
cr := tib_cursor.create(self);
with cr do
try
ib_connection := YourConnection;
ib_transaction := YourTransaction; // I keep a special
"quick-hit" transaction for these uses
SQL.Add('select gl, description from accounts order by gl');
Prepare;
First;
fAccountList.Clear;
while not EOF do
begin
fAccountList.Add(Fields[0].AsString + '=' + Fields[1].AsString;
Next;
end;
finally
Close; // not essential, but won't hurt
Free;
end;
end;

Now you have a structure of strings consisting of NAME=VALUE, which
you can refer to when your report wants the gl description.

e.g.

function GetAccountName(aCode:string): string;
{pass in the gl code asString}
begin
Result := AccountList.Values[aCode];
end;

Even if I still don't clearly understand what the interface is like,
you can use this technique to hook up the stringlist to various
controls and other structures that use stringlists.

Just remember with stringlists that you have to take care of
ownership so that the same Owner that creates them also destroys them.

Helen