I need to make calculations for a rewards program. I have a table of clients, a table of activities, and a table that groups activities by an order number and determines billing.
Rewards would be calculated by the number of orders (grouped activities) that a client makes within a one-year time frame beginning on Jan 1.
I currently have a script which counts the orders by searching the orders table and then returning get(FoundCount). This works but it is slow and worse it must be run at the right time in order to be meaningful.
What I would rather would be a relationship that is capable of checking for the beginning of the year (all rewards reset on Jan 1) and then simply returning Count(Field) to get the value I need.
I am at a bit of a loss on how to pull this off.