1 2 Previous Next 26 Replies Latest reply on Sep 20, 2013 1:48 PM by fmp

    Go to First Record Containing Particular Value

    rmittelman

      This is probably a very simple task. I want to be able to jump to a given section of my table records. Not filter, just jump there.

       

      The table has records like:

      Department1 Item1 DeptFind

      Department1 Item2 DeptFind

      Department2 Item1 DeptFind

      Department2 Item2 DeptFind

      Department2 Item2 DeptFind

      Department2 Item3 DeptFind

      Department2 Item4 DeptFind

      Department2 Item5 DeptFind

      Department3 Item1 DeptFind

      Department3 Item2 DeptFind

       

      DeptFind is a global field. My form has a popup menu based on DeptFind. Also, DeptFind has a Values From setting such that it gives me a distinct list of all departments when clicked on:

      Department1

      Department2

      Department3

       

      Selecting a value from that popup sets the global DeptFind field. So far, so good. Now, all I need to do is figure out the script to jump to the first record for that department in the current found set.

      I know this should be easy, but I'm quite new at FMPro. I was thinking of using Go To Record as a script step, but it requires a record number. I can't figure out how to ascertain the record number of the first record for that department in the current found set.

       

      Can anybody suggest how to do this, or an alternate method, given that I've got the desired department name in the global field?

       

      Thanks...

        • 1. Re: Go to First Record Containing Particular Value
          SteveMartino

          What does first record number represent?  If you include a sort, the first record number will always be the first record.

          I would include a sort by Department Number, then Item number, then you could select record number "1".

          • 2. Re: Go to First Record Containing Particular Value
            pmconaway

            Steve, has a good question. I would add : Do you want to go to the first record created?  When I looked up the Script step Go to Record/Request/Page, the options I have are (First, last, previous, next , by calculation) did you choose another script step. One method I use to get around this sort of issue. Is to sort the records in a manner that the record I want to go to is the first record. I might sort by creation date ascending if I want the oldest record to be first. If I'm looking for the last record created I would reverse the sort.

             

            Tell us more about what constitutes the "First" record for a department.

             

             

            Paul

            • 3. Re: Go to First Record Containing Particular Value
              rmittelman

              Thanks for the quick reply.  Maybe I didn't explain it right.  the data looks like the sample above.  It is sorted by department name, then by item number.  Let's say I open the layout in browse mode, and I'm sitting on the first record (Department1, Item1).  Then I click the popup menu, and choose "Department3" from that menu.  I want to jump to the Department3 Item1 record in the current found set.  I don't want to change the found set, just jump to that record.  Not the ACTUAL first record.  Since there are different numbers of items in each department, I don't know how to find the record number of Department3 Item1.

               

              BTW, it may not always be "Item1".  That department may start with "Item3".  I just want to find the record number of the first record for Department3, given that the records are sorted by Department then by Item.

               

              Thanks...

              • 4. Re: Go to First Record Containing Particular Value
                rmittelman

                Thanks, Paul.  Your reply came just as I was replying to Steve.

                 

                The records, as mentioned before, are sorted by Department+Item.  I don't want to change that, nor do I care about actual order created.  I just want to jump to the first Department3 (for example) record, given the current sort.  Since, in my popup menu, I am choosing only the department name, that is all I have to go on.  How do I translate a particular department name to a record number which equates to the first instance of the records with that department name, given the current sorted result set?

                 

                I assumed that Go to Record/Request/Page, with the By Calculation option was the proper method.  If not, any ideas what to use?  If it is the proper method, I would need that record number, right?  I don't see how I could sort by that, then take the first record, then resort, because I may have chosen "Department2" instead.

                 

                Hope this helps, and thanks again...

                 

                Ron

                • 5. Re: Go to First Record Containing Particular Value
                  SteveMartino

                  More questions.....

                  1.  When you say  "...Then I click the popup menu, and choose "Department3".  What are you clicking--a field, or somthing in a custom menu bar at the top?

                  2.  When you click "Department3", do you want to see a sorted list of all the records in Department3, starting with the first record?  If that's the case, then you could use a script trigger that would take you to the related records and sort.  If sorted in ascending order, the lowest number will always be first.  So if in Department 3, the first record is is Item 24, item 24 will show up as record 1.  You could also do this in a new window so as not to use your 'found set'.  But......

                   

                  I don't understand...."I don't want to change the found set.."  When you open the layout in browse mode, how does it get to the state it's in.  Is there a layout trigger that sorts the data?  If it's an OnRecordLoad script trigger that sorts the records, every time you go to that layout it will put the records in that sorted order.

                  • 6. Re: Go to First Record Containing Particular Value
                    SteveMartino

                    After a sort, did you try Go to Record/Request/Page and choose "First"

                    • 7. Re: Go to First Record Containing Particular Value
                      raybaudi

                      Try this file.

                      It is a.fp7 file but you can convert/open it even with FMP12

                      • 8. Re: Go to First Record Containing Particular Value
                        rmittelman

                        Hi Steve,

                         

                        I'm guessing you are suggesting sorting so the department I want is first, then going to the actual first record (with this new sort), then re-sorting back to Department+Item, leaving my active record as the first one from the department I chose.  So in the sample found set above, I choose "Department2".  After finding the proper record and resorting back to the original order, I would be on record 3, right?

                         

                        Unless I'm missing something, I can't do that.  If the records are sorted as originally stated above, then I choose "Department2" from the popup, how can I sort the found set so Department2 is the first record, given I have Department1 and Department3 records?

                         

                        Hold on, I just had a flash.  Do you mean do a find for the selected value instead of a sort, then goto the first record, then cancel the find (show all records)?  That would work.  In fact, doing it manually, it does work, sort of.

                         

                        The only problem is that if department names have similar words in them, the find isn't exact.  Consider this example, which is closer to actual data:

                        Department One

                        Department One Three Five

                        Department Three

                         

                        On my layout, the Department field is also a popup menu.  When I click the Find icon, then click into the Department field for my criteria, the popup menu displays a list of ALL departments.  So I select "Department Three" from the popup, then click Perform Find.  Instead of finding "Department Three" records, I find "Department One Three Five" records AND "Department Three" records, because both satisfy the find criteria.

                         

                        Is there a way to make the find literal?  IOW, find the records which exactly match the find criteria instead of matching the individual words in the criteria?  That would solve the problem.

                        Thanks...

                        • 9. Re: Go to First Record Containing Particular Value
                          rmittelman

                          Thanks Daniele!

                           

                          That looks like it would work.  Since I'm so new at FMPRO, I may be asking a dumb question, but maybe I don't even need the temporary variable?  Since the global field DeptFind was loaded by picking a department name from a popup menu of department names, couldn't I just use DeptName instead of the variable?  Come to think of it, maybe don't even need the error code for the same reason, right?

                           

                          The only downside I see is that if there are many records in the table, and you pick the last department in the list, it could take a long time to find it, right?

                           

                          If I first do a find based on DeptName I would have a smaller result set, and your technique would also prevent the problem mentioned in my last answer to Steve above.

                           

                          Thanks..

                          • 10. Re: Go to First Record Containing Particular Value
                            raybaudi

                            "maybe I don't even need the temporary variable?"

                             

                             

                            maybe, but it is there because, if the Department isn't in the found set, I wished to return to where I start.

                             

                             

                            "maybe don't even need the error code for the same reason, right?"

                             

                             

                            the answer is the same.

                             

                             

                            "it could take a long time to find it, right?"

                             

                             

                            Not so much, but could be shortened.

                             

                             

                            "If I first do a find based on DeptName I would have a smaller result set"

                             

                             

                            Yes, but you'll lose the starting found set.

                            • 11. Re: Go to First Record Containing Particular Value
                              BruceRobertson

                              How big is your found set? There are several possible approaches to this.

                              It may seem like it's a simple request but - with the current feature set - it isn't simple.

                              It isn't terribly difficult; just not trivial.

                               

                              Here's one approach, looking for a value in the zip code field of a found set.

                              Not suitable for large record counts.

                              Takes a few seconds if the record position is 1,000.

                              In my test case, it was basically instant for position 300.

                              The script could be improved to handle a not-found condition.

                               

                              Set Variable [ $target; Value:"98052" ]
                              Set Variable [ $k; Value:0 ]
                              Set Variable [ $fc; Value:Get( FoundCount ) ]

                              Freeze Window
                              Loop

                              Exit Loop If [ Let( $k = 1+ $k; GetNthRecord( Zips::Zip; $k ) = $target ) ]

                              Exit Loop If [ $k > $fc ]

                              End Loop


                              Go to Record/Request/Page
                              [ $k ]

                              1 of 1 people found this helpful
                              • 12. Re: Go to First Record Containing Particular Value
                                AlanStirling

                                Hi rmittelman

                                 

                                There is a very simple solution to your quest.

                                 

                                You don't need to know the record number of the record you want to select, because the Department Names can be used to select it for you.

                                 

                                I attach a FileMaker database which demonstrates the solution, but for everyone else let me explain how it works.

                                 

                                Firstly, you need to to add a relationship between your Department Selector field (Text Global) and in a new Table occurance (Relationship2) of your data, link to the field 'Department'.

                                 

                                Next you need to construct a one line script, using the script step 'Go to Related Record'.

                                 

                                Set the 'Go to Related Record', so as to use the new Relationship (Relationship2)a nd to show the result on the current layout.

                                 

                                Do not tick any other settings in the GTRR set up box.

                                 

                                Set this script to run using a Script Trigger (On Modify) attached to the Department Selector field.

                                 

                                Now when you select a department, the script runs, which matches the first record with a matching department name to the Department Selector field.  Since you haven't checked either of the two lower boxes in the GTRR set up, the found set will not change, but the required record will be selected.

                                 

                                If you have trouble with this, then come back and see if we can explain more about it.

                                 

                                Best wishes - Alan Stirling, London UK.

                                • 13. Re: Go to First Record Containing Particular Value
                                  raybaudi

                                  Hi Alan

                                   

                                  that is nice.

                                   

                                  Only you'll have to sort the relationship to be sure to go ever to the first item of the department.

                                  And, if the first item of the department isn't in the found set, your script will change it ( the found set )

                                  • 14. Re: Go to First Record Containing Particular Value
                                    rmittelman

                                    Thanks Alan.

                                     

                                    Here is how I solved the issue, before I saw your post, which is a combination of Steve's and Daniele's suggestions:

                                     

                                    If [Departments::DeptFind <> ""]

                                      SetVariable [$Find; Value:Items::DeptFind]

                                      Perform Find [Restore]   (criteria: Items::Department ==$Find)

                                      Go to Record/Request/Page [First]

                                      Show All Records

                                      Sort Records [Restore; No Dialog]  (criteria: Items::Department, Items::Item)

                                      Set Field [Items::DeptFind; ""]

                                    End If

                                     

                                    This does not require knowing the record number.  If I haven't chosen anything in the DeptFind global field, nothing happens.

                                     

                                    I am intrigued by your idea, however.  I've been a programmer analyst for many years, but am stumbling around a bit trying to learn FileMaker Pro.  It's quite different than what I'm used to.

                                    Based on the tutorials I've been through, yours is an excellent idea.  I'm concerned with how much overhead, if any, the new instance brings to the database.  In Access, you can define as many queries as needed, and just execute them.  In FM, apparently, table instances are the equivalent.

                                     

                                    Can you  give me any insight on the merits of the above method vs. the new instance method you proposed?  I'd really appreciate it.

                                    Thanks again for the idea!

                                    1 2 Previous Next