1 2 Previous Next 22 Replies Latest reply on Mar 20, 2017 10:38 AM by mm2007

    Navigate to next sequential number



      The attached file has scripts for navigating to next paragraph when they are in increments of 1, i.e. paragraphs are in serial order of 1, 2, 3 and 4. However, if I have to delete all the records related to paragraph 3, the "next paragraph" button will NOT take me to paragraph 4. I want the "next paragraph' button to find the next higher para number and go to the first available record on it.

      Please note this is a template from a much larger file which has probably hundreds of paragraphs and hundreds of lines within each para.

      Any help will be much appreciated.

        • 1. Re: Navigate to next sequential number

          this is a mess

          • 2. Re: Navigate to next sequential number

            I modified your solution with a new script that has a very basic approach to what you want to do. Enjoy!


            BTW, it's not necessary to insult people who don't have as much experience in FileMaker as you siplus. You can just choose to remain silent.

            1 of 1 people found this helpful
            • 3. Re: Navigate to next sequential number

              I agree that it's a mess. Your paragraph numbers are not unique. From what I see, each record in "table"--a poor choice of names does not represent an entire paragraph.


              What you can do is get a list of all the unique paragraph numbers in your table. If you have multiple documents in the table, you'd need to add criteria that limits it to the current document.


              ExecuteSQL ( "

              SELECT DISTINCT \"Paragraph No.\" FROM \"Table\"

              ORDER BY \"Paragraph No.\"" ;

              "" ; "" )


              If you load a global field or variable with this list, you get the list of paragraph numbers in sorted order.


              GetValue ( $$ParagraphList ; $Current Para + 1 )


              Would then return the number of the next paragraph or null if you are already on the last paragraph.

              • 4. Re: Navigate to next sequential number
                Jason Wood

                The paragraph numbers aren't supposed to be unique. Records represent words. Therefore there can be many records belonging to the same paragraph.


                Jaymo's suggestion of finding "greater than current paragraph" solves the problem very simply.


                I don't think it's a mess. I strongly suspect there are better ways to build this, but not knowing the background about what this is used for and how the data comes in, I wouldn't know what to suggest.

                • 5. Re: Navigate to next sequential number

                  where is the insult ?


                  I spent 15 minutes trying to understand the OP's solution (scripts, data structure, testing out all buttons) just in order to give some useful output.


                  If I said what I said, then you better bet I stand behind it...


                  Too often we get a piece of unidentifiable jerk thrown in the arena with an implicit "help me, shit and fan are friends right now" and an explicit "this is it, won't tell you what I'm trying to achieve, won't tell you if it's hosted, won't tell you what FM version I'm using etc etc just fix it" and I'm trying to put a big sign all over this reading "help me to help you".


                  I do like to think that people come here and hit the search button before asking questions. Maybe I'm wrong, but sincerely it's what I sometimes do, too, before asking a question. And there are answers and Answers. The key to a successful positive echo is to start with a scenario picture in which your own reality and problems are depicted universally, beyond chosen data structures and existing constraints, just as "needs". That's what people must learn to do.


                  Given input, desired output, paths unsuccessfully walked along eventually, nothing more, nothing less. Everybody will get and offer more ideas and fun if there are no constraints, if willing people are free to offer different ways of facing the problem without having to walk a doomed path.

                  • 6. Re: Navigate to next sequential number

                    philmodjunk and siplus are correct about your solution. I just don't agree with the way siplus went about telling you. The issue is the relational design. What I provided you was a solution I thought met your skill level and current solution design. All the best.

                    • 7. Re: Navigate to next sequential number

                      I'm sure mm2007 would have preferred an explanation as to why his database is "a mess" rather than just telling him his database isn't good.

                      • 8. Re: Navigate to next sequential number

                        Dear All,

                        Firstly, Jaymo, Thank you very much for providing a new script. it works fine for me and does perfectly what i was trying to do.

                        Secondly, apologies if I have offended anyone by uploading such a "messy" file and not providing a clear background to my file or the problem. I did try to search in the forum but could not find what I needed. Maybe i was struggling to identify what was lacking in the first place or identifying what I am trying to achieve.

                        Any comments to clean the file up are welcome.

                        Now that everyone has probably looked at Jaymo's solution to my problem, can anyone help me figure out how to amend the script to go to a previous paragraph (even if it is not is increments of 1).

                        i have tried to amend Jaymo's script by changing ">" to"<" but this takes me back to the first record, not to the first record of the previous paragraph.


                        Any help much appreciated.

                        • 9. Re: Navigate to next sequential number
                          Jason Wood

                          i have tried to amend Jaymo's script by changing ">" to"<" but this takes me back to the first record, not to the first record of the previous paragraph.


                          Any help much appreciated.

                          You're on the right track but because the "<" is finding all earlier paragraphs, you're going to end up on the first record which will be the first paragraph. The simplest way would be to add a "Go to Record [Last]"


                          BUT - that's going to give you the last line & word of the previous paragraph, whereas you probably want the first.


                          Probably the easiest way to solve this would be to put the new paragraph number into a variable and perform a second find for that paragraph.

                          • 10. Re: Navigate to next sequential number

                            You are most welcome mm2007. Glad to help. The issue with your file is the relational structure. You need more tables most likely. Any time you repeat the same data in records, you need to consider a new table. This is referred to as first normal form. Normalizing your data is always important but sometimes you need to break the rules depending on the needs of your solution. Before changing the structure of your solution, you should create a requirements document listing everything it needs to do. Think of it as an outline of the features. In this way, we can help you determine the best structure for your solution.

                            • 11. Re: Navigate to next sequential number

                              I will note that my suggestion of a list of paragraph numbers generated by a SQL query would make the "previous paragraph" issue simpler. And you would not need to use ExecuteSQL to get such a list if you added a related table of Paragraph records with one record for each paragraph.

                              • 12. Re: Navigate to next sequential number

                                Here is a modified version of your file with quite a few changes, to give you something to think about.

                                • 13. Re: Navigate to next sequential number

                                  mm2007, this is another version of your file. And yes, things always be done better.


                                  This version has your original deleted paragraph in it. If you don’t want the field “Line Text” that I have placed in the layout, then remove it. Furthermore, I have enabled Table View, so you may disable it.


                                  This new file, should allow you to delete a line, or delete a paragraph, and work correctly.


                                  My apologies, I don’t comments my scripts, a very bad habit indeed.


                                  BruceRobertson’s version is most excellent.

                                  • 14. Re: Navigate to next sequential number

                                    Thank you everyone for all the support and help provided. Special thanks to all the people who have written scripts for my file including Jaime, Bruce Robertson and Rouelf.

                                    The only issue I am left with now is (using Rouelf's script): -

                                    Currently Para 2 has 3 lines:- line 1, line 2 and line 3.

                                    If I delete line 2, the button to navigate backwards (left arrow)  which sits below "Line" in the layout will take me to para 2 line 1.

                                    However, if I had say 5 lines within para2 and I wanted to go from line 5 to line 3 (if line 4 is deleted) or 2 (if lines 4 & 3) are deleted, it won't do it. It takes me straight to line 1.

                                    1 2 Previous Next