Jump to content

Running year total per client


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

Recommended Posts


I have a lot of returning bigger customers and i'd like to populate a field per client with their running total.
Tried doing it with a script:
Find client
Copy total value
Paste that value to their own global field

Now i have to contanty refresch with the script.
Is there a better way to do this? Through a calculation?

The client field is called "Source"
The total field is called "Year total"

Thanks for any help/





Link to comment
Share on other sites

Some more information is needed.  "Bigger customers"...bigger how?  More sales, more total dollar amount of sales, bigger single purchases (is it even sales?)

Based on your limited info, I wonder if this could be done with a one row, one field portal on the layout showing a related field from sales, based on a relationship from customer to sales.  That field would be a calculation field based on the Total sales of that customer.

Also, do you only want a running total for the year, or a rolling 1 year total.  The latter lets you know who is/was the biggest client in the last 12 months, otherwise, when the new year starts, everyone goes back to zero.

Edited by Steve Martino
additional info
Link to comment
Share on other sites

I looked at your script and notice you are using copy and paste. You should use Set Variable and Set Field instead.

Why? Because the Copy and Paste require the fields to be on your current layout, and the Set Variable and Set Field do not have this limitation. Also, using Copy and Paste will mess with the user’s clipboard.

Link to comment
Share on other sites

With big clients i mean clients i sell a lot to, who "deserve" their own running total field

No client deserves their own field. The number of fields should be constant and not change according to data. What you should have is a record for each client in a Clients table, and a relationship between this table and the Sales table, based on matching ClientID. Then you can see the total of each client by using a either a calculation field in the Clients table = Sum ( Sales::Amount ) or a summary field defined in the Sales table as Total of [Amount]. You can also use a filtered relationship (or a filtered portal) to show only this year's sales of each client.

Alternatively, you could find the sales records for the current year, sort them by client and show them in a summary report. All you need for this is one summary field as described above.


I have a new empty FM file for each year.

This too should not be necessary.

Link to comment
Share on other sites

This topic is 3075 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.