2 Replies Latest reply on May 27, 2017 7:07 AM by silas

    A Date Defined Relationship


      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.


      Any ideas?

        • 1. Re: A Date Defined Relationship
          Jason Wood

          Activities does not seem to be relevant here, since you're just counting orders.


          I assume clients are orders are directly related by a client id.


          In Clients, add a global field... you could call it gStartDate


          On file open, you could have this automatically set to January 1 of the current year... Date ( 1 ; 1 ; Year ( Get ( CurrentDate ) ) ), or you could also set it manually.


          On the relationship graph, add a new table occurrence for Orders. I'd call it client_ORDERS|Filtered_AfterDate


          Connect it to CLIENTS using the client id, and add an additional connection between your new global field and the order date. Use the ≥ or ≤ operator (depending on what side its on) so that the relationship brings in orders having a date greater than or equal to the global field in clients.


          Now you can add a calculation field in clients: Count ( client_ORDERS|Filtered_AfterDate::id )

          (specify your primary key in orders or any field that is guaranteed non-empty)

          • 2. Re: A Date Defined Relationship

            This was so easy that I had look at myself and realise that I was just being lazy.

            Thanks for pointing this out. The script took ten times as much effort and was a hundred times slower.

            A lesson learned.