1 2 Previous Next 27 Replies Latest reply on Feb 22, 2017 11:24 AM by philmodjunk

    Script to pull un"Returned" / "overdue" rentals forward via button

    BERGSTEN

      Hello,

       

      My latest issues is in trying to figure out the right script as to pull records that are past their load_out_date and not set to "Returned" in two different fields.

      Ideally- we would press a button that would take us to the invoices layout- performing a search that would pull forward all of the records that were not toggled to returned, one day after it was supposed to be returned.

       

      I had found the following script and was trying to modify it to my needs but have had no success. Either the find comes back with an error, or it tells me I'm all clear when I know I'm not. Based on what Im trying to do it doesnt seem like it should be this difficult! A little frustrated. Just when I thought I was getting better at scripting!

       

      Go To Layout ["invoices" (Invoices)]

      Enter Find Mode

      Set Field [Invoices::Load_Out_date; ≤ Get(CurrentDate)]

      Set field [Invoices::Prep_Status ≠ "RETURNED"]

      Set field [Invoices::Prep_Status_audio ≠ "RETURNED"]

      Pause [indefinitely]

      Set Error Capture [On]

      Perform Find []

      If [Get(FoundCount)>0]

      .. Show Custom Dialog ["These rentals weren't checked in" / OK]

      Else

      .. Show Custom Dialog ["There are no rentals needing your attention" / OK]

      .. Show All Records

      .. Go to Layout [Main Menu Screen or Whatever]

      End If

        • 1. Re: Script to pull un"Returned" / "overdue" rentals forward via button
          coherentkris

          remove the pause and step thru the script with the debugger..

          • 2. Re: Script to pull un"Returned" / "overdue" rentals forward via button
            BERGSTEN

            Woops, the pause has been off.

             

            I am very new to all of this, is there a debugger option on FMP? I don't have advanced.

             

            Thanks

            • 3. Re: Script to pull un"Returned" / "overdue" rentals forward via button
              philmodjunk

              Set field [Invoices::Prep_Status ≠ "RETURNED"]

              Set field [Invoices::Prep_Status_audio ≠ "RETURNED"]

               

              Both of these steps have multiple errors.

               

              They are missing the target field parameter. In find mode, both calculations return the value 1 or True as an empty field (and in find mode all fields except global ones are empty) will never equal the text in quotes. That is both the wrong value and a value that is never entered as find criteria.

              Go To Layout ["invoices" (Invoices)]

              Enter Find Mode

              Set Field [Invoices::Load_Out_date; ≤ Get(CurrentDate)]

              new record/request

              Set field [Invoices::Prep_Status ; "RETURNED"]

              omit records

              new record/request

              Set field [Invoices::Prep_Status_audio ; "RETURNED"]

              Omit records

              Pause [indefinitely]

              Set Error Capture [On]

              Perform Find []

               

              My example assumes that you want to exclude any records with "returned" in prep_status OR in Prep_status_audio.

               

              New record/request, creates a new find request which will then produce an "or" result. Omit Records strangely enough is what you use when in find mode to select the "omit" option for the find request.

              • 4. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                philmodjunk

                a script debugger is only available in Advanced. you need advanced.

                • 5. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                  BERGSTEN

                  Your assumption is correct. Thank you!

                   

                  For whatever reason- your suggested script isn't doing the trick.

                   

                  Go to layout [ "Invoices" (invoices) ]

                  Enter Find Mode (pause: off)

                  Set Field [Invoices::Load_Out_Date ; Invoices::Load_Out_Date ≤ Get(CurrentDate) ] **I couldn't get it to format as you suggested

                  New Record/Request

                  Set Field [ Invoices::Prep_Status ; Invoices::Prep_Status = "RETURNED ] **I couldn't get it to format as you suggested

                  Omit Multiple Records [With Dialog: off]

                  New Record/Request

                  Set FIeld [ Invoices::Prep_Status_audio ; Invoices::Prep_Status_audio = "RETURNED" ] **I couldn't get it to format as you suggested

                  Omit Multiple Records [With Dialog: off]

                  Set Error Capture [On]

                  Perform Find [ ]

                   

                  Can't imagine why- but when I go to perform the script- it just pauses. I realized that was because of the pause indefinitely so I removed that- but even so, once I engage with the button, it takes me to the layout without pulling any records forward.

                  • 6. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                    philmodjunk

                    **I couldn't get it to format as you suggested

                     

                    And that's why it's not working. It has to be that way. My previous post explained why the expression that you used won't work.

                     

                    In the scripts workspace, select Prep_Status from Invoices as the "target field". Then click Calculated result and enter the text in quotes. Do not enter any semi colon or = sign.

                    • 7. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                      BERGSTEN

                      I was able to fix formatting on the prep status lines but for the first set field - the load_out_date ≤ Get(CurrentDate) ] -- the calculation section won't allow me to simply enter ≤ Get(CurrentDate) ]

                      I get an error "A number, text constant, field name or ")" is expected here"

                      If i put the ≤  with " " around it- it asks for an operator.

                      • 8. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                        philmodjunk

                        You are close. The missing operator would be the & placed between the quoted text and function call.

                        • 9. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                          BERGSTEN

                          Okay yes I was able to get that function working.

                           

                          The script appeared to work on my test database but when I applied it to my database of over 20k records, it is still showing me records that have been marked as "Returned". It may be because 'back in the day' I only had one status bar, not two - so the script may interpret the empty status boxes as "not returned".

                          Which leads me to this: is there a way to constrain the year from which it pulls from? The main database has records going back to 2011- but I am hoping to simply search and pull records that are current, 2017 invoices.

                           

                          It's also showing me load out dates that haven't happened yet (the goal is to find rentals that were returned say, the day before, and were never checked in).  So if today is the 21st, I want to find all records that show a load out date up to the 20th, that are not marked as returned - and so on.

                           

                           

                          EDIT: Also, let's just say I wanted to pull from all the records that are considered "LOADED" (that means the rental actually went on, vs. quotes that may have never happened) instead of searching by and omitting the "RETURNED" - is that easily doable? It occurred to me that using the "Loaded" status may avoid pulling forward unnecessary records.

                           

                          Thank you very much.

                          • 10. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                            philmodjunk

                            I suggest posting the current version of your script.

                             

                            You can add another request to omit records where the load out date is empty if that's the records appearing that shouldn't. I assumed that all these records had load out dates. All you need is "=" to specify that a field be empty. It helps to work out how to do these finds manually and then your set field steps simply recreate the criteria that you were entering manually to produce the same result.

                             

                            As to "loaded", it's not clear to me how that works in your records and so I'd need to know more.

                            • 11. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                              philmodjunk

                              Oh yes, you could add criteria that specifies the current year by using:

                               

                              Year ( get ( CurrentDate ) )

                               

                              as the criteria entered into a date field.

                              • 12. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                                BERGSTEN

                                Go To Layout ["Invoices (invoices) ]

                                Enter Find Mode (Pause: off)

                                Set Field [Invoices::Load_Out_Date ; "≤ " & Get(Current Date) ]

                                New Record/Request

                                Set Field [Invoices::prep_status ; "RETURNED" ]

                                Omit Multiple Records [With Dialog: Off ]

                                New Record/Request

                                Set Field [Invoices::Prep_Status_Audio ; "RETURNED" ]

                                Omit Multiple Records [With Dialog: off ]

                                New Record Request

                                Set Field [Invoices::Invoice_Status ; "FINAL INVOICE" ]

                                Set Error Capture [on]

                                Perform Find

                                 

                                 

                                -When I tried to add the year to the get current date it would not work, asking yet again for an operator. I'm not sure if the ≤  and & sign are complicated it.

                                 

                                -An invoice/rental goes through different phases on a radio checklist. From "not prepd", "GB'ed" (looked over by management", edit!, to "Loaded" and "Returned".

                                Presently we have this script set to look for invoices that are not set to returned- when in reality, we're only concerned with rentals/invoices that were "Loaded" and never set to returned. Loaded means the equipment left the building. Those are the ones we're trying to catch. Screen Shot 2017-02-22 at 10.38.22 AM.png

                                As of right now, the script is pulling forward all records (quotes, work orders and final invoices already set with returned- regardless of the dates). As mentioned, the goal is to pull forward only the records that have passed the load in date, and are still "loaded".

                                • 13. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                                  philmodjunk

                                  You should be using

                                  Omit Records, not Omit multiple records.

                                   

                                  Change that detail first and report back as to what results you get.

                                  • 14. Re: Script to pull un"Returned" / "overdue" rentals forward via button
                                    BERGSTEN

                                    Screen Shot 2017-02-22 at 10.48.16 AM.png

                                    This is a good example - the load out date is tomorrow, and yet when I ran the script exactly as it is seen above- it's pulling it forward anyway.

                                    Ideally, I wouldn't see this until running the script on the 24th. It would force me to go out to the workshop to see if the gear came back or not (and if so, lecture our staff as to why they didn't set the record as returned)

                                    1 2 Previous Next