6 Replies Latest reply on Feb 16, 2009 5:14 AM by RobIreland

    New Record numbering problem

    RobIreland

      Title

      New Record numbering problem

      Post

      Hi,

       

      I have a client that wants a very particular type of numbering for all new records in their database.

       

      It needs to be:  PS-2009-02-followed by auto numbering of all records created in Feb. 2009 (incremented by 1). In March, the increment would start back at 1.

       

      I've been playing around for a day now, and I can't seem to get it to work.  The closest that I've come is getting the PS-Year-(month is showing as just a single digit 2, it needs to be 02).

       

      Here is the calculated field details I've been using.  Any help you could give me to point me in the right direction would be extremely helpful.  Thanks!

       

      Upper ( Product Description ) &   (Year ( Date Created )  &  Month ( Date Created ) )

      Note: Product Description is being used to hold the letters PS in it.

        • 1. Re: New Record numbering problem
          davidhead
            

          The first part of your problem is to get the month number padded with a zero if needed. This is done by:

           

          Upper ( Product Description ) & "-" &

          Year ( Date Created ) & "-" &

          Right( "0" & Month ( Date Created ); 2 )

           

          So this gives you PS-2009-02.

           

          The second part of your problem is how to calculate the number of records for that month. This is always problematic when you start asking the tough questions.

           

          What do you want to happen if a record gets deleted? For example, if four records are created and then the second one is deleted, what should be the numbering for the next record? It really should be XX-2009-02-00005. But the other answers could be:

           

          1. go back and use the deleted number so the next record is xx-2009-02-0002.

          2. reserialise all existing records for the month when any record is deleted. 

           

          These are both really bad ideas and stem from a basic problem in trying to create a 'serial' number based on the number of records created. The best idea would be not to allow deletion of records.

           

          Anyhow, those problems aside, you can set up a self-relationship to let you count how many records have been created in the current month. You need a calculated field for YearMonth: 

           

          Year ( Date Created ) & Right( "0" & Month ( Date Created ); 2 )

           

          Use this field on either end of the self relationship. I will call the new table occurrence data_DATA__samemonth.

           

          Then you can set up a number field called CountinMonth. This has an auto-enter calculation:

           

          Max ( data_DATA__samemonth::CountinMonth ) + 1

           

          This will get the largest number in the field from other records created in the month and add 1 to it.

           

          Then you 'serial' field becomes a text field with an auto-entered calculation:

           

          Upper ( Product Description ) & "-" &

          Year ( Date Created ) & "-" &

          Right( "0" & Month ( Date Created ); 2 ) & "-" & 

          Right ( "0000" & CountinMonth ; 5 ) 

           

          So see how that works for you. I have a sample file but I can't post it here. Let me know if you need any clarifications.

          • 2. Re: New Record numbering problem
            RobIreland
              

            Thanks David,

             

            The first part worked beautifully (adding a preceding zero to month).

             

            However, I'm just coming into the Filemaker world from another DB program and I'm not yet skilled here.  I tried to create a self-join (by naming the copy "data_DATA_samemonth) and adding the two calculation fields.  When I add a new record now, all I get is a ? in that field.

             

            Here is a pic of the relationships I have now:

             

            Relationship

            • 3. Re: New Record numbering problem
              davidhead
                

              RobIreland wrote:

              Thanks David,

               

              The first part worked beautifully (adding a preceding zero to month).

               

              However, I'm just coming into the Filemaker world from another DB program and I'm not yet skilled here.  I tried to create a self-join (by naming the copy "data_DATA_samemonth) and adding the two calculation fields.  When I add a new record now, all I get is a ? in that field.


              The relationship you have created looks right. The name you have used does not suit your solution however ;) You could name it something like plan_PLAN__sameMonth.
              Does either calculation field work? Can you give more details or I am only guessing here. 

               


              • 4. Re: New Record numbering problem
                obeechi
                  

                What if there was a serial number kept separate from the Client's requested format? Then you could have a field that acted according to the Client's request, then a real serial field that used a standard approach. Also, isn't it better to use a strictly numeric field for a serial number? 

                 

                I remember once being puzzled by an odd sorting problem, until I realized I was sorting numbers that were in a text field. When I changed the field to numeric the problem was no longer there. Couldn't there odd problems that could crop up using a text field for a serial number? 

                • 5. Re: New Record numbering problem
                  Sorbsbuster
                    

                  Sorry to jump in here, guys, but something has niggled me from early on:  Obeechi said, "I tried to create a self-join (by naming the copy "data_DATA_samemonth)".  Does that mean that the original table was duplicated and re-named in the Tables Tab, and then the relationship made between the two copies, or was it created via a link or in the Relationship Graph, then Filemaker's Occurance Suggested Name re-named?  If it is the former I see trouble ahead, I think.  If it is the latter: sorry to have troubled you.

                   

                  Alan.

                  • 6. Re: New Record numbering problem
                    RobIreland
                      

                    David,

                     

                    The YearMonth field works great (returns 200902) as the number.

                     

                    The CountinMonth field returns the "?".

                     

                    Here is what I have for that calculated field:

                     

                    Unstored, from Plan Sets, = Max (data_DATA__samemonth::CountinMonth ) + 1
                    When I click on "Storage Options", the Indexing section has a checkmark next to "Do not store calculation results -- recalculate when needed."
                    When I uncheckmark that box - I get the following error message "The calculation "CountinMonth" cannot be stored or indexed because it references a related field, a summary field, an unstored calculation, or a field with global storage"
                    If I check the box for Global Storage, I don't get an error message, but the "?" is the result.
                    I took out the Max statement and just had CountinMonth= + 1 and it returns the number 1, but doesn't continue on to number 2, etc.
                    Thanks for looking at this!
                    Rob