Board index » delphi » Filtering based on LookUp field to a Query

Filtering based on LookUp field to a Query

Sorry - I forgot the Title on this message

Dear All

I use D5Pro (updatePack1), BDE 5.01, TableType: Paradox

I have a - somewhat - complicated problem:

Following components are in use:

Orderstable : TTable (Orders.DB) + datasource component
Itemstable  : TTable (Items.DB) + datasource componnent
TotalsQuery : TQuery
Dbgrid1     : TDBGrid (connected to Orderstable)
DbGrid2     : TDBGrid (Connected to Itemstable)
Orderstable has following fields (fkData):

OrderID (ftAutoInc -- ixPrimary), OrderDate (ftDate)

--

Itemstable has following fields (fkData):

ItemID (ftAutoInc -- ixPrimary), OrderID (ftInteger -- Sec index), Price
(ftCurrency)

--

TotalsQuery has following SQL :

SELECT OrderID, SUM(Price) AS OrderTotal FROM "Items.DB"
GROUP BY OrderID

--

There are Master/Detail relations between Orderstable and Itemstable
(Related fields are OrderID)

--

Orderstable has an additional field (fkLookUp) : OrderTotal

(LookUpDataset : TotalsQuery,
KeyField : OrderID,
LookUpField : OrderID,  {The "Group By" field, and therefore unique in the
Query dataset}
ResultField : OrderTotal)

I need to filter Orderstable depending on the lookupfield OrderTotal:

TForm1.OrderstableFilterRecord (Dataset : TDataset; var Accept : Boolean);
begin
  Accept:=(OrderstableOrderTotal.Value>100);
end;

It doesn't work - Why not?

When I enter a new Item or modify an existing item (price) in the DBGrid2
connected to Itemstable,
the new value are not reflected in the DBGrid1 connected to Orderstable
(unless I close the application
and reopen it)

I tried to call "Orderstable.Refresh", "TotalsQuery.Close ..Open",
"Dbgrid1.Repaint" in then
Itemstable.OnAfterPost event
--It doesnt work.  - why not?

I Tried to look in "Demos" dir to find a database application that could
help me - unfortunately, I didn't find any

any suggestions?
(for performance reasons, I prefer SQL rather than traversing the table to
calculate totals)

Best regards

Anders Balslev

By the way -- Where can I find "huge amount" of sample projects containing
all kinds of
database application (i.e. Child fields, Aggregate fields, BlobFields, More
complicated SQL's
and stuff like that)

 

Re:Filtering based on LookUp field to a Query


Quote
>any suggestions?
>(for performance reasons, I prefer SQL rather than traversing the table to
>calculate totals)

I believe the problem is that the lookup value is not available when the
OnfilterRecord event fires.  Write code in the OnfilterRecord event to do a
locate for the OrderTotal record you are looking for.
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads