Cor Ligthert [MVP]
1/17/2008 6:48:00 PM
Jim,
Why not 2 resultsets one with your invoice grouped by vendNo, InvNO and one
for the payments?
Cor
"Jim" <nomail@dev.null> schreef in bericht
news:13otrmpbb72qbff@corp.supernews.com...
> Cor,
>
>> Your datatables needs in your datasets needs to have relation. Now the
>> datasources of your childtables become the relations.
>> In fact is that all you need to do.
> Yes, that takes care of showing the Invoices for the selected Vendor (for
> example), but here is the problem: The vendor grid (parent) is based on
> the same DB table as the invoice grid (the children), but the vendor grid
> is a summary view and the invoice grid is a "non-summary" view.
>
> The DB tables are:
> Invoice (VendNo, InvNo, DueDate, Amount)
> Payment (VendNo, InvNo, SeqNo, PayDate, PayAmount)
>
> The Vendor Grid should contain the equivalent of this simplified SQL
> statement:
> SELECT Invoice.VendNo, SUM(Invoice.BalDue) as TotalDue,
> SUM(Payment.PayAmount) as TotalPay
> FROM Invoice LEFT OUTER JOIN Payment ON
> Invoice.VendNo = Payment.VendNo AND
> Invoice.InvNo = Payment.InvNo
> GROUP BY Invoice.VendNo
>
> I could of course do this as a view on the DB server and use that for the
> vendor grid and use the Invoice table for the Invoice grid, but how do you
> keep them in sync when payments are added / deleted or modified from the
> invoices? Would I have to persist the data to the DB and rebind the
> grids? It would probably be too slow.
>
> Thanks,
> Jim
>