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: Need suggestions on layout to report/pivot
    ... Filtering will prevent a user having to search for anything. ... If I'm reading your comment correctly, my number of rows would grow tremendously as I already have over 1000 rows for one month's invoice data. ... Just enter your payments in exactly the same way as your Invoices, just make the Amount and the Hours negative. ... Using the dropdown on Inv No, selecting any Invoice will filter the table to show all of the Debit and Credit amounts against Invoice, and in B1 the Balance of Amount will show, and in D1 the Balance of Hours. ...
    (microsoft.public.excel.worksheet.functions)