Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 7 of 7 total |
Filter on Lookup field |
Mon, May 29 2006 7:56 AM | Permanent Link |
Michael Urban | 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 |
Mon, May 29 2006 9:31 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, May 29 2006 12:08 PM | Permanent Link |
Michael Urban | 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 |
Mon, May 29 2006 2:14 PM | Permanent Link |
"Robert" | "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 |
Tue, May 30 2006 1:49 AM | Permanent Link |
"Bobby Gallagher" | 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 > |
Tue, May 30 2006 5:51 AM | Permanent Link |
"Robert" | "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 >> > > |
Tue, May 30 2006 10:17 AM | Permanent Link |
"Robert" | "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. |
This web page was last updated on Tuesday, April 23, 2024 at 08:10 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |