2 Replies Latest reply on May 11, 2015 4:26 AM by MariusNergård

    Making a search based on a search



      Making a search based on a search



      I've been trying to wrap my head around this, but Im not very familiar with FM scripting.

      What i am trying to do is to first find a set of unprocessed orders that is older than 2 days, make an array of the companies from that search, and then make a new set with all unprocessed orders from all of those companies.

      I dont know if I made myself clear, but basically I want a recordset of all orders from companies that has unprocessed orders that are older than 2 days, and then also include newer orders from those companies if they have one.

      I was thinking of :

      • making the search on all unprocessed orders older than 2 days
      • looping the search adding companyID to an array
      • then make a new search on all unprocessed orders having companyID in that array.


      But im not sure (beyond making the seach) on how to do this.
      Is there even possible to make an array in Filemaker?

      I come from PHP/MySQL..


        • 1. Re: Making a search based on a search

          It's possible to make an array using a repeating variable, but you can also just build a return list for the same purpose.

          ExecuteSQL is a function that could also be used to get a list of the Order ID's for unprocessed orders older than two days.

          You don't provide enough detail to just follow this script example exactly, but you should be able to adapt it to the actual names and values used in your database.

          a) Define a "list of" summary field: sCompanyIDList to list the value of the Company ID field, _fkCompanyID

          b) write and run this script:

          #Find all unprocessed orders 2 or more days old starting from a layout based on orders:
          Enter Find Mode []
          Set Field [Orders::OrderDate ; "<" & Get ( CurrentDate ) -2 ]
          New Record/Request
          Set Field [Orders::Status ; "Processed" ]
          Omit Records
          Set Error Capture [on]
          Perform Find[]
          If [ Get ( FoundCount ) ]
             Set Variable [$CompanyIDList ; Orders::sCompanyIDList ]
                Set Variable [$K ; value: $K + 1 ]
                Exit Loop If [ $K > ValueCount ( $CompanyIDList ) ]
                Enter Find Mode []
                Set Field [ Orders::_fkCompanyID ; GetValue ( $CompanyIDList ; $K ) ]
                New Record/Request
                Set Field [Orders::Status ; "Processed" ]
                Omit Records
                Extend Found Set[]
          End loop

          This script will not work if used with FileMaker 12 or older as it uses a summary field type introduced with the release of FIleMaker 13, but other methods exist that can create that list. The list of company ID's could also be produced (In FileMaker 12 or newer), using an ExecuteSQL query and a return character for the record separator.

          For examples of other scripted finds, see: Scripted Find Examples

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Making a search based on a search

            Thanks PhilModJunk!

            I ended up with a slightly different solution, but you got me in the right direction! :)