7 Replies Latest reply on Sep 3, 2014 2:10 PM by JeremyBrooke

    Script to automatically renumber other records when one is manually renumbered

    bruderdog

      Title

      Script to automatically renumber other records when one is manually renumbered

      Post

           Hi, I have a product list which is sorted numerically in ascending order (see picture). The Product Numbers are editable, and the list resorts automatically. However, I need a script to automatically renumber the records i didn't modify to avoid duplicate Product Numbers. ObjectModify will trigger this script.

      Example with picture below:

           -If I change 6 to 8, 7 and 8 should become 6 and 7.

           -If I change 8 to 6, 6 and 7 should become 7 and 8.

      Screen Shot:

           Any ideas? I'm pretty new to this so go easy on me.

           J

            

        • 1. Re: Script to automatically renumber other records when one is manually renumbered
          philmodjunk

               Do not use this "product number" field as a field for linking these records to other tables in relationships.

                Replace Field Contents has a serial number option that can be used to renumber a found set of records as you describe.

          • 2. Re: Script to automatically renumber other records when one is manually renumbered
            bruderdog

                 Hey PhilModJunk,

                 Thanks for the reply. You're absolutely right, and I should clarify. I have it set up so every product has a unique serial number that is created with every new record. This pk is never modified.

                  

                 The product number I'm using here will not be used to link this table to any other, but merely be used as a field by which the records can be sorted. The product number will be customizable without creating duplicate pks.

                  

                 Any solutions to the sorting/renumbering script?

            • 3. Re: Script to automatically renumber other records when one is manually renumbered
              philmodjunk

                   to repeat:

                   Replace Field Contents has a serial number option that can be used to renumber a found set of records as you describe.

              • 4. Re: Script to automatically renumber other records when one is manually renumbered
                bruderdog

                     My bad. You're absolutely right. That's exactly what I needed.

                      

                     Thanks.

                • 5. Re: Script to automatically renumber other records when one is manually renumbered
                  JeremyBrooke

                  If anyone is still reading this thread....I am very new to FMP and I am quite confused.  I can see no way that using only Replace Field Contents via a Serial Number works.  I can renumber every record after, but it does not re-order.  

                  For example, I have ~1000 images of objects with descriptions and they were originally sorted by a file name that was the "order" they were to appear in a book.  I now want to create a new field to sort by which would be the new order of the images in the current draft, but need to make it changeable in the event that I want to move an image from, for example, the 20th to appear to the 100th.  I created a field and numbered them with serial #s.  Now I want to go into a field and change 20 to 100, which would make 1-19 the same, 21-100 into 20-99, 20=100 and 101-1000 stay the same.  If I am not mistaken this is what the OP was asking for.  In the event that I go to 20, change it to 100 and use Replace Field Contents (serial number option) it makes 20-1000 into 100-1080 so the count goes 1-19,100-1080.  Not at all what I want to do.  I tried scripting this so that it re-sorts, but the issue is that there would be 2 entries with #100 and none with 20 and the sort cannot know which #100 is first.  I suppose I can use some kind of secondary sort for this (some flag to mark the "changed" one and put that first) then renumber the entire series from the first entry (which is what i am working on now), but this seems inelegant and requires a script be run.  Is there a non-scripted option?  A better way?  or am i on the right track and I need to write a semi-complicated code to do what i want?

                  • 6. Re: Script to automatically renumber other records when one is manually renumbered
                    bruderdog

                    Hey Jeremy, here are some tricks I've learned about creating and modifying a sort order:

                    1. In the Replace Field Contents script step, click the lower 'Specify' button and select 'Replace With Serial Numbers'. Also, be sure to place this script step near the end of your sort script

                    2. Regarding sorting values when you have multiple of the same value (e.g. two 20s): you can create two Scripts set to Script Triggers. The first script is triggered when you click and "enter" the [Sort Number Field] and the second script triggers when you click out of or "exit" that field. Soooo....

                    -The first script (set this to OnObjectEnter) captures the field contents before you change the sort number and creates a global variable.

                             Set Variable [$$SortNumberBefore; Value:Get ( ActiveFieldContents )]

                    -The second script (set this to OnObjectExit) captures the new sort number, runs a minor calculation to ensure the records are in the correct order, sorts the records based on their Sort Number, then re-serializes the 'Sort Number' field to remove value jumps ( like 1,2,4,5,6,9,17 etc...)

                             Set Variable [$$SortNumberAfter; Value:Get ( ActiveFieldContents )]

                             If [$$SortNumberAfter > $$SortNumberBefore]

                                      Insert Calculated Result [ Select; $$SortNumberAfter + .1 ]

                             Else If [$$SortNumberAfter < $$SortNumberBefore]

                                      Insert Calculated Result [ Select; $$SortNumberAfter - .1 ]

                             End If

                             Sort Records [ Restore; No dialog] //(sort the records by your 'Sort Number' field)

                             Replace Field Contents [No dialog; 'Sort Number' field'; Serial numbers]

                    *make sure the variables you set are global (use $$ instead of $) so that the second script can access the first.

                    In the above script, when a record is row '10' and you change it to '20', it will temporarily change the number to '20.1' to ensure that the previous '20' will come before the new '20'. On the other hand, if you change row '30' to '20', the value will become '19.9' and the old '20' will become '21'. After the records are sorted, they'll be re-serialized to become (in your case) 1 - 1000 again.

                    Hope that helps.

                    J

                    • 7. Re: Script to automatically renumber other records when one is manually renumbered
                      JeremyBrooke

                      I will try this out this evening, but you have, quite simply, perfectly expressed in code what I was looking for.  I just was having trouble wrapping my head around the 2 of the same number issue.  I am very grateful -- this is going to save me so much time and effort.

                       

                      Thank you AGAIN!

                       

                      Jeremy