7 Replies Latest reply on Nov 13, 2009 4:16 PM by jjbong

    Loop function to append text in a field

    trialuser1111

      Title

      Loop function to append text in a field

      Post

      I have a database, Contacts, and a field, Saved Searches (I realize that FileMaker has a saved search function, I can't get that to do what I want it to do).  After a user performs a standard search based on specific criteria, there is some subjective decision making that goes into the finalization of a list.  I would like to be able to save these lists for reference later across the group.  Currently, I have a button called "Save Search" which executes as such:

       

      Show Custom Dialog ["Save Search"] (prompts the user to enter text into the Search Name field)

      Go to First Record

      Copy [Select, Contacts::Search Name]

      Past [Contacts::Saved Searches]

      Loop

         Go to Next Record

         Paste [Contacts::Saved Searches]

      End Loop

       

      Now there are a few problems here.  The first is that the loop never ends because I don't know how to tell it to stop when it hits the last record in the found set.  The second is that the first record often has the search name pasted repeatedly.  Ideally, I would like the script to insert a return/new line in the Saved Searches field, then paste the text from Search Name, then move on through the found set until complete.  If possible, I would also like to add the Search Name text to a value list of search names so that future users may select them from a drop-down.

       

      Any help is appreciated.  Thanks. 

        • 1. Re: Loop function to append text in a field
          philmodjunk
            

          First suggestion: never ever use copy and paste unless you have no alternative. Copy and paste require their referenced fields to be present on the current layout in order to work. That's the case now, but if someone changes the layout, later the script breaks. It also destroys any data the user has copied to the clipboard and that tends to tick off your users.

           

          Here's an improved version of your script:

           

          (I am assuming Contacts::Search Name is a global field, if not make it global.)

          Show Custom Dialog ["Save Search"] (prompts the user to enter text into the Search Name field)

          Go to First Record

          Loop

            Set Field [Contacts::Saved Searches ; If (not IsEmpty(Contacts::Saved Searches); Contacts::Saved Searches & "¶" ; "" ) & Contacts::Search Name ]

            Go to Record [Exit after Last; next]

          End Loop

          • 2. Re: Loop function to append text in a field
            trialuser1111
              

            Thanks.  This works perfectly.  Any ideas on how to add to the value list?

             

            Also, is there a function or set of functions that would make it easy to delete one of these lists in the future?

            • 3. Re: Loop function to append text in a field
              philmodjunk
                

              "Any ideas on how to add to the value list?"

              What value list?

               

              Also, is there a function or set of functions that would make it easy to delete one of these lists in the future?

              There's a number of text functions that might be used for this. Several are specifically designed for working with lists of values such as this script creates.

               

              One approach:

               

              Substitute ( Substitute ( yourlistfield ; itemtoremove; "") ) ; "¶¶" ; "¶" )

               

              will remove an item and also eliminate any extra carriage returns as well. You can use this function with Replace Field contents to make this change on all the records in a found set or use a looping script like the above example and perform this operation one record at a time.

               

              • 4. Re: Loop function to append text in a field
                trialuser1111
                  

                Substitute ( Substitute ( yourlistfield ; itemtoremove; "") ) ; "¶¶" ; "¶" )

                 

                You're two for two, thanks!

                 

                Regarding the value list--I'd like to have each saved search name be logged into a value list so that people can see the old searches that they and their peers have run in a drop-down menu.

                • 5. Re: Loop function to append text in a field
                  philmodjunk
                    

                  trialuser1111 wrote:

                  Regarding the value list--I'd like to have each saved search name be logged into a value list so that people can see the old searches that they and their peers have run in a drop-down menu.


                  The easiest is to put your values in a table and define your value list to take its values from that table. Now managing your list of searches can be done simply by adding/changing/removing records from this table.


                  • 6. Re: Loop function to append text in a field
                    trialuser1111
                       Everything works exactly as I wanted now.  Thank you!
                    • 7. Re: Loop function to append text in a field
                      jjbong
                        

                      One approach:

                       

                      Substitute ( Substitute ( yourlistfield ; itemtoremove; "") ) ; "¶¶" ; "¶" )

                       

                       

                      I just came across this, researching a similar problem. There's a subtle problem with this straightforward approach.

                       

                      If the list has two different items of the form "Item 1", "Item 1 Picture", the calculation above won't work.

                      Same problem for "Item 1", "xxx Item 1 yyy".

                       

                      A more complicated approach does the right thing (I believe):

                       

                      Let(

                          [InitialString = Substitute (Substitute("¶"&Array&"¶";"¶"&Item&"¶";"¶");"¶¶";"¶");

                           WithoutLeading = If(Length(InitialString)>0;If(Middle(InitialString;1;1)="¶";

                                Middle(InitialString;2;Length(InitialString)-1);InitialString);"");

                          WithoutTrailing = If(Length(WithoutLeading)>0;If(Middle(WithoutLeading;Length(WithoutLeading);1)="¶";

                                Middle(WithoutLeading;1;Length(WithoutLeading)-1);WithoutLeading);"")];

                          WithoutTrailing)

                       

                      The important line is the first line, which wraps both the array and the item to delete with leading  ¶ characters,

                      so that you're only deleting the text if it's an entire item in the array. The remaining text just gets rid of leading

                      and trailing ¶'s. If you don't care about the latter, you don't need the Let function, and you can just use

                      the double-substitute.