At 08:02 PM 07-12-00 -0500, you wrote:

>I'm trying to figure out how to do a lookup that is filtered on a field in a
>different table. To wit: I have a table call BUGS which contains a number of
>fields, two of which are of importance here: COMPANY_ID and
>APPLICATION_ID. The
>COMPANY_ID field points to a company table with two fields: COMPANY_ID and
>COMPANY_NAME. The APPLICATION_ID field points to a table with three fields:
>APPLICATION_ID, COMPANY_ID and APPLICATION_NAME.
>
>I've managed to use the TIB_DBLookup control to show a drop-down list of
>COMPANY names and another to show a list of APPLICATION names. Unfortunatly,
>the list of APPLICATION names contains all of the applications, not just the
>one for the currently selected COMPANY_ID in the BUGS table.
>
>The relevant properties that I currently set are as follows (based on the
>documentation in the help on using DBLookupCombos):
>
>ibqryBug: TIB_Query
> ColumnAttributes.Strings = (
> 'COMPANY_NAME=COMPUTED'
> 'APPLICATION_NAME=COMPUTED')
> DatabaseName = 'dbSupport'
> IB_Connection = ibconSupport
> SQL.Strings = (
> 'SELECT BUG.BUG_ID,'
> ' (select COMPANY.COMPANY_NAME '
> ' from COMPANY '
> ' where COMPANY.COMPANY_ID = BUG.COMPANY_ID) as COMPANY_NAME' +
> ' BUG.COMPANY_ID,'
> ' (select APPLICATION.APPLICATION_NAME '
> ' from APPLICATION '
> ' where APPLICATION.APPLICATION_ID = BUG.APPLICATION_ID) ' +
> ' as APPLICATION_NAME,'
> ' BUG.APPLICATION_ID,'
> ' BUG.RECEIVED_DATE,'
> ' BUG.DESCRIPTION'
> 'FROM BUG')
>
>ibqryApplication: TIB_Query
> DatabaseName = 'dbSupport'
> IB_Connection = ibconSupport
> SQL.Strings = (
> 'SELECT COMPANY_NAME, APPLICATION_NAME, APPLICATION_ID, COMPANY_ID'
> 'FROM APPLICATION'
> 'LEFT JOIN COMPANY'
> 'ON APPLICATION.COMPANY_ID=COMPANY.COMPANY_ID')
> KeyLinks.Strings = (
> 'APPLICATION.APPLICATION_ID=APPLICATION_ID')
> KeyDescLinks.Strings = (
> 'APPLICATION.APPLICATION_NAME=APPLICATION_NAME')
>
>ibluApplication: TIB_LookupCombo
> DataSource = dataSupport.ibdsApplication
> DisplayField = 'APPLICATION_NAME'
>
>The above do show a list of applications in the drop down, but it shows all
>applications rather than only the ones for the current company as selected in
>the BUGS table. Is it possible for me to filter out all applications and only
>show the ones for the relevant company?
>
>Tia,
>Denis

Probably not in any practicable way - there is a limit to how far you can
expect IBO to compensate for a missing table in your database.

You have COMPANY
You have APPLICATION
You have BUG which links COMPANY and APPLICATION, but only if a company has
an application that has a bug. You need an intersection table to link
COMPANY and APPLICATION, something like

create table COMPANYAPPLICATION (
COMPANYAPPLICATION_ID INTEGER,
COMPANY_ID INTEGER,
APPLICATION_ID INTEGER,
CONSTRAINT PK_COMPANYAPPLICATION PRIMARY KEY(COMPANYAPPLICATION_ID));

This way, you can make a lookup query that shows all the possible
applications for the selected company and filter the bugs on the basis of
the application selected.

select ca.COMPANYAPPLICATION_ID, ca.APPLICATION_ID, a.APPLICATION_NAME
from COMPANYAPPLICATION ca
join APPLICATION a
on ca.APPLICATION_ID = a.APPLICATION_ID

The WHERE clause for your filter is

FILTER='COMPANYAPPLICATION.COMPANY_ID= :COMPANY_ID'

You could get a set using the Bug table itself as your intersection table,
as long as it was OK only to offer those applications that have bugs
reported for them, i.e.

select b.BUG_ID, B.APPLICATION_ID, a.APPLICATION_NAME
from BUG b
join APPLICATION a
on b.APPLICATION_ID = a.APPLICATION_ID

The WHERE clause for your filter is then

FILTER='BUG.COMPANY_ID= :COMPANY_ID'

I wouldn't be happy with this myself, because I would want my app to be
aware of the possibility that another user was adding a bug for an
application that previously didn't have any bugs.

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________