10 Replies Latest reply on May 9, 2009 2:48 PM by DaveParry

    Conditional auto index



      Conditional auto index


      I want a index field to increment only when a value field ="Y". Thought it would be simple but, either it's not or I am missing something. I have tried using auto index using calc "If ( new cust = "Y"; cust Number = cust Number + 1 ; cust Number) and other combinations, but it still increments regardless of the value field.


        • 1. Re: Conditional auto index

          Hi Dave


          You didn't explain where you have placed your calc expression, therefore, I have to assume that you have defined it in the field "cust Number" as an Auto-Enter option.


          Your calc should be like is:            If ( new cust = "Y"; cust Number + 1 ; cust Number)


          And ... to avoid input errors due to entering lowercase caracters your calc should look like this:


          If ( Upper (new cust) = "Y"; cust Number + 1 ; cust Number).


          Let me know if this worked for you.







          • 2. Re: Conditional auto index

            Yes I have placed it in the auto enter.

            If I use your calc, if I have calculated value ticked, it doesnt increment, it always displays "1", and if I have the serial number ticked it always increments. I have also checked and unchecked the  dont replace value option. same result.

            • 3. Re: Conditional auto index

              Hi Dave


              you wrote


              If I use your calc, if I have calculated value ticked, it doesn't increment, it always displays "1", and if I have the serial number ticked it always increments. I have also checked and unchecked the  don't replace value option. same result.




              Have you selected "Seral Number" in Auto-Enter as well? Because If you did it will interfere with calc. In Auto-Enter tick only the box Calculated Value, insert my calc in your "cust Number" field and deselect the box "Do not replace existing value for field (if any)". Make sure you don't have more than one option ticked in Auto-Enter. Also check Validation tab in Auto-Enter. You should have ticked only the radio button "Only during data entry" and the check box "Allow user to override during data entry".

              Now test it. Every time you retype "y" in your text field "new cust",cust Number will increase.


              Let me know of your results.


              • 4. Re: Conditional auto index

                Thanks for reply Kido, I did have it set as you suggest, I tried other settings just to see if they cured the problem.

                If I cick Y it does increment the value, but on the next record it resets to 1. 

                • 5. Re: Conditional auto index

                  Of course... The calc works on a record level. It is good if you want to know how many times the cust changed. What you are trying to achieve requires a different approach. Try the following:


                  Deselect your calculated value in your field cust Number.

                  In your table create a new text field named _one, and in Auto-Enter Calculated Value type in the number 1.  In the Storage tab tick the first option box "Use global storage (one value for all records)". Click OK end exit auto-enter..


                  Create a new Table named Globals.

                  Create a field named _one, exactly like the above. Same procedure.

                  Create another field, numeric type, named CustIndex.

                  Now click on Relationships tab and link the 2 fields named _one, by selecting and dragging one into the other.A  line with a box in the middle will appear, linking those fields together. Click OK.


                  Now build a script named AutoIndex as follows:


                  ----- AutoIndex -----


                  Allow User Abort [off]

                  Set Error Capture [On]

                  If [ Upper( yourTable::new cust) = "Y" ]

                    # Increment your index field

                     Set Field [Globals::CustIndex; Globals::CustIndex + 1]

                    # ... and copy it to your cust Number field

                    Set Field [ YorTable::cust Number; Globals::CustIndex]

                  End If


                  Save your script.


                  Now in layout mode  select your text field "cust New" and MENU / FORMAT / Set Script Triggers... and in the pop-up window select OnObjectExi and select your script AutoIndex.


                  Test it now and see what happens.




                  • 6. Re: Conditional auto index


                    tried it and cant get it to work.

                    have created a test database and have labeled as per your instructions, same result.

                    It displays field _one as a 1 and nothing in the cust Number field.

                    • 7. Re: Conditional auto index



                      The field _one is just to establish a relationship between your table and the new table Globals. It will always show 1.

                      You need to go to the layout globals and Add a new record (I forgot to tell you this, sorry). The table Gobals must have only 1 record. You don't need to include this field in your layouts, but you can include the Globals-CustIndex for testing purposes.


                      Recheck my last message and make sure you have followed the steps. If you did, the field CustIndex in table Globals will hold your last incremented value, whilst your field cust Number will hold the value at the time you typed "y" in your text field cust New. This text field, cust New, must have the script AutoIndex in script trigger OnObjectExit.


                      Don't forget to deselect the Calculated Value in Auto-Enter for your field cust Number.

                      I have tested and it works in my computer.


                      Let me know if you get stuck.



                      • 8. Re: Conditional auto index

                        Thanks KIDO that works fine now.

                        Two Points

                        one - how can I set it to a starter value, is it the _one field?

                        Two - at the moment it increments every time I press Y on the same record. Can it easily be made to increment only once?


                        • 9. Re: Conditional auto index
                             <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

                          No Dave


                          1 - Forget the _one field. It exists only for relationship purpose between the two tables. Go to your layout Globals and insert the starter value in the field CustIndex. Do not add more records in this table, just change the value in the field'


                          2 – Yes. In your table Globals create a new text field named CustStatus.

                          Create a script 'Global Status' with 1 line:

                          Set Field [ Globals::CustStatus ; yourTable::cust New]

                          edit the script triggers in your field cust New and check the box OnObjctEntry. Assign your script Global Status.


                          Now edit your script AutoIndex and amend the 'If'' instruction as follows:

                          If [ Upper( yourTable::new cust) = "Y" and Upper(Globals::CustStatus) “Y”]


                          Test it now.



                          • 10. Re: Conditional auto index

                             Works a treat. Would never have got there without your help. Will have to take some time now to try and understand what you did !!

                            Thanks again.