Re: Business objects, subset of collection

You got my attention earlier with your statements, but seem unable to
apply your theories to the problem at hand.  Would you mind backing up
your theories with some advice on how to apply them to this scenario:

The app is for scheduling payments of invoices and has three grids
(regular DataGridViews):
A Vendor grid showing a summary view of each vendor (with total Due,
etc.)
An Invoice grid listing all invoices for the selected vendor
(including a total amount of payments scheduled for each invoice)
A Payment grid listing all payments scheduled for the selected
Invoice.

Any payments added / removed or modifed in the payment grid (or mass-
updated via code) needs to be reflected instantly in the other two
grids.

Note that 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 detail 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? (too slow)

After adding a payment, you obviously need to refresh the grids. How
many millis does this take? How many millis does it take to execute
the SQL? What is your time constraint?

//frebe
.

Relevant Pages

  • Re: Business objects, subset of collection
    ... The app is for scheduling payments of invoices and has three grids ... A Vendor grid showing a summary view of each vendor (with total Due, ... An Invoice grid listing all invoices for the selected vendor ...
    (comp.object)
  • Re: Business objects, subset of collection
    ... A Vendor grid showing a summary view of each vendor (with total Due, ... An Invoice grid listing all invoices for the selected vendor ... A Payment grid listing all payments scheduled for the selected ...
    (comp.object)
  • Re: Business objects, subset of collection
    ... A Vendor grid showing a summary view of each vendor (with total Due, ... An Invoice grid listing all invoices for the selected vendor ... A Payment grid listing all payments scheduled for the selected ...
    (comp.object)
  • Re: Business objects, subset of collection
    ... The most general approach is and most of the abstract action languages used in OOA/D will have a WHERE clause available for any relationship navigation to filter the set of objects accessed. ... FOREACH invoice IN invoiceSet ... when the user wants payments. ... one just needs to substitute "amount<100" in the WHERE clause to deal with another selection criteria in the second fragment above. ...
    (comp.object)
  • Re: report group subtotals incorrect
    ... there are multiple payments of the invoice, ... This text box accumlates the total for the customer over their invoices, ... An alternative idea would be to use a subreport for the payments. ... > tehn totals per group. ...
    (microsoft.public.access.queries)