3 Replies Latest reply on Jun 9, 2011 10:31 AM by aammondd

    Assigning consecutive Serial Numbers to non-consecutive records

    KristineBell

      Title

      Assigning consecutive Serial Numbers to non-consecutive records

      Post

      Hello all,

      I've set up a DB for a design studio, in which each job has a docket number.

      We have two types of dockets:
      1. DUPLICATE dockets (where a docket is worked on multiple times, we want to record EACH instance of the job as separate records, but use the same docket number).

      2. NEW dockets, which need to be assigned NEW docket numbers.

      Question: What formula/calculation can I use so it skips over duplicate records and assigns the next consecutive serial number from the last NEW job (ie not necessarily the last new record)?

      Example of entering records:

      New Record: New Docket: Assign next consecutive number: 1001

      New Record: Duplicate Docket #985 (keep same docket number)

      New Record: New Docket: Assign next consecutive number: 1002 (currently this record gets assigned 1003, because it counts the previous (duplicate) record as 1002, which I want it to ignore).

      I hope this makes enough sense that someone can help me. I'm pulling my hair out!! Thanks.

      I need a field that will assign a consecutive serial number to a new record, but NOT assign a serial number to a duplicate record.