1 Reply Latest reply on May 23, 2012 4:48 AM by BruceHerbach

    Sequential Numbering based on complex relationship

    gregg.joseph1

      Hi Guys

       

      I'm building a report that uses labour records to show the amount of time worked on each site. Employees clock into a site, which makes a new record in the labour storage table. The record holds the Timestamp, employee PK and job PK

      I need to have several portals on this report, each showing a different employees time.

      The hard part is that the portals need to be in order of who was at the job first, so for that I need the labour records to hold that data somehow?

       

      Is there a way to make a relationship and then have a calculation field determine which number they appeared there?

      For example: If employee #12 is the first to arrive a job#5076, then a new field "emp@job" on all labour records with emp#12 and job#5076 would show "1"

      If employee #43 shows up the next day, then all records for emp#43 & job#5076 would show "2". And so on...

       

      Any help would be much appreciated! Thanks so much!!

        • 1. Re: Sequential Numbering based on complex relationship
          BruceHerbach

          Hi,

           

          Here are a couple of options. 

           

          -  Set up a self join based on Job# and Count the number of records.  Add 1 to the Count value when you create the the next record.  This could be set up as an auto-enter calc with Do Not replace Value. 

           

          - Script this and have the script do a find based on Job#.  Set Variable to the Value of Get(FoundCount) and add 1.  Then create the new record and Set field

           

          - Do a Find based on Job#, sort decending by emp@job, go to record 1, set Variable to emp@job + 1, create new record and set field.

           

          HTH

          Bruce