Hi. How do I define a field that totals the fields in the latest (newest) of multiple records? For example, say I have several records for each of account numbers 1, 2, 3 and 4. Under each account number, I have salesmen A, B and C. What I need, is a total of salesman A's commissions, for example, but only in the latest records that he shows up in. So if salesman A is in records with account numbers 1,2, and 4, then I need the total of his commissions in the latest of those records.

I guess the thing I'm missing is the command that says to look for something in the latest record it shows up in.

Thanks in advance.

Hi, Emery. You'll need a field that is auto-entered to the creation date, and a relationship that sorts descending by that field. The first record returned through that relationship will then be the latest account number.

Alternatively, you could leave out the part about the auto-entered creation date if and only if your account number field is a number field and is an auto-entered serial, because in that case the highest account number will always be the latest. Just sort on that field instead.

J

Thanks, J, for the quick reply. I considered doing this, and I can create relationships based on the salesman's ID number and a corresponding date or counter. But the part I am missing, is how to make that relationship correspond to the latest records, given the latest records may not have the same creation dates or counter values. For example, the latest record for account #1 may have been created last week, whereas the latest for account #2 may have been created yesterday. Have I overlooked something?

thanks.

-Emery

Just make the relationship from SalesmanID to SalesmanID. The date/counter should not be part of the relationship. However, it SHOULD be in the sort order for the relationship (Sort related record by...)

If you relate salesmen to accounts and sort related records (descending) by the creation date, then the matched set will be all accounts assigned to that salesman -- you're used to seeing this -- but this time, the first record will always be the one most recently created, due to the fact that the matched set is being sorted (descending) by the creation date.

J

Got it. Thanks, man!

-Emery