4 Replies Latest reply on Aug 31, 2012 1:56 PM by JimBones

    FMP 11: Set Field while in Find Mode

    JimBones

      Title

      FMP 11: Set Field while in Find Mode

      Post

      Hi - Please excuse my long post. My background is in English Literature so brevity is difficult for me.  I have gleaned a TON of information from these support forums and I appreciate all the knowledgeable people here. I hope one day I can give back to the community a bit. 

      First though, I need to some more learning. I am pretty green with Filemaker but I am attempting to create a new database for one of our departmental mailing lists.

      The mailing lists have a code, such as 100, 101, 102, etc. Each mailing list has an appended number that tells the users when it was used previously, such as -01, -02, or whatnot, so the complete code is 100-01, 100-02 and so on.

      However when these are searched, only the initial three digits will be needed, but I want to encompass all the appended numbers.

      In order to do this, I have created a temporary holding table to take the user input and append a * to the data. That is, when a user inputs 100, I have that move to a table and result in 100* to search all mailing lists that start with 100. 

      The problem is when I try to search these appended lists. I cannot Set Field for the mail list code and I think it has to do with this going across tables that are joined by a cartesian join (but then again I might be completely wrong). The join was created to get rid of the <unrelated table> error I received when adding the holding table data to the layout (as this holding data is on a separate table).

      My end goal here is to be able to have the user enter several mail codes, then hit "search." What I want Filemaker to do is search on each mail code. My assumption is that I have to do a Find, then extend the found set each time and continue to do a find, extending it until all mail codes have been searched. 

      This is my script:

      Freeze Window
      #Check to ensure the Search Box has data
      If [ IsEmpty ( TemporaryData::SearchCriteria ) ]
      Show Custom Dialog [ Title: "Search Box Error"; Message: "Nothing to search"; Buttons: “OK” ]
      Exit Script [ ]
      End If

      #Add a wild card to each search criteria item. Add to "searchmodified" field.

      Go to Layout [ “TemporaryData” (TemporaryData) ]
      Go to Record/Request/Page [ First ]
      Loop
      Set Field [ TemporaryData::SearchModified; TemporaryData::SearchCriteria & "*" ]
      Go to Record/Request/Page [ Next; Exit after last ]
      End Loop

      #Go back to original layout, search on criteria

      Go to Layout [ original layout ]
      Enter Find Mode [ ]
      Set Field [ Mailing Lists::MailListCode; TemporaryData::SearchModified ]
      Perform Find [ ]

       

      Everything works fine up until the second to the last line here. The field MailListCode does not receive the SearchModified data.

      My sense is that Filemaker isn't sure what it is supposed to do with Set Field here because the SearchModified field could contain more than one record. I've messed around with a loop to have it go to SearchModified, search, then extend the set to include the next record in SearchModified, but I am clearly in over my head.

      The logic works in my head: Enter find mode, search on the first record in SearchModified. Then get the next record in SearchModified and add the results to the first find. Continue until no records remain in SearchModified. Spit out the results.

      Is the join screwing things up? And if so, the unrelated table error returns. I've read some posts that say a cartesian join will get rid of the error (and it did), but other posts say you shouldn't use cartesians unless there is a very special circumstance (which this most likely is not).  I have attached a screen shot of this very simple database.

      Any feedback would be greatly appreciated. Thank you!

      Manage_Database_for_%E2%80%9CCEMailingListSearchable0815%E2%80%9D_2012-08-22_15-47-53.jpg

        • 1. Re: FMP 11: Set Field while in Find Mode
          philmodjunk

          It's not the relationship, it's the mode you are in.

          Try this test:

          go to any layout and enter find mode.

          What happens to the data you could see in the fields before you entered find mode?

          Notice that the fields now are empty so that find criteria can be entered?

          The same happens in your script. Once you enter find mode, the set field step can no longer access data in the fields of your related table, they all appear blank to the script just like they do to you. The only fields that will retain data while in Find Mode, are fields with global storage specified. If you were only specifying a single criterion for this field, changing the storage option for that field would be all that you need to do, but since you have a list of values to use for criteria, that won't work.

          Try this modification to your script:

          Go to Layout [ original layout ]
          Set Variable [$CriteriaList ; List ( TemporaryData::SearchCriteria ) ]
          Enter Find Mode [ ]
          Loop
            Set variable [$K ; value: $K + 1 ]
            Set Field [ Mailing Lists::MailListCode; GetValue ( $CriteriaList ; $k ) & "-*"  ]
            Exit Loop If [$K > ValueCount ( $CriteriaList ) ]  --> warning, you can't copy and paste this expression into FileMaker, type it in or edit the operator
            New Record/Request
          End Loop
          Set Error Capture [on]
          Perform Find [ ]

          Notes:

          1) Values in variables are accessible while in find mode, which is why this script puts the list of criteria into a variable

          2) This script removes the need for your second script as it adds the wild card operator as part of the same loop

          3) The script appends a "-" to the critera so that entering 100 will match to 100-01, 100-02, but not to 1000-01

          4) New Record/Request when performed in find mode generates a new request. A find with multiple requests finds the specified records for each request and and combines them in one fond set so this does the same as the "Extend found set" option you mentioned in your post.

          • 2. Re: FMP 11: Set Field while in Find Mode
            JimBones

            PhilModJunk -- you are PhilModGOLD!  It worked perfectly and your explanation is great as well.  I planned on creating the option for more searches, using this one as a guide (thinking if it will work for one of them, I can retool it to work for all of them).  So this is VERY much appreciated! 

            The notes at the end, also great. Each question I had pop into my head was answered in that section. 

            Again - thank you!

            • 3. Re: FMP 11: Set Field while in Find Mode
              philmodjunk

              One more comment, if this is for a database that will be accessed by more than one user at a time, your method for using a table will need some enhancement or your scripts will combine search criteria from all of your users.

              • 4. Re: FMP 11: Set Field while in Find Mode
                JimBones

                Thanks for that!

                I have a method to clear the search criteria, but I hadn't considered if more than one user was trying to search at the same time. I'll add that to my list. Smile