8 Replies Latest reply on Apr 7, 2013 8:43 AM by pokerbear

    Sequential numbering per ID

    MikeF

      Title

      Sequential numbering per ID

      Post

       

      Need to create sequential numbering per foreign key field.

      Example using persons attending meetings ..

      If field MeetingID was 101, and three people attended, in the SeqPersons field they would be numbers 1 thru 3 respectively.

      If field MeetingID  was 108, and five persons attended, the SeqPersons field would automatically number them 1 thru 5 respectively.

      Is there an easy way to accomplish this?

      Thanks,

      -          Mike

        • 1. Re: Sequential numbering per ID
          philmodjunk

          How would you use this sequential numbering?

          You may not need to add any field at all to get such numbering, it depends on the purpose of such a number.

          Example:

          If you have a portal of related records that lists each person attending a given meeting, you can put the layout text @@ in the portal row and each row will automatically number itself starting with 1.

          The same layout text in a list view layout will show the same numbering.

          This is just a layout label for showing the current record number--the order of the records in the portal or found set. Change the order and these numbers will also change so they aren't specific to a given record, but they are the simplest option to set up.

          If you need the numbers to be actual values in a field, please also indicate whether these records might be generated by 2 or more users entering such data at the same time or not.

          • 2. Re: Sequential numbering per ID
            MikeF

             Tried that with a portal, and it does work in that context.

            If it's easy enough, would actually like to get the sequential numbers in a field.

            Different analogy ... ticket prices in an event mgmt system ... field name "PriceLevel", where 1 would be the highest price [and possibly only price], 2 would be the second highest price, and so on.  Some events would have one price, some would have say -- 22, others would have 4.

            By having the price level in the field, analyses could then be done.

            Unlikely that two different people would be working on the layout at the same time over a network.

            Thanks,

             - Mike

             

            • 3. Re: Sequential numbering per ID
              philmodjunk

              Unlikely and impossible are two different things. Unless it's impossible, we have to take into account the possibility--however unlikely--that two users might try to do this at the same time. Many approaches have issues where two simultaneous users can get duplicated values unless careful trapping is implemented to avoid it.

              Is it impossible or unlikely?

              By having the price level in the field, analyses could then be done.

              Not sure what "analysis" you have in mind here. Personally, I perfer to keep such data as a price level in one field, and the serial number in another. I can always use a variety of methods to combine the data in the two fields for display/reporting purposes and usually I find it much more flexible to store the different values of this type separately.

              • 4. Re: Sequential numbering per ID
                MikeF

                 Without a doubt, no two people will be entering at the same time.

                Price Level analysis -- well, the differences between Price Level 1 for the same event in two different years would be useful. 

                Am glad to hard-enter if necessary, just looking for easy data entry ..!

                Thanks,

                 - Mike

                • 5. Re: Sequential numbering per ID
                  philmodjunk

                  Such price level analysis does not require special serial numbers, just a field that identifies the price level and another that records the date. Such fields will be easier for people new to your system being able to figure out what they are dealing with as it does not require teaching them any special information on how to decode your serial numbers in order to identify the price level.

                  Is identifying price level the only reason for different serial numbers? if so, there's no need for them.

                  As long as it is not possible for two people to do this at the same time a simple Max Existing Value + 1 set up can be used to auto-enter the next serial number in a given series.

                  Set up a "self join" that matches records by Price Level and any additional fields that identify a specific number series. The following example Matches by Product ID, Price Level and Year. (To create a "self join", go to Manage | Database | Relationships, select your table and use the duplicate button (Two green plus signs) to create a new table occurrence to the same datasource table. THen you can drag from a field in one to a field in the other just like any other table occurrences found here.)

                  YourTable::ProductID = YourTableSameSeries::ProductID AND
                  YourTable::PriceLevel = YourTableSameSeries::PriceLevel AND
                  YourTable::Year = YourTableSameSeries::Year

                  You can double click the new occurrence to open up a dialog where you can change the name like I've shown here.

                  Now your serial number field can be defined as a field of type number with this auto-enter expression:

                  Max ( YourTableSameSeries::SerialNumberField ) + 1

                  Make sure to clear the "do not evaluate if all referenced fields are empty" check box and also clear the "do not replace existing value" check box.

                  Note: Year can be a number field or a calculation field with an expression such as: Year ( SalesDateField )

                  • 6. Re: Sequential numbering per ID
                    MikeF

                     Phil,

                    Thanks for this.

                    Will look at it and get back.

                     

                    Note - We *might* be a little out of sync here...

                    "Such price level analysis does not require special serial numbers, just a field that identifies the price level and another that records the date."

                    ...I *completely* agree with your statement as above.  Am merely looking for a way to automate the [analogous] PriceLevel field so it assigns a number.  It might just be easier to hard-enter it.

                     - Mike

                    • 7. Re: Sequential numbering per ID
                      philmodjunk

                      Note that the relationship specified matches by pricelevel (among other values in other field) to produce the desired price level specific series. I've guessed as to possible other factors, but you can change the results here by matching on different values than what I used in the example.

                      • 8. Re: Sequential numbering per ID
                        pokerbear

                             I have a very similar problem to that originally posted here by MikeF.  I have a project table, and a related table of reports.  However, I want to sequentially number the reports for each different project.  If I just serialize the field report number, they won't be specific to the project.  This is my first database, and I've figured almost everything else out, and I've tried my first few scripts to make this work, but no luck so far.  

                             So, each project field report would be linked to the specific project with it's own Field Report #'s.

                             Also, lot's of other data on each field report specific to that specific field report and project.  

                             Thanks for any help or questions.