2 Replies Latest reply on Mar 1, 2014 7:52 PM by mcrip

    Auto-Numbering based on field values


      I'm having a lot of trouble with the search terms on this issue. I'm sure the solution already exists but most of my searches result in discussions about serial numbers. If anyone can point me to an existing discussion I'd appreciate it. But suggesting solutions in this discussion also works


      I want to setup a field that automatically keeps track of the sequence of records based on a type field (displayed in a list view). If the record is of type X, Y, or Z it should increment the count by one from the last record of X, Y, or Z. However if it is of type A or B then it should display some text. The sorting of records is based on a couple of different fields. If I change the type from A to X then everything the numbering of the rest of the records should automatically shift.


      The data setup is a pretty simple one to many relationship of Events>Days>Sections>RI (Route Instructions). I was able to setup some calculations in the RI table that satisfied my requirements by keeping track of the last used number and adding one to that. This worked fine when looking at all RI's in a section, which is anywhere from 5 to 80 records. However when I look at all the RI Records for a specific Day (which is several hundred records) the calculation fails about 140 records in (displays a ?). I presume because this is a couple of calc fields that reference the previous record it is essentially a recursive function that eventually fails after too many calls.

        • 1. Re: Auto-Numbering based on field values

          Consider a self-relationship between RI, based on your x,y,z field (and date? didn't understand that part), sorted by number. Sorted descending, the first number you get via that relationship is the last one used (or sort ascending and use Last() ). Then add an auto-enter calculation à la


          Case ( type = "x" or type = "y" or type "z" ; RI_selfJoin::number + 1 ; type = "A" or type = "b" ; "Your a/b Message" )


          unless the field can only be x, y, z, a or b – then the second test is unnecessary, and you can make its result the default result.


          If an initially a/b field should recive a number after switching the type to x/y/z, you need to uncheck the “Do not replace existing value…” checkbox for auto-enter calc.


          I actually remember a similar thread from not too long ago, but cannot find it at the moment … (but I'm sure I made the same suggestion …)

          • 2. Re: Auto-Numbering based on field values

            With a couple of variations this worked out well.


            I setup a couple of value lists with the values of the two different types.  Then a couple of auto enter fields with a value of 1 if it was in one of the relevant value lists.  Then a count summary field for each of those auto enters.


            Then the suggested self join back to the RI table, along with a relationship to the Previous sections allowed me to setup auto consecutive numbering across sections when looking at the RI's via sections.  Duplicating those relationships for the Table Occurance of RI_ByDay (with some slight modifications in logic) allowed similar auto consectuive numbering across the whole day.