1 2 Previous Next 20 Replies Latest reply on Jan 6, 2010 8:30 AM by philmodjunk

    Ran out of Reference #'s

    RyanWolf

      Title

      Ran out of Reference #'s

      Post

      I have a database I use for product developement and I have run out of numbers at 100000.

       

      I think I have located the script I need to change 

       

      Set Field [Sample Request Table::New Sample Number; Left (Sample Request Table:: Highest Number Used; 5)+1]

       

      If I change the 5 to 6 or the +1 to +2 will this fix my problem.  

       

      Thank you!

        • 1. Re: Ran out of Reference #'s
          RyanWolf
            

          I've also found the "Highest Sample Calc" which goes a little something like this.

           

          Case (Length ( Sample ) <  5 ;""; Left ( Sample; 5 )<90000 ; ""; Left ( Sample; 2 ) ="lc"  ;"" ;Left ( Sample; 1 )  ≠ "9"; ""; 1 )

           

          Thanks for any help.  I did not create this database, I am just in charge or running it now.  

          • 2. Re: Ran out of Reference #'s
            philmodjunk
               The simplest "fix" would be to toss out this entire approach and replace it with an auto-entered serial number. The examples you give do suggest there may be issues to be resolved by doing this, but if you can make it work, life will be much simpler for you.
            • 3. Re: Ran out of Reference #'s
              RyanWolf
                 PMJ-  Thank you very much for this and I DO think that

              that would solve my problem.  The only thing stopping me is these numbers
              are linked to products and product lines.  The issuance of a duplicate
              number (by human error) could result in tens of thousands of dollars in lost
              business due to production of incorrect product, factory confusion, massive
              time delays due to our factories being oversees etc... Etc... Any other ideas.

              • 4. Re: Ran out of Reference #'s
                RyanWolf
                   Follow up findings.In the item creation database "Sample Request

                Database" there is a button you press to create a new number.  The
                button starts a script part of which is a set field command. When I go to the
                specify command menu within script editor I see this;


                Left ( Sample Request Table::Highest Number Used; 5 )+1



                This calc has, I guess, allowed us to get from 90000 to 100000 but now when
                I try to create a new sample it says “Sample” is defined to contain unique
                values only.


                If I change this calc to;

                Left ( Sample Request Table::Highest Number Used; 6 )+2
                It will create 100001 but after that record it repeats “Sample” is defined

                to contain unique values only







                • 5. Re: Ran out of Reference #'s
                  philmodjunk
                    

                  "The issuance of a duplicate number (by human error) could result in tens of thousands of dollars in lost business due to production of incorrect product, factory confusion, massive time delays due to our factories being oversees etc... Etc... "

                   

                  That's precisely why you should define this as an auto-entered serial number. The number is assigned to new records automatically without human input.

                   

                  Find this field definition in Manage | Database | FIelds and double-click it.

                  Set a "next serial value" at least one larger than the largest value in your table.

                   

                  That should be it for simple numeric serial numbers.

                   

                  In the expression you report, the Left function makes no sense. Simply removing that function from the expression to make it:

                   

                  Sample Request Table::Highest Number Used + 1

                   

                  should work--provided Highest Number Used is returning the correct maximum value.

                   

                  But then this is exactly what an auto-entered serial number setting produces without any special programming or summary fields such as "highest number used" and avoiding a number of potential issues that can keep the above method from assigning the correct value.

                  • 6. Re: Ran out of Reference #'s
                    RyanWolf
                      

                    "That's precisely why you should define this as an auto-entered serial number. The number is assigned to new records automatically without human input."

                     

                    I believe it is an auto-enter field.  We click a button and it creates a whole new record and assigns that record a number, 1 integer larger than the previous.  But it seems to be telling me it has run out of unique numbers. The question is why it wont just create 100001, 100002, 100003 and so on.

                     

                    So should I remove the "highest sample number used" part of the script? Or replace it with next serial value?

                     

                    Thanks!

                    • 7. Re: Ran out of Reference #'s
                      philmodjunk
                        

                      It may be an auto-enter by calculation field, but it's not an auto-entered serial number field.

                       

                      There's a lot I don't know about your database that might affect the correctness of my answer, but from what you've described, I see no reason why you can't simply use the auto-entered serial number (next serial value) setting instead of the calculation.

                      • 8. Re: Ran out of Reference #'s
                        RyanWolf
                           I erased the script that was designated "New Sample Request Script.I then went to Field/Control Setup|Define Database|Fields tab| Sample Field.I then went to Auto Enter|Checked the Serial Number box| Generate- On creation|Next Value 100000|Increment by one.  It still wont let me create a new sample stating that "Sample" field is defined to require unique.BUT!When I uncheck the unique value box and create records it will keep creating a record with the same number, however in the Define database|auto enter tab| Next Value Box it keeps couting up ie;100004-100011.The "sample request database" which creates records in the  "Sample info database" is just not communicating this increase correctly.  Thanks 
                        • 9. Re: Ran out of Reference #'s
                          philmodjunk
                            

                          Details to check:

                           

                          Sample, if that's the name of your serial number field should be a number field. If not, make it so by changing the field type.

                           

                          Do a show all records and sort by Sample. Go to the last record and confirm what value is the largest sample number in this field. Make sure your next serial value is larger than this value.

                           

                          Make sure that you don't have any other field in the table that validates for unique values--perhaps with a custom validation message that is creating some confusion here.

                          • 10. Re: Ran out of Reference #'s
                            RyanWolf
                              

                            1. Sample is a text field

                             

                            2. Perfromed the show all records commnad and then used next highest number

                             

                            3. I found a field called "Sample Info Link" Type: Container, Auto Enter Tab- Everything is greyed out, Serial Number is not selected.  Generate- "On creation" Next Value-1 Increase-1.

                            • 11. Re: Ran out of Reference #'s
                              philmodjunk
                                 According to your original post, the field that should be set up as a serial number is named: Sample Request Table::New Sample Number.
                              • 12. Re: Ran out of Reference #'s
                                RyanWolf
                                   That was a script, not a field.  The field is the "Sample" (number) field.
                                • 13. Re: Ran out of Reference #'s
                                  philmodjunk
                                    

                                  Well your script shows

                                   

                                  Set Field [Sample Request Table::New Sample Number; Left (Sample Request Table:: Highest Number Used; 5)+1]

                                   

                                  which is assigning the serial values to "New Sample Number", not "Sample".

                                   

                                  I noticed that discrepancy and am asking if you have the right field set up for an auto entered serial number.

                                   

                                  If a validation rule set on New Sample Number enforces unique values and you are currently leaving that field empty for each new record you created, you might just see the error message you reported.

                                  • 14. Re: Ran out of Reference #'s
                                    RyanWolf
                                       I think I follow but as you probably know I am quite novice at the developement side of this program.  Everything I've learned is just through poking around practice databases and figuring it out.  I am going to follow up this post with a the exact readout of what is contained under the script name "New Sample Request".  Perhaps there is something in there that need not be left out.  Once again thanks for your pateince and time I really appreciate it!
                                    1 2 Previous Next