Defining criterion on values in a relation

[Esmo2000]

Hi everyone, I've run into quite a dilemha! (As per usual ^^) and I was wondering if anyone here had an answer (or even knew which forum this should go in ;-P)

Okay, basically, i have a database with a bunch of invoices. Each invoice is associated to multiple payments. Each payment has a payment date on it. In the main database, there is a calculation which calculates a balance. Basically, it takes the invoice amount, and subtracts the sum of all the associated payments.

Now here is the kicker! I want to take a sum from only a specific date range in the payment database! And i want the user to be able these constraints every time they do a search. Firstly, I don't even know how I would manage to get two dates for searching in find mode (because I can't really get globals into find mode and enter information into them).

Secondly, I don't know how to only sum up partial payments from a specific time period.

Does anybody have any suggestions? MUCH APPRECIATED!

-Jim

[Fenton]

First, you don't need to enter global fields in Find mode; because you can safely enter them in Browse mode, then pass the data to the relevant date field in Find mode. For a range:

Insert Calculated Result [ Date_, DateToText(DateStart_g) & "..." & DateToText(DateEnd_g) ]

A Summary total field in a Footer part would show the total payements. A Summary (running total) in the Body would show them incrementing.

You could capture that total into a global, then show it somewhere else, as part of a Find Payments Date Range script. You'd have build the interface, and initialize that field.

If you want to show this in a portal, by entering the 2 dates, then it's more complex. If you have version 7, then it's not that bad. You can use multiple criteria in a relationship.

PersonID = PersonID AND

DateStart_g => DatePayment AND

DateEnd_g <= DatePayment

Otherwise you'd have to build a concatenated PersonID & Date key in both files (multi-line with the PersonID and an incrementing global date on the left side, simple concatenated PersonID & Date on the right).

It would be limited to a certain number of days (about 170?), unless you used the Smart Ranges technique, or a plug-in, due to a limit of the number of functions in a calculation.