14 Replies Latest reply on Aug 4, 2011 9:19 AM by philmodjunk

    FIND RECORDS without changing the open record

    GaryMak

      Title

      FIND RECORDS without changing the open record

      Post

      How do you find a subset of records without changing the open record you are in?

      I have a data base of several hundred accounts and have a FIND script  to find a subset of those accounts.  It does this successfully. (There are 51 accounts that meet this criteria and the script correctly finds this subset of 51 based on matching the criteria from a specific field ("company name.))  This isn't the problem.

      The problem is that I am in a given record when I click this Find Script but then the Script finds all the 51 records in the subset and changes the open window to a different record!  (It's always the same record, and it is NOT alphabetical.)  NOTE: The open record I am in when I click the  script is part of the subset!

      How do I execut this script to find all the records in the subset but not change the window I am working in?  It is important, since there is data executed in other fields in an open window that calculate results based on the totals from the subset.

      Hope I explained the problem clearly enough and it's not confusing.

      THANKS!

      -Gary

        • 1. Re: FIND RECORDS without changing the open record
          philmodjunk

          A little more detail would help.

          What do you intend to do with that found set of 51 records once you have found it?

          Do you want a found set of those 51 records, but want the current record, if part of that 51, to remain the same current record?

          If so, you can use a script to first store the primary key or record ID of the current record so that it can return you to it after performing the find.

          Or do you want to process the 51 records in some manner "off stage" with out changing records on the current layout?

          This can be done by performing the find in either a hidden window (Not always a good idea in Windows systems) or in a hidden layout based on a different table occurrence of the same table as your current layout.

          I just need to know what you want here and then I can elaborate...

          • 2. Re: FIND RECORDS without changing the open record
            GaryMak

            Thanks for your response!  Sorry to not have made it clearer.  OK, here goes:

            I have a database of "accounts" I've kept for the last 25 years (since the original FileMaker!)  It contains hundreds of accounts in the industry I'm in.  These are the accounts I've had over several companies that I've worked for.    I really only work with my current accounts of 50 or so (and growing) of my present company, let's just call it "GMak Inc"   However, there are some cross-references with the old accounts, (people, products, etc.) and for historical and informational reasons, it's good to have access to them in the same db.

            If I do a "FIND" in the db "ACCOUNTS" of say "Acme Hospital" it returns "Acme" no problem,  the result is a subset of 1/300 (where "1" is this specific account and "300" is the total number of accounts in the db.)  The problem is, however, that there are some calculations in ACCOUNTS (sales subtotals, sales totals, percent of sales for this account as total of all accounts, for example) that are dependent upon the ENTIRE subset of all the current accounts of "GMak Inc" being found. 

             So, I can solve this problem by running a script (attached to a button) as follows: 

             PERFORM SCRIPT… Script Name "FIND GMak Inc" executes: PERFORM FIND (RESTORE) where FIND RECORDS = Field "ACCOUNT RECORDS… FIND RECORDS WHEN ACCOUNT RECORDS (COMPANY) = GMak Inc

            This executes and finds all accounts associated with GMak Inc, a total of 52 accounts. 

            This is not the problem.

            The problem is that when I do this, it finds all the "GMak Inc" accounts but changes what I had been viewing in the open window to display a different account - one particular account - in the open window.  So, if I am viewing say "Acme Hospital" as the open window and click this find subset button, it finds all the accounts for "GMak Inc" and changes the open window to say, "City Hospital."  (It always seems to pick the same file - and it is not alphabetical, or chronological.)  (Also, I experimented and tried taking the "City Hospital" account out of the mix, and it then picke another account consistently to display.)

            All the calculations mentioned above are done on the subset of "GMak Inc" accounts (sales, total sales, etc.) but the account that I am viewing is NOT "Acme Hospital;" it is a different account.  If I then do a "FIND Acme Hospital" it finds it, but the totals are not correct because now the subset of "found" accounts is only 1!

            All I want to do is have it find the subset of "GMak Inc" accounts in the db, but keep whatever window I am viewing open.

            I hope that is clearer and makes sense.  Thanks for wading through this.  The previous line is the crux of the problem, the rest is background.

            Thanks!

            PS: I am using the Mac version of FM11.

            • 3. Re: FIND RECORDS without changing the open record
              philmodjunk

              A find returns the matching set of records in unsorted order and puts the first record of this set as the current record. Unless importing records has been performed, the first record created will be the first record in this set untill you sort the records.

              Here are 3 different ways to keep the current record before the find so that it is still the current record afterwards:

              1) Have the script set a field in the current record to 1. After performing the find, sort the records by this field in descending order. Then use Go To Record/Request/Page [first] to go to the first record. This will be the record you started on. Now have the last step be to return the value of this field to 0 so that there is never more than one such record "marked" in this fashion.

              This works well in single user systems. Do not use in systems where 2 or more users might be doing this task at the same time as the sort step might sort a different record to the top of the found set--one marked by a different user's copy of this same script.

              2) Have the script store the current record's RecordID or PrimaryKey in a variable ($CurrentRecord). Then it performs the find. Then is uses a looping script like this to step through the records until it finds the original record:

              Perform Find
              Loop
                 Exit Loop If [Get ( RecordID ) = $CurrentRecord ]
                 Go To Record/Request/Page [next ; exit after last ]
              End Loop


              This method can take a bit of time to do if you have large found sets--not the case here, so this will work for you.

              3) Have the script store the current record's primary key in a global field. After finding the records, use Go To Related Records without using the "show related records only" option to go to the current record. This will be faster than the above loop, but requires a self join relationship:

              YourTable::GlobalField = YourTable 2::PrimaryKeyField

              It also will pull up all records should the current record not have been part of the records found by the scripted find. 

              • 4. Re: FIND RECORDS without changing the open record
                GaryMak

                (It seems that this post did not upload the first time, so sorry if it is just a time-delayed problem and there are two posts.  I didn't save a copy so the wording will be slightly different (hopefully better.)

                Thanks for the multiple options!!!  I think I will pass on option #2 and #3 as that's way beyond my FM programming abilities.   I did make progress!

                I did try #1 as follows:

                Set Field (Account Records: Hospital (Note: Specify Field= Account Records:: Hospital, Repetion =1)

                Perform Find (Restore) [Note: Find Request is: Account Record = "GMak Inc"]

                Sort Record [Restore; No Dialog) (Note: Sort is on "Hospital" in descending order)

                Go to Record/Request/Page[First]

                Set Field [Account Records::Hospital[Account Records::Field for Sorting=0]]

                I got the following results:

                1) It successfully found and sorted the 51 accounts that form the current subset.

                2) It did sort in descending order BUT only sorted alphabetically, and thus continually returned the account with the highest letter first.

                3) I tried creating a field called "sorting field" and using that, but that didn't work.

                I guess I do not understand "Have the script set a field in the current record to 1." properly….

                • 5. Re: FIND RECORDS without changing the open record
                  philmodjunk

                  If you type in exactly what you see in the script editor when posting a script here, it can help. I think that first set field line isn't set up correctly.

                  When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Click OK. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                  For easier options when posting a script:

                  To post a script to the forum:

                  1. You can upload a screen shot of your script by clicking the edit link to your original message and uploading it there
                  2. You can upload a screen shot to a file share site and post the download link to a new response you post here.
                  3. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF.
                  4. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.
                  5. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional look to your script. (Use the HTML option and post the HTML tagged version into the HTML editor.)
                  • 6. Re: FIND RECORDS without changing the open record
                    GaryMak

                    Thanks again for the tips.  I'm not getting the SET FIELD portion as I've never done it before.  Everything else is working, though.

                    OK here it is:

                    FIND SUBSET of All ACCOUNTS

                    Set Field [ ACCOUNT RECORDS::Hospital; ACCOUNT RECORDS::Hospital ]

                    Perform Find [ Specified Find Requests: Find Records; Criteria: ACCOUNT RECORDS::Company: “="ACME"

                    ” ]

                    [ Restore ]

                    Sort Records [ Specified Sort Order: ACCOUNT RECORDS::Hospital; descending ]

                    [ Restore; No dialog ]

                    Go to Record/Request/Page

                    [ First ]

                    Set Field [ ACCOUNT RECORDS::Hospital[ACCOUNT RECORDS::Field for Sorting = 0] ]

                    • 7. Re: FIND RECORDS without changing the open record
                      philmodjunk

                      You need a different field for the first script step. And this should refer to a field that stores a value that unquely identifies each record in accounts. (Don't know if each account is with a different hospital or not.

                      Try defining a number field, flag in account records and make the first step:

                      Set Field [Account records::Flag ; Get ( RecordID ) ]

                      Then sort your records by Flag in the sort step.

                      The final set field step is set up incorrectly, please read my last post on how to do it. The extra brackets tell me you are putting your expression in the wrong place.

                      It should be either:

                      Set Field [Account Records::Flag ; "" ]

                      or

                      Set Field [Account Records::Flag ; 0 ]

                      • 8. Re: FIND RECORDS without changing the open record
                        GaryMak

                        I'm really sorry.  I just can't follow.

                        1) Are you suggesting I create a new numeric field for assigning a unique ID to each file and call it "flag"?  Or is "flag" a function?

                        2) in the function pick list, there is no function called "Get (RecordID)"

                        3) Is "RecordID" the name of the field I set up in #1 above?

                        4) When I try to string the funtions, the semi-colon does not come automatically.  When I enter it manually and try to save, I get an error messages like "The specified field can't be found" or "expecting an operator here..."

                        I think I may be taking too much of your time.  I am familiar with setting  up basic FM scripts, auto-filling information from other files, global calculations and the like, but you are at a level way higher than my level of competency.  Sorry for the basic questions...

                        In case I'm in the completely wrong place, here are some screens shots:

                        Sorry, Edit function not working on Safari it seems.  I tried inserting an image and the screen goes to a new screen with no controls to upload the photo.

                        • 9. Re: FIND RECORDS without changing the open record
                          philmodjunk

                          1) yes

                          2) There is a function called Get and RecordID is one of a long list of parameters you could use with it. This is a "swiss army knife" type of function that can do many different things for you depending on the parameter put inside the parenthesis. (You can look up this function in help to get a list of all of them and descriptions of what each does.)

                          3 ) no, but I answered in haste. You do not need to use get ( RecordID ). Any constant, such as the number 1 will do. You just need a value in that record's field with the field empty in all of the other records in the found set. That way the sort will bring the original current record to the beginning of the found set.

                          4) Please re-read my post of this morning ( two posts ago). It spells out step by step instructions for setting up set field--telling you want buttons to click and when as well as how to select the target field and how to enter the calculated result. You do not type in the semi-colon. FileMaker adds that in for you.

                          • 10. Re: FIND RECORDS without changing the open record
                            GaryMak

                            Thanks so much for your time and effort!  I really appreciate it.  However, I just can't follow you or understand what you mean.  I still don't know if you mean "Flag" is a field I create OR it's a function someplace.  (I understand Excel very well - and that has built in formula help and correction - but FM is just a different animal.)  There is no function called "GET", either, in my list - "Get (Flag)" "GetAs Boolean" etc, yes, but not "Get."   I may not be starting from the same place as you, since the results of trying to follow the steps produce error messages.  I apologize, but I think I would be wasting your time further as without a script template that I can follow, I just can't figure out your instructions over several posts.  Again, I really appreciate your time and effort, but so as not to waste your time further, since clearly I don't have the command of FM to interpret the nuances or assumptions inherent in all of this - and it must be frustrating for you - perhaps it's best if I just end it here.  Thanks again for trying to help.  

                            • 11. Re: FIND RECORDS without changing the open record
                              philmodjunk

                              Yes, flag would be a field you define in your table.

                              Get (flag) is the function, but you don't need it for this script. Look up the Get function in the fileMaker help if you want to learn more.(

                              The script might look like this.

                              Set Field [Account records::Flag ; 1 ]
                              Enter Find Mode [] //clear the pause check box
                              Set Field [Account Records::Company ; "Acme"]
                              Set Error capture [on] // this step keeps the "no records found" dialog from interrupting the script
                              Perform Find[]
                              Sort Records [Restore ; no dialog ] // specify sorting by flag in descending order
                              Go To Record/Request/Page [first]
                              Set Field [Account Records::Flag ; "" ]

                              BTW, if you select "get functions" from the view drop down, you'll get a list of all the Get functions instead of the generic get ( flag ).

                              When Setting up Set Field, there are two Specify buttons that must be  clicked. To get Set Field [Account records::Flag ; 1 ], add set field to  your script and click the first button (specify target field). Account records::Flag from the list of fields. Click OK. Now click the lower  specify button (calculated result) and 1 and click OK. Do not try to type in the semicolon.

                              • 12. Re: FIND RECORDS without changing the open record
                                RickWhitelaw

                                Phil..

                                "Get (flag) is the function" ? I don't see this function unless it's a custom one. Perhaps I'm missing something?

                                RW

                                • 13. Re: FIND RECORDS without changing the open record
                                  GaryMak

                                  Phil-

                                  CoolWOW!Laughing

                                  I can't express my amazement - and appreciation - enough. The above script works precisely!  THANK YOU!  There were a few false starts caused by me trying to figure out how you got the script to come out the way you did as there are several options for "specify," for example, which appear to lead to the same window but which lead to slightly different formula expressions and this procedure is are not intuitive.  I see by the final product that I NEVER would have figured out how to do this on my own.  Thanks!  And, after a brief scare when it sorted properly but didn't show the sales/info totals for the specific account - I found I just needed to click somewhere on the open page and then, for some reason, voîla! the totals would pop back in.  (I don't know why but I don't care and can absolutely live with this little FM quirk as it happens on some other scripts I've written and used.  No problem.)

                                  Phil again, thanks so much.  Thanks for sticking through this with me.  This is VERY helpful to me and I will study this formula and use it as a template for trying other variations and seeing how the script functions that I've never even heard of before - let alone use - work.  I hope this brings my FM competency level up a little bit.

                                  Again. much thanks!Smile

                                  GM

                                  • 14. Re: FIND RECORDS without changing the open record
                                    philmodjunk

                                    @Rick

                                    If you look in list of functions in the the specify calculation dialog, get (flag) is the placeholder for the get function. They don't specify the parameters here--presumably because it would make the list too long to be easy to work with.

                                    GaryMark is far from the first new user to be confused by that setup.