7 Replies Latest reply on Nov 27, 2015 10:23 AM by macwombat

    Scripted Find with multiple variables

    williamrollo

      I have seen PhilModJunk's post on Scripted Find Examples. However I am a little stuck.

      I want the user to be able to select a radio button from each of the two multiple variables that i have.

      I want radio buttons as there is only one variable I want them to see, in each of the two fields. EG Car Make and Colour.

      I also want a date variable to be added. The result of the found count should appear in a field on the same page layout. I have it working for one variable and the date. But not sure how to use two.. I have butchered my working script by following Phil's script suggestion but am not getting the correct find counts. ... I can supply more info of course but this is just an intro. Script below:

      Thank you

       

      • Set Error Capture [ On ]
      • Allow User Abort [ Off ]
      • Freeze Window
      • Commit Records/Requests [ Skip data entry validation; No dialog ]
      • #Validate Dates
      • If [ IsEmpty ( PREF::gFinish ) and IsEmpty ( PREF::gStyle ) ]
      • Show Custom Dialog [ Title: "Warning"; Message: "You need to enter a finish or style or both."; Default Button: “OK”, Commit: “Yes” ]
      • Go to Layout [ “Reports” (PREF) ]
      • Exit Script [ ]
      • End If
      • If [ PREF::gDateStart and IsEmpty ( PREF::gDateEnd ) or PREF::gDateEnd and IsEmpty ( PREF::gDateStart ) ]
      • #Missing both start and end dates
      • Show Custom Dialog [ Title: "Warning"; Message: "You need to have both a start and end date."; Default Button: “OK”, Commit: “Yes” ]
      • Go to Layout [ “Reports” (PREF) ]
      • Exit Script [ ]
      • Else If [ PREF::gDateStart > PREF::gDateEnd ]
      • #Start date greater than end date
      • Show Custom Dialog [ Title: "Warning"; Message: "The end date must be on or after the start date."; Default Button: “OK”, Commit: “Yes” ]
      • Go to Layout [ “Reports” (PREF) ]
      • Exit Script [ ]
      • End If
      • If [ IsEmpty (PREF::gDateStart) and IsEmpty (PREF::gDateEnd) ]
      • #No date specified, find all
      • Show Custom Dialog [ Title: "Warning"; Message: "Please enter date range."; Default Button: “OK”, Commit: “Yes” ]
      • Go to Layout [ “Reports” (PREF) ]
      • Exit Script [ ]
      • Else
      • Freeze Window
      • #Find by specified date range.
      • Go to Layout [ “Report orders by type” (INVOICEITEMS) ]
      • Set Variable [ $Style; Value:PREF::gStyle ]
      • Enter Find Mode [ ]
      • Set Field [ INVOICEITEMS::fk_order Date; PREF::gDateStart & ".." & PREF::gDateEnd ]
      • Loop
      • Set Variable [ $K; Value:$K+1 ]
      • Set Field [ INVOICEITEMS::Fender Style; GetValue($Style;$K) ]
      • Exit Loop If [ $K ≥ ValueCount($Style) ]
      • New Record/Request
      • End Loop
      • Set Error Capture [ On ]
      • Perform Find [ ]
      • Set Field [ INVOICEITEMS::Fender style Find; Get(FoundCount) ]
      • End If
      • If [ Get (FoundCount) = 0 ]
      • #No records found
      • Show Custom Dialog [ Title: "Message"; Message: "No orders found within the specified date range."; Default Button: “OK”, Commit: “Yes” ]
      • Go to Layout [ “Reports” (PREF) ]
      • End If
      • Go to Layout [ “Reports” (PREF) ]
        • 1. Re: Scripted Find with multiple variables
          macwombat

          If I'm understanding you correctly, you would like to find two different variables as well as records within the date range.  For instance find cars with a Fender Style of $Style and with Colour of $Colour.

          How about adding a loop within the loop to set the second variable?

           

          By the way, this step:

           

          Set Field [ INVOICEITEMS::Fender style Find; Get(FoundCount) ]

           

          Its only going to set the value in the first record of the found set.  So if you find 10 records that match your variables you will need to do a replace to set the value in all the records.

          • 2. Re: Scripted Find with multiple variables
            BruceRobertson

            Is there in fact a field INVOICEITEMS::fk_order Date

            Seems unlikely. And if it does exist, how is it populated?

            • 3. Re: Scripted Find with multiple variables
              BruceRobertson

              Also note that in the case that there are multiple Fender styles selected, you probably want to duplicate the request; then set the style of the duplicated request to value $k of the style list. Otherwise only the first find request is affected by the date search you have specified.

              • 4. Re: Scripted Find with multiple variables
                williamrollo

                Thank you all.

                The INVOICEITEMS::fk_order Date is populated own creation of the invoice items line record. it gets the data from the related table, INVOICEDetails::fk_order Date by using a set variable script.

                Thank you Chris for your suggestion of a loop with in a loop. I am a novice when it comes to loops but will give it a go.

                If I want the result from the find to appear in a field box on the same layout, how should I go about this, if my Get(found count) isn't then best way? Also added screen grab of the layout I have to show went fender styles and finishes, radio buttons. If I select on e style, I want the toal number of those types of styles ordered. If I also click a finish style< want this total to be narrowed down to just show, e.g., brass,  curved dip...

                I have altered by script to this: (not sure my loop in a loop has made much difference - when I select a finish, the result shoes the total amount of records...)

                • Set Error Capture [ On ]
                • Allow User Abort [ Off ]
                • Freeze Window
                • Commit Records/Requests [ Skip data entry validation; No dialog ]
                • #Validate Dates
                • If [ IsEmpty ( PREF::gFinish ) and IsEmpty ( PREF::gStyle ) ]
                • Show Custom Dialog [ Title: "Warning"; Message: "You need to enter a finish or style or both."; Default Button: “OK”, Commit: “Yes” ]
                • Go to Layout [ “Reports” (PREF) ]
                • Exit Script [ ]
                • End If
                • If [ PREF::gDateStart and IsEmpty ( PREF::gDateEnd ) or PREF::gDateEnd and IsEmpty ( PREF::gDateStart ) ]
                • #Missing both start and end dates
                • Show Custom Dialog [ Title: "Warning"; Message: "You need to have both a start and end date."; Default Button: “OK”, Commit: “Yes” ]
                • Go to Layout [ “Reports” (PREF) ]
                • Exit Script [ ]
                • Else If [ PREF::gDateStart > PREF::gDateEnd ]
                • #Start date greater than end date
                • Show Custom Dialog [ Title: "Warning"; Message: "The end date must be on or after the start date."; Default Button: “OK”, Commit: “Yes” ]
                • Go to Layout [ “Reports” (PREF) ]
                • Exit Script [ ]
                • End If
                • If [ IsEmpty (PREF::gDateStart) and IsEmpty (PREF::gDateEnd) ]
                • #No date specified, find all
                • Show Custom Dialog [ Title: "Warning"; Message: "Please enter date range."; Default Button: “OK”, Commit: “Yes” ]
                • Go to Layout [ “Reports” (PREF) ]
                • Exit Script [ ]
                • Else
                • Freeze Window
                • #Find by specified date range.
                • Go to Layout [ “Report orders by type” (INVOICEITEMS) ]
                • Set Variable [ $Style; Value:PREF::gStyle ]
                • Set Variable [ $Finish; Value:PREF::gFinish ]
                • Enter Find Mode [ ]
                • Set Field [ INVOICEITEMS::fk_order Date; PREF::gDateStart & ".." & PREF::gDateEnd ]
                • Loop
                • Set Variable [ $K; Value:$K+1 ]
                • Set Field [ INVOICEITEMS::Fender Style; GetValue($Style;$K) ]
                • Exit Loop If [ $K ≥ ValueCount($Style) ]
                • New Record/Request
                • Loop
                • Set Variable [ $K; Value:$K+1 ]
                • Set Field [ INVOICEITEMS::Fender Finish; GetValue($Finish;$K) ]
                • Exit Loop If [ $K ≥ ValueCount($Finish) ]
                • New Record/Request
                • End Loop
                • End Loop
                • Set Error Capture [ On ]
                • Perform Find [ ]
                • Set Field [ INVOICEITEMS::Fender style Find; Get(FoundCount) ]
                • End If
                • If [ Get (FoundCount) = 0 ]
                • #No records found
                • Show Custom Dialog [ Title: "Message"; Message: "No orders found within the specified date range."; Default Button: “OK”, Commit: “Yes” ]
                • Go to Layout [ “Reports” (PREF) ]
                • End If
                • Go to Layout [ “Reports” (PREF) ]

                 

                2015-11-26 08.50.53 am.png


                • 5. Re: Scripted Find with multiple variables
                  macwombat

                  Hi William.  I'm wondering if the scripted approach might be unnecessarily complicated to achieve what you want.  I've attached a sample file for you to have a look at. 

                  https://dl.dropboxusercontent.com/u/27538907/Alternative%20to%20Scripted%20Find.fmp12.zip

                   

                  The idea is to use relationships between table occurrences matching from global variable fields in your "dashboard" looking across to your orders that are within the date range and that have matching FenderStyle & Colour. (not sure if i'm fully understanding your data correctly but the methodology can be copied to your system fairly easily).

                   

                  Then you can choose your radio buttons on the dashboard, see the number of matching orders and go to those orders via a button if you want.

                   

                  Screen Shot 2015-11-27 at 5.01.27 pm.png

                  • 6. Re: Scripted Find with multiple variables
                    williamrollo

                    Dear Chris

                    This works perfectly and I have integrated it into my solution. Thank you

                    • 7. Re: Scripted Find with multiple variables
                      macwombat

                      Pleasure!  Glad it was what you were wanting