2 Replies Latest reply on May 8, 2015 7:54 PM by TGreen

    Finding the first occurrence

    TGreen

      Title

      Finding the first occurrence

      Post

      Hi everyone - does anyone have any recommendations on how to accomplish this?

      We have an ongoing contest for our sales people like this:

      1)First sale of the week receives 100 dollar commission

      2) Second sale of the week receives 50 dollar commision

      and so on...

      I am trying to find a calculation to find the 1st, 2nd, 3rd, etc occurrence of the sales for the week specified in the layout between startdate/stopdate. The sales agent field is sales_agent and the sale field is a number field named sales_price, along with the date/time field 

      I might be going at this the wrong way, perhaps it would be best when they data entry person inputs the sale to run a calculation there first to see if they are the first, second, whatever...anyways, any input would be appreciated thanks!

        • 1. Re: Finding the first occurrence
          philmodjunk

          Use a relationship that uses a date range to match to the desired set of records. Specify a sort order in this relationship that sorts the related records by date. A portal to this table occurrence will list the first occurrence as the first portal row, the second will be in the second portal row... You may not need to sort the relationship as an unsorted relationship will list the first record to be created first and so forth. Sorting just makes sure in case the transactions did not get created in correct order. (so for a contest, an unsorted relationship may be better to avoid people changing dates to win the contest...)

          You can then use GetNthRecord to access specific records from the related table.

          GetNthRecord ( 2, RelatedTable::Field )

          will return the value of field from the 2nd related record which would be the second portal row in the example that I described.

          Here's a relationship that matches by a date range:

          LayoutTable::Date1 < SalesTransactions::Date AND
          LayoutTable::Date2 > SalesTransactions::Date

          Date1 and Date2 would be used to specify your date range.

          A completely different approach would be to perform a find on a SalesTransactions layout for a date range (Date1...Date2) and then sort the found records by date (or refer to their unsorted order as the oldest record found will be first) to find the first, second, etc transaction in that date range.

          Edit Note: Morning caffeine was falling down on the job and I had the inequalities reversed.

          • 2. Re: Finding the first occurrence
            TGreen

            Thanks Phil - you are a true rock star of the forums! I will try both to see which works best