Running year total per client

[Toko]

Hi,

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/

 

 

 

 

[Steve Martino]

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 September 29, 2015 by Steve Martino
additional info

[Lee Smith]

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.

[Toko]

Hi,

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

I'd like a running total per year. I have a new empty FM file for each year.
@Lee Good point, thank you.

[Toko]

Lee, which function do i use to set the field in calculation window?
Set the variable to $$JohnDoe.

 

[comment]

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.

[Toko]

Haha, hilarious "No clients deserves their own field".
Thank you for your thoughts, you're right. I'll have to set things up in a different way.