7 Replies Latest reply on May 8, 2012 10:50 PM by JustinWhite

    Assigning sequence numbers a new value



      Assigning sequence numbers a new value


      I am trying to create a field that can take a sequence number for each record and assign a new number to it so I can sort the new data.  Starting with the first sequence number (1) I need the next numbers in the sequence to be (5,9,13).  The rub is that If I have 100 records  I only need the sequence to go to (97) then starting at (2) and following the same pattern (2,6,10,14...98), then starting at (3,7,11,15...99) and lastly starting at (4,8,12,16...100).  The number or records always changes so the script/formula will have to reference the total number of records found.

      So after those numbers have been assigned I would sort that field sequentially from small->large.  If everything is correct then the orginal sequence number would be in (1,26,51,76,2,27,52,77,3,28,53,78...25,50,75,100) order.

      I am pretty new to FM so any help would be great.  I can do this in excel very quickly but can't seem to figure it out.


      I really appreciate any guidance,





        • 1. Re: Assigning sequence numbers a new value

          This is an unusual request. Why do you need this special numbering pattern? (If we can get the same results without the pattern, we can probably set up a simpler database solution which will then be easier to maintain and modify.)

          • 2. Re: Assigning sequence numbers a new value

            I need the number pattering for printing needs.  I make name cards, 4 to a page.  

            Abe, Adam, Chris, Doug, Evelyn, Frank, Gretchen, Huey...


            But the way it is setup in preview mode: top left Abe, top right Adam, bottom left Chris, and bottom right Doug.  When working with 1000's of names it is very time consuming to put them in one log run of cards in alpha order.  So if I can get the second name "Adam" to move to the 5th spot, which is top left on the second page and Chris to move to the 9th spot, top left spot on the third page I only have to stack the 4 stacks together and they are in alpha order instead of having to touch every card to get in order.

            As you can see from the image attached it is a very long process to put them in one run in alpha order when they are printed in this order.

            Please let me know if you need any clarification.  


            Thanks a ton.



            • 3. Re: Assigning sequence numbers a new value

              I don't see the need for the special sequence here.

              Take a look at a FileMaker Labels layout. In it, you design the layout for one card and you enter layout mode and go to layout setup.. in order to use the printing options to specify two column printing with the records filling in from left to right rather than from top to bottom.

              When you sort your records in Alpha order, they will automatically print out in the pattern shown in your screen shot.

              If your labels fit a standard avery number based format, this can be very easy to set up via the labels wizard that activates when you specify a new layout of the label format.

              • 4. Re: Assigning sequence numbers a new value

                maybe we are having a miscommunication? Although the labels thing sounds promising. 


                To clarify... The screen shot I sent before is what I DON'T want.  If they are printed in that order then I have to physically put them in alpha order since each card next in the sequence is in a different spot in the layout.  


                I guess what  I need is a way that I can take a long list that is sorted alpha, take the first 1/4 of the names and put them in the same position, top left, of each page.  Then take the next 1/4 and have them in the same position, top right, and so on for the remaining two groups, bottom left and bottom right.  This will allow me to take the top left stack of cards and put them on top of the top right stack which then will go on top of the bottom left and lastly stacked with the bottom right.  In the end I have the cards in alpha order with not having to resort them on my end.

                I am including a screenshot of what I do in Excel in order to have the differnt names moved to the right quadrant on the right page.


                Thanks again,

                • 5. Re: Assigning sequence numbers a new value

                  Given the order you want, shouldn't


                  be numbered like this?

                  1 Apple
                  3 beet
                  5 carrot
                  2 dog
                  4 elephant

                  To produce this pattern?

                  Apple   | Dog
                  Beet     | Elephant

                  Which is what you would need for a Filemaker labels layout here.

                  • 6. Re: Assigning sequence numbers a new value
                    I will try to explain a different way using numbers 1-8. Here are two pages that take me time to sort 1|2 3|4 5|6 7|8 As you can see id have to take each card to put it in order say in a foledex index card holder.  This take a. Long time when working with 1000's of records. Id like them to be in this order 1|3 5|7 2|4 6|8 Now when I have many pages of paper and they are cut into quadrants I only have to take 4 stacks and group them together. Does this help or is my mind stuck in a loop with no end loop? Thanks,
                    • 7. Re: Assigning sequence numbers a new value


                      Thank you for your time in trying to solve my problem. I figured it out with the below formula

                      If ( Record Count Group ≥ Sequence;1+(Sequence-1)*4;If ( Sequence  ≤ Record Group Top Right ; 2+(Sequence-Record Count Group-1)*4 ;If (Sequence ≤  Record Group Bottom Left;3+(Sequence-Record Group Top Right-1)*4;If ( Sequence ≤ Record Count ;4+(Sequence-Record Group Bottom Left-1)*4 ; "" ))))


                      Thanks again.