1 2 Previous Next 16 Replies Latest reply on Jan 2, 2015 5:46 PM by herby

    Filtering A Find Set

    herby

      Title

      Filtering A Find Set

      Post

      Months have gone by since I (as a 'newbie' successfully adapted the FM Solutions Contacts register as a Membership Register and the Assets Register to my own version of an Assets Register. I’m now back working on the Job Register (the version with the Found Set version that dsvail very kindly set up for me). It works perfectly for me in all respects other than being able to filter the records displayed by the found set to a date selected in a drop-down on the Dashboard layout. 
       
      My attempts have each time been stopped completely in their tracks when trying to filter the output selected from the Status drop-down menu so that it only displays records from their start date up to a selected reporting date. Since contacting you last I’ve installed FM Pro advanced - I’ve found this to be extraordinarily useful in developing/adapting the other solutions however, although I’ve stepped through the scripts countless times I haven’t ‘found’ the solution.
       
      Here is the Find based on Status script with my comments in bold type. Hate to trouble you folks again after your original sterling efforts but I would be grateful for some advice.
       
      All the very best the Holiday season and 2015. 
      Best regards, Herby
       
      11 December 2014 17:31:58 BMS JOB REGISTER Ver 6.4.fmp12 - Find based on Status -1-
      Dashboard_Invoices: Find based on Status
      Allow User Abort [ Off ]
      Set Error Capture [ On ]
      Set Field [ BMS Dashboard::gFoundSet; "" ]
      Set Variable [ $$scriptTrigger; Value:1 ]
      Set Variable [ $status; Value:BMS Dashboard::gStatus ]
      // Go to Field [ ]
      Freeze Window
      New Window [ Name: "utility"; Height: 1; Width: 1; Top: 1; Left: 1; Style: Floating Document; Close: “Yes”; Minimize: “No”;
      Maximize: “No”; Zoom Control Area: “No”; Resize: “No” ]
      Go to Layout [ “Utility | Invoices” (Invoices) ]
      If [ $status = "All" ]
      Show All Records
      Else
      Perform Quick Find [ $status ]
      Set Variable [ $error; Value:Get ( LastError ) ]
      If [ $error ]
      Set Field [ BMS Dashboard::gFoundSet; "" ]
      Close Window [ Current Window ]
      Exit Script [ ]
      End If
      End If
      Go to Record/Request/Page
      [ First ]
      Set Variable [ $id; Value:Invoices::Invoice ID ]
      If [ BMS Dashboard::gReport Date ≤ Invoices::Date Started ]
      Speak [ Text: True; Voice: “Cellos” ]
      [ Wait for completion ]
      #HERE IS WHERE I'VE BEEN TRYING TO EXCLUDE THE DESIGNATED RECORD FROM THE LIST ( Have tried OMIT
      RECORD / CONSTRAIN FOUND SET without success)
      End If
      Loop
      Go to Record/Request/Page
      [ Next; Exit after last ]
      Set Variable [ $id; Value:$id & ¶ & Invoices::Invoice ID ]
      If [ BMS Dashboard::gReport Date ≤ Invoices::Date Started ]
      Speak [ Text: True ]
      [ Wait for completion ]
      #HERE IS WHERE I'VE BEEN TRYING TO EXCLUDE THE DESIGNATED RECORDS FROM THE LIST ( Have tried
      OMIT RECORD / CONSTRAIN FOUND SET without success)
      End If
      End Loop
      Close Window [ Current Window ]
      Set Field [ BMS Dashboard::gFoundSet; $id ]
      Set Variable [ $$scriptTrigger; Value:"" ]
      Exit Script [ ]
       

        • 1. Re: Filtering A Find Set
          philmodjunk

          What is the purpose of the loop with the Speak step? What is that supposed to do here?

          The quick find also seems a bit unnecessary as I'd expect that the value in Status is only found in one field. (Quick find allows you to search for a specified value across all fields on the layout that have been enabled for quick find.) Quick find might, in unusual cases, even find records you don't want due to matching text entered in a different field.

          But here's an example of how to constrain a found set:

          Enter Find Mode []
          Set Field [YourTable::Field ; "New criteria specified here" ]
          Constrain Found Set

          The result will be limited to only those records found by the initial find that also match the new criteria specified in the set field step. The expression used in this step can include literal values as well as references to variables and global fields.

          You may find this thread of scripted find examples helpful: Scripted Find Examples

          • 2. Re: Filtering A Find Set
            herby

            Phil - The Speak loop was put there merely to audibly (with different voices) ,in addition to visual identification via the data viewer, readily identify the first from the second loop when I was experimenting with possible solutions.

            Thanks for your advice. Immediately after posting the query I tried the Enter Find Mode; Set Field [...]; but rather than Constrain Found Set I then entered Omit Record. This works except that it doesn't capture the last found record after the Exit Loop. It probably needs another Omit Record loop after this Exit Loop to catch the last found record.

            I'll try this and then your solution and let you know how I get on. - Herby

             

             

            • 3. Re: Filtering A Find Set
              philmodjunk

              While in Find mode, Omit record turns the current request into an omit request. (The same thing as entering find mode and clicking the Omit button in the status area tool bar.)

              But then what? Are you then performing a find or constraining the found set? Without such a step, you are left in find mode.

              • 4. Re: Filtering A Find Set
                herby

                 

                With respect to my adding another Omit loop to catch the last record appearing outside the filter request - it didn't work. I then tried your constrain suggestion - but I couldn't get to to work either. With regard to your post above, I don't seem to be left in find mode however, I must tell you that I've only been sing FM Pro for a few months, I'm nearly 80 and having some difficulty getting my old head around these sort of issues.

                If only I could get the last record to recognise the "If [ BMS Dashboard::gReport Date ≤ Invoices::Date Started ]" filter I'd be made. As it is I'm stumped. 

                Thanks any way for your help.
                 

                 

                 

                 

                 

                 

                 

                • 5. Re: Filtering A Find Set
                  philmodjunk

                  I suggest posting your exact script with the Constrain Found set code that didn't work.

                  To post a script to the forum:

                         
                  1. You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
                  2.      
                  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 to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                  4.      
                  5. If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                  6.      
                  7. 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 and easier to read format.
                  • 6. Re: Filtering A Find Set
                    herby

                    Phil - Screen shots are attached as requested. I made numerous attempts to enter meaningful criteria in the Constrain dialog box without success. What should I be trying to enter there?

                    Here is the script: 

                    12 December 2014 17:41:46 BMS JOB REGISTER Ver 6.4 Copy.fmp12 - Find based on Status -1-

                    Dashboard_Invoices: Find based on Status

                    Allow User Abort [ Off ]

                    Set Error Capture [ On ]

                    Set Field [ BMS Dashboard::gFoundSet; "" ]

                    Set Variable [ $$scriptTrigger; Value:1 ]

                    Set Variable [ $status; Value:BMS Dashboard::gStatus ]

                    // Go to Field [ ]

                    Freeze Window

                    // New Window [ Name: "utility"; Height: 700; Width: 1000; Top: 1; Left: 1; Style: Floating Document; Close: “Yes”; Minimize: “No”;

                    Maximize: “No”; Zoom Control Area: “No”; Resize: “No” ]

                    New Window [ Name: "utility"; Height: 1; Width: 1; Top: 1; Left: 1; Style: Floating Document; Close: “Yes”; Minimize: “No”;

                    Maximize: “No”; Zoom Control Area: “No”; Resize: “No” ]

                    Go to Layout [ “Utility | Invoices” (Invoices) ]

                    If [ $status = "All" ]

                    Show All Records

                    Else

                    Perform Quick Find [ $status ]

                    Set Variable [ $error; Value:Get ( LastError ) ]

                    If [ $error ]

                    Set Field [ BMS Dashboard::gFoundSet; "" ]

                    Close Window [ Current Window ]

                    Exit Script [ ]

                    End If

                    End If

                    Go to Record/Request/Page

                    [ First ]

                    Set Variable [ $id; Value:Invoices::Invoice ID ]

                    #START OF CONSTRAIN / OMIT LOOP

                    If [ BMS Dashboard::gReport Date < Invoices::Date Started ]

                    Enter Find Mode [ ]

                    Set Field [ BMSdashboard_INVOICES_FoundSet::Date Started ]

                    // Constrain Found Set [ Specified Find Requests: Omit Records ]

                    [ Restore ]

                    Omit Record

                    End If

                    #END OF CONSTRAIN / OMIT LOOP

                    Loop

                    Go to Record/Request/Page

                    [ Next; Exit after last ]

                    Set Variable [ $id; Value:$id & ¶ & Invoices::Invoice ID ]

                    #START OF CONSTRAIN / OMIT LOOP

                    If [ BMS Dashboard::gReport Date < Invoices::Date Started ]

                    Enter Find Mode [ ]

                    Set Field [ BMSdashboard_INVOICES_FoundSet::Date Started ]

                    // Constrain Found Set [ Specified Find Requests: Omit Records ]

                    [ Restore ]

                    Omit Record

                    End If

                    #END OF CONSTRAIN / OMIT LOOP

                    End Loop

                    // Pause/Resume Script [ Indefinitely ]

                    Close Window [ Current Window ]

                    Set Field [ BMS Dashboard::gFoundSet; $id ]

                    Set Variable [ $$scriptTrigger; Value:"" ]

                    Exit Script [ ]

                    Sorry about the spacing and lack of indenting however I not sure how to use the Known Bugs List database to paste a version as you suggested.

                    Herby

                     

                     

                    • 7. Re: Filtering A Find Set
                      herby

                      Phil - Here's the other screen shot. You may notice that the first record shown is outside the date range of the filter. Have tried all sorts of remedies however the when the last record triggers the exit Loop command, the Omit test loop is avoided.

                      • 8. Re: Filtering A Find Set
                        herby

                         

                        Phil - I'm back this time to tell you that by moving the filter code (I wrongly called it a loop in earlier posts) I have largely overcome the problem of the last record always being listed even though it should have been omitted. I did this as you will observe by locating the code to where it should have been testing the condition in the first place. 

                        Despite this there are still anomalies - for instance despite a few records being trapped by the 

                               
                        • If [ BMS Dashboard::gReport Date < Invoices::Date Started ]  test, that still don't get omitted. Is this something to do with Quick Find not being as accurate as Find?

                        I tried using 

                               
                        • Find Matching Records [ Constrain; Invoices::Date Started[ BMS Dashboard::gReport Date] ] and it gave the same result as Omit Record

                        Attached is a screen shot showing that section of the Find based on Status script. 

                        Hope to hear from you again.

                         

                         

                         

                         

                        • 9. Re: Filtering A Find Set
                          philmodjunk

                          I made numerous attempts to enter meaningful criteria in the Constrain dialog box without success.

                          If you check my examples in the other thread for which I posted a link, you'll find that I never put ANY criteria into constrain found set, enter find mode or perform find dialog boxes. Even if you can specify data there that works, it hides this detail from anyone reviewing the script step and forces them to open multiple dialogs just so see what is used. I use Set Field Steps to specify all find criteria. as I can use calculations to compute the value used as search criteria and because I can then see all the criteria without having to open any additional dialog boxes.

                          In your script, your set field step is not set up correctly:

                          Set Field [ BMSdashboard_INVOICES_FoundSet::Date Started ]

                          There should be two parameters listed there where you have one, and you are now referring to data using a different Tutorial: What are Table Occurrences? name: BMSdashboard_INVOICES_FoundSet, when previously you just referred to invoices. You need to use whichever table occurrence is specified in Layout Set up|Show Records from in both cases, so either both should read Invoices:: or both should read BMSdashboard_INVOICES_FoundSet.

                          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. Do not click the specify button next to the repetition box. Click OK to close this dialog box. 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.

                           

                          • 10. Re: Filtering A Find Set
                            herby

                            Thanks Phil - I understand now and have made the corrections as recommended. Both the Set Field/Constrain (and Find Matching Record [Constrain ...] which seems to achieve the same result) work perfectly now except that some records are still not being constrained (omitted). Using the Script Debugger indicates to me that in the instances where this occurs, the internal pointer reaches the last record and Go to Record/Request/Page [Next; Exit after Last] triggers 'Last' resulting in an exit from the loop.

                            It seems to me that as the manually entered Invoice id stored in the variable $id does not always correspond with the order in which the record was added to the database, where the preceding record being checked (If [ BMS Dashboard::gReport Date < Invoices::Date Started ] ...) is the last record in the database, the Exit after Last causes an exit from the Loop.

                            I've tried inserting Unsort Records in various spots in the script without success. I note that it only works (I think) in Found Sets. Perhaps this the problem.

                            I hesitate to trouble you further, however being so close to getting it all together prompts me to ask for help once again.

                            Regards - Herby

                             

                            • 11. Re: Filtering A Find Set
                              philmodjunk

                              I don't see why you need a loop. Can you post your corrected script and explain the purpose to the loop?

                              • 12. Re: Filtering A Find Set
                                herby

                                The Job types to be viewed in Dashboard are sorted in accordance with gStatus (All, In Progress, Completed, Stock, No Charge, Internal). The purpose of the loop is to restrict (filter) the records found to no later than a date selected (gReport Date). The loop part of the script is attached. 

                                • 13. Re: Filtering A Find Set
                                  herby

                                  The last line of the above script extract not shown in the post above is Exit Script [ ] 

                                  Attached is that portion of the script before the start of the Loop shown in the previous post. 

                                  • 14. Re: Filtering A Find Set
                                    philmodjunk

                                    Your Find matching Records step has a problem. This step finds all records with the same record as the field specified in that step. But you have two fields specified, one as the repeating field index of the other and Find matching records is executed in Browse mode not find mode, but your script first enters find mode which renders a reference to a nonglobal field like Invoices::Date Started an empty field.

                                    Seems like you need the disabled script steps that set a field and then constrain the found set instead of Find Matching records.

                                    There are a number of other issues with the script such as trying to loop through a found set that keeps changing with each loop and building a list of ID's in the $ID variable, but only one record at a time from that set of records that might be changing constantly.

                                     

                                    1 2 Previous Next