7 Replies Latest reply on Jan 11, 2010 7:47 AM by ninja

    How do I create blank record with incrementing serial number.

    trouty

      Title

      How do I create blank record with incrementing serial number.

      Post

      Hi, can anyone tell me how to achieve the following:

      After creating a new record and filling in all fields, I want to then create another blank record, but want the field that contains the serial number to increment by one.   Please note that I cannot use the auto incrementing serial number function, as the record may be duplicated several times in which case I may have 3 or 4 (or more) records with the same serial number which is what I'm after.

       

      I'm probably looking for a script to do the above, but am new to Filemaker and need to learn more about scripting.

       

      Any suggestions much appreciated.

       

      Trouty

        • 1. Re: How do I create blank record with incrementing serial number.
          ninja
            

          Howdy trouty,

           

          I would keep the autoenter serial in place and write a script for those that you want to duplicate.

           

          I would also question thoroughly why you would want multiple records with the same ID#.  It sort of defeats the concept of having an ID# doesn't it?  Why do you want to do this?

           

          When you want a new record with the next higher number, use:  New Record/Request

           

          When you want a duplicate record with the same ID# (again, why?):

          run the script:

           

          SetVariable [$ID ; Value: IDField ]

          New Record/Request

          SetField [IDField ; $ID ]

           

          You don't have to answer the "Why?" question to me...but you may have to answer it to your database design when it stops working the way you want it to...so think it through thoroughly.

          • 2. Re: How do I create blank record with incrementing serial number.
            trouty
              

            Hi Ninja,  already have an auto serial number for relating this table to other tables, so this isn't at issue.   The field value I want to increment when creating a new record is the serial number (not ID).  I use this serial number to group records together by this field.  For instance, a commission payment may come in from a company such as Legal & General, this commission payment may be split between 6 financial advisers.  By using the serial number, I can group the split together to produce a total which should match up with the gross commission payment to the main company for reconciliation and accounting purposes.

             

            So I really don't wish to use the autoenter serial.  Also, when the payment comes in from Legal & General, I will create the first record with all details relating to the payment such as the payment date and the period that the commission payment covers.  I will then duplicate this 5 times to give me the 6 way split so that each adviser is listed with their own payment details and the payment.

             

            Could you consider the solution for this again on the above basis.  I cannot see how the autoenter serial would work.

             

            Many thanks

             

            Trouty 

            • 3. Re: How do I create blank record with incrementing serial number.
              ninja
                

              trouty wrote:

              already have an auto serial number for relating this table to other tables, so this isn't at issue.   The field value I want to increment when creating a new record is the serial number (not ID).  


              Got it.  Well done.

               

              So the serial number (not ID) is used to group similar records.  What else is it used for?  Is there another table that links to/references this number (Payments Table?)

               

              If so, perhaps it would be best for you to work from a layout based on that OTHER table and work through a portal.  This would have all of your Serial numbers not only be the same (based on the key from the Payment Table), but would take care of linking it to the place you want it linked.

              Your gross commission payment would be from the reference of the other table and simply be Sum(Commissions:: pymt)

               

              Sorry if I'm slow to understand.


              • 4. Re: How do I create blank record with incrementing serial number.
                trouty
                  

                Hi Ninja,

                 

                Wonder if it would be possible to create a script to create the new blank record, which would look at the previous serial number for a completed record, andthe would increment it by one.  I would like the options to either do this or not do this and would prefer to use a button when I need to carry out the auto increment.

                Could you give me some idea of syntax.  Don't worry about summarising these records grouped by the serial number, I've already got it sorted with reporting.

                 

                Many thanks

                 

                Trouty

                 

                ps - thanks for your help! 

                • 5. Re: How do I create blank record with incrementing serial number.
                  philmodjunk
                     Will you be doing this in a multi-user set up (file is shared over network) or single-user (only one person has file open at a time. Scripts that assign serial numbers instead of relying on an auto-entered serial number have to be designed with care to make sure that issues are avoided if two different users don't try to assign a serial number to the same record or the same serial number to different records...
                  • 6. Re: How do I create blank record with incrementing serial number.
                    trouty
                       Hi PhilModJunk,  this is for a single user setup only.
                    • 7. Re: How do I create blank record with incrementing serial number.
                      ninja
                        

                      One way to do this, assuming that you want the newID to be incremented by one from the max serial# (as opposed to the record you are currently on)...

                       

                      Self Join your table and run the following script:

                      New Record/Request

                      SetField [IDNumber ; Max (JoinTable::IDNumber) + 1 ]

                       

                      At the risk of being an annoying, resounding gong...the above script snippet simply replaces the autoenterID...

                       

                      For a duplicate record with the same ID (I still can't figure out why you want this...but that's OK) simply run the three step script I posted up above.

                       

                      Put these two scripts on buttons labelled "Duplicate Record" and "New Record" or as appropriate.

                       

                      Note that if there is a "-" preceeding the number in your ID it will treat as a negative, so switch to "- 1 ] "