Hi all,

I need to filter on a lookup field, but using the tables "Filter" property fails as a
lookup field is no real physical field.

Basically what I need to do is to only show records of one table for which there is a
linked record in another table. The table I am showing has no indication if there is a
related record, so I have to look it up.
In other words: I need to show only masters for which there is a detail record.

Anyone got an idea how to achieve this - short of creating a SQL query?

thanks,
Michael

Michael


If there's no field to filter against you can't filter! There are two possibilities I can come up with:

1. Use the OnFilter event - this will be slow
2. Use a query to create a list of valid keys in the target table and then create a filter using that.

2 is an approach I have used several times and it works reasonably well. What you do is:

a) create a query with a join linking the other table and only containing the key for the table you want to filter
b) loop through that table appending the key (quoted if its a string field) to a string and separated by a comma
c) create a query that is roughly KEYFIELD IN (keylist)

Off it goes.


Roy Lambert

Roy,

interesting approach. I think I will try this.

I hoped there would be a way to make filters work with lookup (or computed) fields, but
this might be as good - more work though...

Michael

"Michael Urban" <murban@urbanitconsulting.de> wrote in message
news:1871F6E6-23AA-454F-A917-9076D5AB8F52@news.elevatesoft.com...
> In other words: I need to show only masters for which there is a detail
> record.
>

In OnFilterRecord, do a Lookup on the detail table, and filter if not found.

Robert

Hi

There must be some field in the main table to act as the key / reference for
the lookup - could you not filter on field

Regards

Bobby

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:3066326B-F47D-4126-9171-C4E0E1BF4D12@news.elevatesoft.com...
> Michael
>
>
> If there's no field to filter against you can't filter! There are two
> possibilities I can come up with:
>
> 1. Use the OnFilter event - this will be slow
> 2. Use a query to create a list of valid keys in the target table and then
> create a filter using that.
>
> 2 is an approach I have used several times and it works reasonably well.
> What you do is:
>
> a) create a query with a join linking the other table and only containing
> the key for the table you want to filter
> b) loop through that table appending the key (quoted if its a string
> field) to a string and separated by a comma
> c) create a query that is roughly KEYFIELD IN (keylist)
>
> Off it goes.
>
>
> Roy Lambert
>

"Bobby Gallagher" <bobgal@computech.ie> wrote in message
news:56AAE5CA-52AF-4212-AEF6-A8624B44207D@news.elevatesoft.com...
> Hi
>
> There must be some field in the main table to act as the key / reference
> for the lookup - could you not filter on field
>

OP is looking to filter items that don't exist in the lookup field. You need
to do a find on the lookup field to see if the item is there.

Robert

> Regards
>
> Bobby
>
> "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
> news:3066326B-F47D-4126-9171-C4E0E1BF4D12@news.elevatesoft.com...
>> Michael
>>
>>
>> If there's no field to filter against you can't filter! There are two
>> possibilities I can come up with:
>>
>> 1. Use the OnFilter event - this will be slow
>> 2. Use a query to create a list of valid keys in the target table and
>> then create a filter using that.
>>
>> 2 is an approach I have used several times and it works reasonably well.
>> What you do is:
>>
>> a) create a query with a join linking the other table and only containing
>> the key for the table you want to filter
>> b) loop through that table appending the key (quoted if its a string
>> field) to a string and separated by a comma
>> c) create a query that is roughly KEYFIELD IN (keylist)
>>
>> Off it goes.
>>
>>
>> Roy Lambert
>>
>
>

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:CEA69703-4A3B-4DFE-9B16-F0DBB42863CA@news.elevatesoft.com...
>
> "Bobby Gallagher" <bobgal@computech.ie> wrote in message
> news:56AAE5CA-52AF-4212-AEF6-A8624B44207D@news.elevatesoft.com...
>> Hi
>>
>> There must be some field in the main table to act as the key / reference
>> for the lookup - could you not filter on field
>>
>
> OP is looking to filter items that don't exist in the lookup field. You
> need to do a find on the lookup field to see if the item is there.
>

Read "don't exist in the lookup TABLE". sorry. You need to access both
tables to determine if the item passes the filter. The SQL would be

SELECT * FROM MASTER
JOIN DETAIL ON DETAIL.ID = MASTER.SOMEFIELD

which would not select items that don't have a corresponding entry in the
detail table. The approach using ttable's OnFilterRecord would be

Accept := DetailTable.Locate('ID', MasterTableSomeField.Value, []);

As usual, it is advisable to use a tTable different than the one you are
using for the lookups.

R.