7 Replies Latest reply on Apr 9, 2014 11:49 AM by philmodjunk

    Need help with a calculation auto fill field

    shae1725

      Title

      Need help with a calculation auto fill field

      Post

           Need a bit of help on this - Hope I described it correct

            

           I have a field in my table called    Temp_Position

           This field is to have values 001, 002 003 etc

           The Table is Filterd by found set based on field  Temp_Name and sorted by Temp_Position  Decsending

           I need away to auto fill the Temp_Position field as I create new records . I ALSO need it to recalculate the field if I delete a record

           If I create a new record with a  Temp_Name  Blue then its Temp_position would become 004

           If I delete for example    Record  5    BLUE       001    Then    Record 6  would become    Record 5    Blue   001   

           so for example if I look at the un filtered table

           I will see

           Record        Temp_Name                     Temp_Position

           1                         BOX                                              001

           2                         BOX                                              002

           3                         BOX                                              003

           4                         BOX                                              004

           5                         BLUE                                            001

           6                         BLUE                                            002

           7                         BLUE                                            003

           8                         RED                                              001

            

            

            

        • 1. Re: Need help with a calculation auto fill field
          philmodjunk

               Can you describe how this value will be used? I can think of two options--one will renumber automatically if you delete a record, the other requires a script to do the updating after a record is deleted, but the first method can only be used in very specific situations.

          • 2. Re: Need help with a calculation auto fill field
            shae1725

                 The value is used to keep the sorted records in strict order!

                 The table is part of a complex script step with its functions and parameters in the fields

                 It is also important as a visual indicator as I populate the records. Its really like a mini compiler

                 Its only the calculation part I am trying to solve

                 I could build a script to do it without much of a problem but I would rather have a calculation if possible.

                 Thanks

                  

                  

            • 3. Re: Need help with a calculation auto fill field
              philmodjunk
                   

                        The value is used to keep the sorted records in strict order!

                   But that does not require this kind of numbering system. The same order can be produced simply by using an auto-entered serial number and a sort order that sorts on Temp_name and then by the serial number.

                   Why do the numbers need to start at one with each different Temp_Name and why must there be no "gaps" in the number sequence of "temp_Position"?

                   (If this is just to make the data display nicely, the option that does not require a script to renumber the records when a record is deleted can be used to produce "pretty" values for display purposes only.)

              • 4. Re: Need help with a calculation auto fill field
                shae1725

                     The numbering system as described is an absolute necessity.  auto-serial just does not do what I need it too.  it would take too long to explain the reasons

                     I have been manually adding them recently , And only posted for help before I complied a script, but would be nice for a calculation

                      

                • 5. Re: Need help with a calculation auto fill field
                  philmodjunk

                       Yes, but how do you need to use this numbering?

                       Why is it an "absolute necessity"?

                       I'm not trying to argue with you. I just need to know that so I can pick from one of two approaches that can produce what you want. Your answers will select my suggestion for how to do this.

                  • 6. Re: Need help with a calculation auto fill field
                    shae1725

                         The numbering is called on by script calculations as it loops through the found set. .

                         I Just realised! the records will only ever be added or edited whilst found by Temp_Name and never as an unfiltered table. so it will probably just be better scripting a fix as well as being a lot less complicated.

                         The table will be getting exported to another application when I am finished and the user has no access to it.

                          

                          

                          

                    • 7. Re: Need help with a calculation auto fill field
                      philmodjunk

                           Here's the "no script needed" method that I had in mind. I'm still not sure if it can be used here or not as it depends on how you need to interact with this data.

                           a) stick with a simple auto-entered serial number for each record in the table. Don't worry about the fact that you have gaps as this field will not actually be shown on the screen nor imported.

                           b) Define a field that is either a calculation that always returns the value 1 or that auto-enters the number 1.

                           c) Define a summary field as a running total summary field that computes the total of the field of b) above. I know that you said that you'll only need this with found sets that all have the same temp_name value, but if there is any chance that this might not be the case, use the "restart summary for each sorted group" option with Temp_name as the "group by" field.

                           Sort your records by the serial number field in a) but use the summary field from c to show the sequential, "gap free" numbers for this set of records.This field can be exported so you can still export the values you specified in your original example.