13 Replies Latest reply on Jun 18, 2012 9:52 AM by philmodjunk

    Auto number

    Fagreement

      Title

      Auto number

      Post

      I have a table Collection related to another table Document. Each Collection may have multiple row in Document table.

      I have in the UI a "document No" field, from the Document table,  which is a free text field, the user enter per example: MT_1/2012

      where "MT" is a prefix

      1 is a manual incremental number by the user, and insdiate the document number under a colelction

      2012 is the year.

       

      for another document under the same collection the user enter: MT_2/2012

       

      All this management is manual actually. the user ask to make it somehow automated, which mean the doc number (only the doc number) should be auto incremental within the same Collection.

      so i thought to split this field and put the doc number independant, but how can i make it auto incremental only within the same collection .

       

      hope i was clear

       

      thanks

        • 1. Re: Auto number
          philmodjunk

          Might there be more than one user entering new documents in the same series at the same time?

          There are several ways to set up an auto-numbering sequence specific to a category, but the trick is to make sure that if two users do this at the same time, they can't both get the same number in the same series.

          • 2. Re: Auto number
            Fagreement

            basically one user at the same time.

            how can i do this?

            • 3. Re: Auto number
              philmodjunk

              "Basicallly one user" and "never ever two users" are not the same thing. Wink

              Assuming "never ever", you can define a self join relationship matching on a field that contains the "prefix". Then Max ( relatedTable::SequenceNumber ) + 1 can be used to enter the value of the next record for that prefix. This can be scripted or can be done as an auto-entered calculation if you carefully set all the options just right.

              If there's is the slightest chance that two or more users might do this at the same time for the same prefix, add a text field that uses an auto-enter calculation to produce the full combination of prefix, number and year. Put a unique values validation rule on this field to catch instances when you might get a duplicate value.

              • 4. Re: Auto number
                Fagreement

                one user at the same time.

                how can i do this by script? cause i don't want to complicate the matter and create other tables + relationship 6....etc.

                thanks

                • 5. Re: Auto number
                  philmodjunk

                  I recommend a relationship, though you can script to not use one. But you only need one relationship to make this work. And no added tables are needed either.

                  In your Documents table, define a text field, Prefix, to store the prefix such as MT for each document. Define a number field, DocNumber to store the document number. Define a number or calculation field that stores/returns the year: DocYear.

                  Define this relationship:

                  Documents::Prefix = DocumentsSamePrefix::Prefix And
                  Documents::DocYear = DocumentsSamePrefix::DocYear

                  Where DocumentsSamePrefix is a second occurrence of Documents.

                  Then a script can assign a new number like this:

                  Set Field [ Documents::DocNumber ; If ( IsEmpty ( Documents::DocNumber ) ; Max ( DocumentsSamePrefix::DocNumber ) + 1 ; Documents::DocNumber ) ]

                  If you did not have the relationship, your script would need to;

                  perform a find for all documents records with the same prefix and document year.
                  sort them by DocumentNumber or use a summary field defined to return the Max Docnumber for that prefix
                  Assign the max plus 1 to the new Documents record.

                  And keep in mind that an auto-enter calculation can do this instead of the script.

                  • 6. Re: Auto number
                    Fagreement

                    Thanks,

                    1. but something is missing i nthe script cause in this way when the user create new record (new document under a collection) he will find always Documents::DocNumbe=0, so each tie he will have DocumentsSamePrefix::DocNumber = 1

                    2. Question, this script should be triggred on record new event?

                    • 7. Re: Auto number
                      philmodjunk

                      1) I don't understand. Why would he always find DocNumber = 0?

                      2) There is no New Record event script trigger. The script can be performed whenever you need it. The script does not alter an existing DocNumber value so nothing changes once the record is assigned a DocNumber. The OnRecordCommit, thus could be used as the Trigger.

                      Keep in mind that any script at all is uneeded. An auto-entered calculation can assign the DocNumber instead of using the script. I haven't spelled out the details for that approach as you asked for the script instead.

                      • 8. Re: Auto number
                        Fagreement

                        You said:

                        In your Documents table, define a text field, Prefix, to store the prefix such as MT for each document. Define a number field, DocNumber to store the document number. Define a number or calculation field that stores/returns the year: DocYear.

                        Define this relationship:

                        Documents::Prefix = DocumentsSamePrefix::Prefix And
                        Documents::DocYear = DocumentsSamePrefix::DocYear

                        Where DocumentsSamePrefix is a second occurrence of Documents.

                        Then a script can assign a new number like this:

                        Set Field [ Documents::DocNumber ; If ( IsEmpty ( Documents::DocNumber ) ; Max ( DocumentsSamePrefix::DocNumber ) + 1 ; Documents::DocNumber ) ]

                        I did exactely what you recommended. what I want is to increment the DocNumber each time the user create a new record in Document table, and should be reset to 0 if it is the first Document in the Collection table.

                        So somewhere I should trigger the script that you mention in your message, and logically should be triggred when i create new record i document number, no?

                        Anyway this script return 1 everytime, Therefore, when i do debugging i find always Document 2::DocNumber= empty !


                        • 9. Re: Auto number
                          philmodjunk

                          Ok, missed that detail. This numbering system starts with 1 not zero. You'd need to add special code to set the first document in the series as 0 instead of 1.

                          Set Field [ Documents::DocNumber ; If ( IsEmpty ( Documents::DocNumber ) ; If ( IsEmpty (DocumentsSamePrefix::DocNumber ) ; 0 ; Max ( DocumentsSamePrefix::DocNumber ) + 1 ; Documents::DocNumber ) ) ]

                          • 10. Re: Auto number
                            Fagreement

                            I think still something missed, it doesn't work (still return 1), here is my script:

                             

                            If (
                               IsEmpty ( Document::DocIncrementalNb ) ;
                               If ( IsEmpty ( Document 2::DocIncrementalNb ) ;
                                    0 ;
                                   Max (  Document 2::DocIncrementalNb ) + 1 ) ;
                               Document::DocIncrementalNb  
                            )

                             

                            DocIncrementalNb is DocNumber in your script

                            Document 2 is DocumentsSamePrefix in your script

                            • 11. Re: Auto number
                              philmodjunk

                              Did you try this with a new document and document type in your test?

                              Is DocincrementalNb set to auto-enter a value in field options? (The above code won't work if a value is auto-entered as then the field is never empty.)

                              • 12. Re: Auto number
                                Fagreement

                                in the relationship document and document 2, does i have to check the options allow creation and deletion?

                                when i debug step by step i find always that Document 2::DocIncrementalNb is null, even with the script that you give me above it doesn't work.

                                sorry but I don't know why it doesn't work ! I'm stucking here.

                                please see attach.

                                • 13. Re: Auto number
                                  philmodjunk

                                  Allow creation should not be needed as we are not using the relationship to create new related records.

                                  Try adding a commit record step just before the set field step.