Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

This topic is 7421 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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. smile.gif

Posted

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

Posted

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

Posted

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

This topic is 7421 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.