5 Replies Latest reply on Feb 5, 2009 6:50 AM by LightningAd

    Field format changes when data entered into preceding field?



      Field format changes when data entered into preceding field?


      I am using four fields to enter the component parts of a larger code number. One of these fields is a serial number, which i need to display in 3 digit format (including any leading zeroes for number less than 100). These components are then combined to create a single alphanumeric code number


      I have the serial number auto-entering upon record creation. This works fine and shows up in the 3 digit format as required.  The problem i am having is that when i enter data into the preceding field, then the serial field stops displaying the leading zeroes. However, they still display correctly in the combined code number.  


      As this code number is one of the most vital id tools for the item, i need it to look 100% correct whether you are looking at the component fields or the final combined number.



      the settings i have used are as follows:-


      4 fields - Agency (3 character alpha), Product (4 character alpha), Serial (3 digit serial number), Duration (3 digit from value list)


       Serial settings - Auto Enter Serial (start 001  increment by 1),

      calculated value  Case( IsEmpty (TVC 2::serial) or Max(TVC 2::serial)=999; 1; Max(TVC 2::serial)+1)

      Prohibit modification of value during data entry is checked

      Validation - only during data entry checked + Allow user to override during data entry checked

      In Range  000 - 999 



      Can anyone spot any obvious errors in my settings?



        • 1. Re: Field format changes when data entered into preceding field?

          Are all those auto-enter and validation setting on the single Serial Field? I don't think the Calculated value will run at the same time as the Auto-enter serial. What happens when you go over 999?


          What do you need to happen when you go over 999, should it revert back to 001 and count up again?


          If this is the case and the actual serial field is never seen by the user, you could start at 1001, let the Serial value count up by 1, and in you larger code number calculation just reference the right three characters, which will give you your three digit code.


            Right ( TVC 2::serial ; 3 ) 


          so if you went up to "5234" for example you will only ever see "234" in your main code. This all depends if the user interacts with the Serial field directly.


          Would this do the trick? 

          • 2. Re: Field format changes when data entered into preceding field?

            Hi LightningAd


            > Serial (3 digit serial number)


            You have to change that field from number to text...


            001 isn't a number !

            • 3. Re: Field format changes when data entered into preceding field?

              thanks both of you.


              I think i might have found the cure - after the calculated Value option, i checked the Do not replace existing value of field (if any)  button, and that seems to have cured the missing zeroes.



              I will bear in mind your solution Orlando...I need the number (ideally) to restart back at 000 after it has reached 999. Its mainly me using the database so its something i would be aware of, but i would rather keep it simple in case i need others to use it too.


              I have a stack more entries to make before i get to the magic 999, so when i get there i can decide which way to proceed based on what happens!




              • 4. Re: Field format changes when data entered into preceding field?

                Hi Orlando,


                i know this thread is now a few weeks old, but i have just managed to get to the stage where i can be more definitive about what the database is (or isn't) doing.


                Having managed to enter all the data that i needed to get the basic database up to date, i can now say that the  measures i hoped would revert the serial number back to 001 after 999 have failed!


                You suggested that it could work by only referencing the right 3 digits (and i am using this method in the concatenated (Clock Number field) - which works great....but i really need the serial to be on a 001-999 cycle. There is the need to have interactivity with these digits occasionally , and i have a situation now where  the serials are 4 digits and climbing.


                I have tried a calculation  " Case( IsEmpty (TVC 2::serial) or Max(TVC 2::serial)  ≥ 999; 1; Max(TVC 2::serial)+1)"  

                But its having no effect and makes me wonder if the calculation field is ignored for auto-entry serial numbers? 


                I'm using a script to look up the next serial by referencing the last one, and wondered if you can think of a way to validate the size and value of the serial by adding something to this script:-

                Go to Record/Request/Page(last)

                Set next serial value [tvc::serial; tvc::serial+1] 

                New Record/Request





                • 5. Re: Field format changes when data entered into preceding field?

                  Hey Orlando - i just figured out this scripting lark on my own...and it works!!!!  well almost...just one little thing thats puzzling me..


                  i changed that script so it now reads:


                  Go to new record/request/page(last) 

                  if [tvc::serial >=999]

                  set next serial value [tvc::serial; tvc::serial=1]


                   set next serial value [tvc::serial; tvc::serial+1]

                  end if

                  new record/request



                  this works in that when it encounters a serial on the previous page it reverts to 000 - and then begins to climb the numbers again from there....the only problem is - why does it go back to zero when the script tells it to go back to 1 ?


                  any ideas?