8 Replies Latest reply on Feb 8, 2011 11:18 AM by philmodjunk

    Working with text results and "set field"

    nickodm

      Title

      Working with text results and "set field"

      Post

      OK, here's my complete situation.

      We have a report that is used to show each department their pending service request. We have the status of "Pending" or "Resolved" so the report was simply using this:

      Set Field [Services::Status; "Pending"]

      This would get the results with no problem. Yet someone decided to add another status: "Work in Progress" 

      So now I'm in a pickle. Either I have to find a way to select "Pending" AND "Work in Progress" or I have to find a way to select all and exclude "Resolved".

      Thoughts? I appreciate your help with this.

        • 1. Re: Working with text results and "set field"
          philmodjunk

          Presumably, this step is part of a script that enter's find mode, uses the above set field to specify the criteria and then performs a find...

          Find both values script:

          Enter Find Mode []
          Set Field [Services::Status; "Pending"]
          new Record/Request
          Set Field [Services::Status; "Work in Progress"]
          Set Error capture [on]
          Perform Find[]

          This finds all records with "Pending" OR "Work in Progress" in the status field.

          Find all records except "resolved" script:

          Enter Find Mode []
          Set Field [Services::Status; "Resolved"]
          Omit Record
          Set Error capture [on]
          Perform Find[]

          Both scripts should produce identical results, though the second one will continue to work even if someone dreams up yet another value for status where the first would need to be updated to generate a third find request.

          • 2. Re: Working with text results and "set field"
            nickodm

            I did that and I'm getting results with the status of "Resolved". I really don't get it. :/

            Here's the script so far:

            Enter Find Mode []
            Set Field [ContractArea::OfficeName; $Office]
            Set Field [Contracts::Area; $Area]
            Set Field [Contracts::Product; $Product]
            Set Field [Services::Date; $DateRange]
            If [Get ( AccountName ) = "System"]
            Show/Hide Status Area [Show]
            Pause/Resume Script [Indefinitely]
            End If
            Go to Layout ["Service Edit" (Services)]
            Set Field [Services::Status; "Resolved"]
            Omit Record
            Set Error Capture [On]
            Perform Find []

            I have tried commending out stuff and cannot figure out why I am getting "Resolved" in the report when I run this script.

            • 3. Re: Working with text results and "set field"
              philmodjunk

              Modify the last part of your record like this so that the omit criteria is on it's own request:

              New Record/Request
              Set Field [Services::Status; "Resolved"]
              Omit Record

              That go to layout step in the middle of specifying your Find makes me nervous. It appears to have worked for you in the past, but I prefer to have the layout be selected before entering find mode. Just seems to make things a little more clear (in my mind) that the criteria being entered into ContractArea, and Contracts are criteria specified for records related to the records to be found in the Services Table Occurrence...

              • 4. Re: Working with text results and "set field"
                nickodm

                Deleted the layout line and added the New Record/Request.

                No change in the report. :(

                • 5. Re: Working with text results and "set field"
                  philmodjunk

                  Why did you delete the layout line? (I was thinking that it would be better to put this layout change at the beginning of the script rather than in the middle of setting up the find requests.)

                  What layout are you on when this script runs?

                  I assume it worked to at least find records where services::status = "pending" before you made these changes?

                  • 6. Re: Working with text results and "set field"
                    nickodm

                    Wait. I got it to work by keeping the omit statement but adding a new record/request line before the set field/omit lines and after the date range and office/area/product selection.

                    • 7. Re: Working with text results and "set field"
                      nickodm

                      And yes, the script was working fine prior to that with the set field simply being "pending". I don't understand why the new record/request would not produce the correct results on the inclusion and it was required on the omission in order for the report to work but I stumbled upon the solution simply by trying the various things Phil suggested.

                      Weird! 

                      • 8. Re: Working with text results and "set field"
                        philmodjunk

                        In find mode, New Record/Request creates a new "request". In Browse mode, it creates a new record. When executed in Find mode, Omit Record is the functional equivalent of clicking the Omit button at the top of your screen when you are in find mode and setting up a manual find.

                        "omit" request should be created after all other find requests have been created and it should only specify the criteria you don't want to see. Filemaker first finds the records matching the preceding requests, then uses the criteria in the final, omit request to omit any records found that match the omit criteria.