Subject Re: [IBO] How to avoid lookup table from fetching all records ?
Author Geraldo Lopes de Souza
"Harald Klomann" <nibler@...> escreveu na mensagem
news:3AD31F13.96BC95DA@......
> Geraldo Lopes de Souza wrote:
>
> > I know I could establish a master detail relationship to achieve the
same
> > result and still be capable of making search. The problem is that the
search
> > would be based on EXISTS clause which isn't very fast.
> >
> > Thanks in advance
> >
> > Geraldo Lopes de Souza
>
> Geraldo,
>
> I don�t really understand exactly what you want to do. Having a lookup
dataset
> without having a lookupcombo bind to it doesn�t make sense to me.
>
> If you just want to display and search via a lookup field, but edit the ID
field,
> why don�t you use a inline select ? That would match your needs exactly,
so far
> I understand.
>
> Like:
>
> select a,b,your_ID, (select Your_Description from YourLookupTable L where
L.ID=T.ID)
> from YourTable T
>
> So you don�t need a lookup dataset, but you see your description, you can
edit your ID
> and you can search on your description/ID.
>
> Is it this you want ?
>
> Harald
>
My main TIB_Query is a query like this :

SELECT EXAMES_FEZES.*,
ATENDIMENTO_LABORATORIO.ATL_REGISTRO,
ATENDIMENTO_LABORATORIO.ATL_CONTROLE,
ATENDIMENTO_LABORATORIO.ATL_PACIENTE_NOME,
ATENDIMENTO_LABORATORIO.ATL_DT_ATENDIMENTO,
ATENDIMENTO_LABORATORIO.ATL_DT_PREVISAO_ENTREGA,
ATENDIMENTO_LABORATORIO.ATL_OBSERVACOES
FROM EXAMES_FEZES
JOIN ATENDIMENTO_LABORATORIO ON
(ATENDIMENTO_LABORATORIO.ATL_ID=EXAMES_FEZES.EXF_ATENDIMENTO_ID)
FOR UPDATE

Note that I'm editing EXAME_FEZES but it has a field called
EXAMES_FEZES.EXF_ATENDIMENTO_ID which is the key that join the two tables.
When the user is supposed to add arecord to to EXAME_FEZES he already knows
the id of ATENDIMENTO_LABORATORIO so I don't need a lookup combo to display
the entire table . I was very happy when my lookup relationship worked
without lookup combo. I was assuming that the entire lookup table would not
be fetched because there wasn't the necessity of browsing it (by not using
LookupCombo).

An inline select has the trade off of not allowing the user to search by the
lookup fields. The join works well if the data is already digited, and
you're browsing it. In my case the user needs to insert and update and this
is the reason that I'm using a lookup relationship.

So I have a lookup query like this :

SELECT ATENDIMENTO_LABORATORIO.ATL_HOSPITAL,
ATENDIMENTO_LABORATORIO.ATL_ID,
ATENDIMENTO_LABORATORIO.ATL_NUMERO,
ATENDIMENTO_LABORATORIO.ATL_REGISTRO,
ATENDIMENTO_LABORATORIO.ATL_CONTROLE,
ATENDIMENTO_LABORATORIO.ATL_PACIENTE_NOME,
ATENDIMENTO_LABORATORIO.ATL_DT_ATENDIMENTO,
ATENDIMENTO_LABORATORIO.ATL_DT_PREVISAO_ENTREGA,
ATENDIMENTO_LABORATORIO.ATL_OBSERVACOES
FROM ATENDIMENTO_LABORATORIO

This was supposed to work like calculated fields : The user change the
EXAME_FEZES.EXF_ATENDIMENTO_ID and the lookup in background update the
fields.... Everything is working, except for the fetch of the entire lookup
table.

Thanks for helping me

Geraldo Lopes de Souza