12 Replies Latest reply on Jul 18, 2009 10:15 AM by ocolamonici

    go to record by calculation

    ocolamonici

      Title

      go to record by calculation

      Post

      Hi,

      I have a script used to search on a specific set of ID records in a complex database. The ID records are grouped according to a field that can contain different results (for example, some ID records have A in the field while other have , B or C).   In case that the search does not return any records, I created a variable ($StartingID) so I can return to the original ID within the original group).  The database is always showing all the IDs that are from a single group (i.e., A) that is previously selected. The $StartingID variable captures the unique RecordID created by FM using the Get (RecordID) function before starting the search script.  If the search fails (get found=0), the scripts runs a find for all the records in group A, and then, uses the calculation for the Go to Record/request/page/calculation step at the end of the script to recall the starting record. The script works well until the Go to Record[calculation] step, in which the $StartingID should recall the recordID displayed before starting the search.  Using the debugger I found that everything works perfect until the Go to Record{calculation:$StartingID) step. I pasted the name of the variable into the calculation field of Go to Record, to avoid mistakes, and still does not work. Am I missing anything? Is this a bug?

      Can somebody help me out.

      Thanks,

      Oscar 

        • 1. Re: go to record by calculation
          philmodjunk
            

          Go to record [by calculation] will take you to the nth record which will likely not be the recordID value you stored in your variable.

           

          In other words, if you have 20 records in your found set. Go To Record [10] will take you to the 10th record.

           

          When I want to save the current record so that I can return the user to it, I rely on an auto-entered serial number field, a global number field and a self join linking Table::GlobalField--=--Table2::SerialField.

           

          Then, I can save the current serial number in my global field at the start of the script and can use Go To Related Record to reselect the original record as my current record.

           

          Make sense?

          • 2. Re: go to record by calculation
            ocolamonici
              

            Thanks. At least now I understand why it was not working. I will try to do what you suggested.  The only problem is that I do not have a serial number assigned to the records.  However, I have a unique number for the record ID so I will try to do the self joining table with that.

            I am new doing this things and I do appreciate your help.

            Best,

            Oscar 

            • 3. Re: go to record by calculation
              comment_1
                 It may be simpler to do your find in a new window. If it didn't succeed, close the new window - if it did, close the original one.
              • 4. Re: go to record by calculation
                ocolamonici
                  

                That would work if it fails.  However, if it does not, I still have the problem that the search will show the found ID and all the records in that table, including those that do not correspond the group of the subject ID.  What I need is to find the ID and at the same time have the rest of the ID members of the same group, so the user can navigate (with arrows) among IDs from the same group.  I tried doing the find and then extend or restrict the search to the group.  However, when I do that I lose the ID record of the initial search and the layout displays the first or last record in the group.

                By the way, when one uses the Go to Record[calculation]; what does one use as a parameter in the calculation?  I tried field=ID number and field=$variable, but it does not work.

                Oscar 

                • 5. Re: go to record by calculation
                  philmodjunk
                    

                  "By the way, when one uses the Go to Record[calculation]; what does one use as a parameter in the calculation?  I tried field=ID number and field=$variable, but it does not work."

                   

                  Your parameter needs to be an expression that evaluates to a number that represents the desired record's position in the current found set.

                   

                  With regards to comment's suggestion for opening new windows. That's a very elegant solution--for mac OS systems. In windows, you may get some very undesirable window resizing that requires extra handling to minimize (you can't totally get rid of it).

                  • 6. Re: go to record by calculation
                    ocolamonici
                      

                    I am working on a way around using a store calculation for every record when sorted by the group they are part of.  Is there a way to get the stored calculations to update?  The idea is that if there are no changes in the number of records within the members of a group sorted in a particular way, I can get that number to use it in the go to record step.  However, I a new record is added the number will change and the stored calculation will have to be updated.

                    Oscar 

                    • 7. Re: go to record by calculation
                      etripoli
                         What about storing the current record number in a global variable, using your GTRR script?  Remember, global variables start with $$.
                      • 8. Re: go to record by calculation
                        comment_1
                          

                        ElCola wrote:

                        That would work if it fails.  However, if it does not, I still have the problem that the search will show the found ID and all the records in that table, including those that do not correspond the group of the subject ID.


                        I don't understand: the search will show the records it found, based upon the search criteria. If you're not happy with the results, then you need to refine your criteria - but this seems unrelated to the issue of going back to (or rather staying on) the original record if the search fails.


                        • 9. Re: go to record by calculation
                          ocolamonici
                            

                          I am sorry about the confusion.  The problem with going back to the record if the search fails has been resolved.  I created a global field as suggested above before and everything is fine.  

                          I am using the search as a Navigation tool. Let's say the user has 2000 records in the database, of which 700 have in the "field group" A, 700 B and 600 C.  The user is working with group A and wants to go to recordID 345 within group A.  Then, he/she clicks a button that starts the search.  If the user enters a recordID that does not exist, no problem, a window tells him that there is no such a record and takes him back to the record where he originally was within group A. Now, the problem I have is that if the search finds the record, ends up with only one record.  I wanted to see that record and have all the rest of the database (groups A, B and C), I just have to add a "show all" step.  But, I want to show just the found record within the context of group A sorted by ID.  So, if I extend the search to include group A, now I have all the RecordIDs that have "A" in field group but I lose the found record since the new search ends up in the last record of group A.  The key issue, as PhilModJunk mentioned, is to have the "record count" for record 345, in the example above, when the found set is group A sorted by ID.

                          Oscar 

                          • 10. Re: go to record by calculation
                            philmodjunk
                              

                            Actually,

                             

                            If the desired record is present in the current found set, GTRR without the "show" option, can be used to select the desired record without using any go to record step at all.

                            • 11. Re: go to record by calculation
                              obeechi
                                

                              after you find the record, then show all', do an 'omit of B, C, et... '

                               

                              unless there's a better way (i mean 'show all' will eat resources won't it?) => so maybe a self-join relating 'A' in a global field (in one table-instance) to the set 'A" in non-global (i.e. local) fields in the other instance, then use GTRR ... to move from the first instance to the other (across the relationship) -- no find is performed so as long as the current record is in the related set, it should stay there (right ?!?)... 

                              • 12. Re: go to record by calculation
                                ocolamonici
                                  

                                Thanks to all of you.  I solved the problem using a mixture of your suggestions.  Basically, for the search that returned a result I used a GTRR to a related table in which I have the different groups and a calculation field that returned record count when sorted by group and iD.  The record count was transferred to a $$variable and used in the search to go back to the found record after doing a find for the group.

                                Oscar