10 Replies Latest reply on Apr 3, 2015 1:37 PM by MattMinde

    Want to automate searching by four fields via Value List using script...

    MattMinde

      Title

      Want to automate searching by four fields via Value List using script...

      Post

      Hello all! 

      I'm building a table of contents for a local phone directory our newspaper produces each year, and would like to sort records of various businesses by various Categories. A business record may have up to four categories under which it needs to be be listed.

      In my DB, each record has four Category fields, all of which have the same value list. Records are categorized via selections from each of the four fields (again, a record may have more than one Category under which it's listed). For example, the business "Hotel A" may be categorized under Accommodations in the first Category field, and Conference Facilities in the second. The business "Visitor Center B" may have Conference Facilities in the first Category field, and Performance Venues in the second. Both "Hotel A" and "Visitor Center B" have "Conference Facilities" in common, and need to be found, sorted and exported under "Conference Facilities." ("Hotel A" will also need to be found and listed with other hotels and motels under "Accommodations", etc.)

      Now, this may be asking the moon, but I would like to: 

      1) Use each Category in the Value List (about 60 items) to search each of four fields, and expand the search each time;
      2) Sort the found records alphabetically;
      3) Export the "Business Name" field of the found set, and include the Value List item used for the search along with the exported text; and
      4) Automate this process until the list is complete (i.e., loop until the end of the list is reached...)

      I've scripted the search function, but currently, to define the search, I have to manually select the Value List item in each of the four Category fields each time. I also cannot figure out a way to export the Value List item I used in the search, though I've got a separate field for it.

      I have no idea if or how this could be automated. Is this totally unreasonable? I am using FMPro 6 — yes, dated, but still functional. Any input would be greatly appreciated. 

      THANKS! —MM

        • 1. Re: Want to automate searching by four fields via Value List using script...
          philmodjunk

          Set up a global text field to use for your scripted search. Format it as a drop down list with your value list of categories. I'd also set it to auto-complete, but that's an option for you to select or not as you see fit.

          Then use this scripted find method:

          Enter Find Mode [] ----> no stored criteria, clear the pause check box
          Set field [YourTable::categoryField1 ; YourTable::YourGlobalCategoryField ]
          Set Error capture [on]
          Perform Find[]
          Enter Find Mode [] ----> no stored criteria, clear the pause check box
          Set field [YourTable::categoryField2 ; YourTable::YourGlobalCategoryField ]
          Set Error capture [on]
          Extend Found Set[]
          Enter Find Mode [] ----> no stored criteria, clear the pause check box
          Set field [YourTable::categoryField3 ; YourTable::YourGlobalCategoryField ]
          Set Error capture [on]
          Extend Found Set[]
          Enter Find Mode [] ----> no stored criteria, clear the pause check box
          Set field [YourTable::categoryField4 ; YourTable::YourGlobalCategoryField ]
          Set Error capture [on]
          Extend Found Set[]

          This is patterned after the scripted find examples found here: Scripted Find Examples

          • 2. Re: Want to automate searching by four fields via Value List using script...
            philmodjunk

            I forgot to mention that you can then export the global field along with the business name and that Sort records can be used after the last "extend found set" to sort your records into alphabetical order.

            • 3. Re: Want to automate searching by four fields via Value List using script...
              MattMinde

              I want to thank you in advance, Mr. PhilModJunk! I have yet to attempt to implement this, but wanted to express my gratitude for your taking this time! —MM

              • 4. Re: Want to automate searching by four fields via Value List using script...
                MattMinde

                Hello again, Mr. Phil! 

                First off, thank you, thank you! Your suggestion for automating the search-and-sort worked, even in my ancient FMPro 6 (though there's no target field, no $$, no semicolons, just a simple SetField ["Field A", "Field B"] ). But it worked! This is a real timesaver! 

                So the next big questions are, if you will bear with me: 

                1) Is there any way to export the Global Field only once per search-sort-export? Ideally, I'd like to get:

                Accommodations<tab>Hotel A<tab>Hotel B<tab>Hotel C, etc...

                Currently, I'm getting  

                Accommodations<tab>Hotel A<tab>Accommodations<tab>Hotel B<tab>Accommodations<tab>Hotel C, etc...

                2) Is there a way to automate the function (i.e., loop or if...then) to use the Global Field Value List items and go through the above search-find-export process until the end of the list is reached? This would be a boon, as we need to generate this material yearly,  there are over 80 categories, and — though I don't intend to have it happen — the hit-by-a-bus scenario looms such that I would want to set this up for anyone to do  as conveniently as possible. 

                3) Is there any way to aggregate the found sets into one file? Ideally, I'd like for the export to generate a table of contents, so that each item from the Global Field is a header for listings underneath. 

                Ideally, each found-sorted-exported set could be pasted into one giant text field, which could then be exported. I've done lots of cut-and-paste content from calculation via script, but I can't readily see how I could get this to work here. 

                I was also contemplating possibly trying to export/append/import each found-sorted set into another database, which would generate a new record for each found-sorted-exported set. 

                Are any of these three items feasible? any hints? 

                Again, thank you for your generous sharing of your time and experience. I only hope to someday be able to do the same for others. 

                THANKS! —MM

                • 5. Re: Want to automate searching by four fields via Value List using script...
                  MattMinde

                  Actually, I found a way to aggregate, sort of.

                  Even if I can't automate the search-sort-export process over the entire Value List, I can aggregate all the separate exported text sets in a new DB by using Import Records, and specifying the folder in which all the text files are. I then sort and export the single field as one giant block of copy for my TOC.  —MM

                  • 6. Re: Want to automate searching by four fields via Value List using script...
                    MattMinde

                    One other addition to the above: FMPro lets you import the filename into a field, so that when exporting, all I need to do is to name the exported text file what I want the Category header to be. I then import the 80-some files and, voila! I have a field which has the header for my Category, and a field which has all the businesses found under that header. I just wish I could automate the process. 

                    • 7. Re: Want to automate searching by four fields via Value List using script...
                      philmodjunk

                      It's really important to let people know when you are using an older version of FileMaker. Otherwise we could waste a great deal of time putting together a suggested solution that fails to help you because it requires a newer version of FileMaker.

                      I started to put together a suggested solution, but found that the functions I would user aren't all present in FileMaker 6.

                      In theory, you can use ValueListItems to get a return separated list of values in a global field so that you can extract one value at a time in a loop, but getValue, the function I'd use to extract this value was introduced in FileMaker 8 so that is not an option. You'd have to create your own version of this function by combining the Middle and position functions.

                         

                      • 8. Re: Want to automate searching by four fields via Value List using script...
                        MattMinde

                        Mr. Phil! Thanks for your help! I am in the process of upgrading to a newer version for our office (we've been holding our breath, financially speaking, for ten years). I did initially write that I'm using FMPro 6, specifically as a qualifier because I'm asking for help in forums where vast majority of people are using way newer versions. That having been said, I truly appreciate your help in reaching back in time to help us with our version, with all its limitations. But even the help based on the newer versions gives me clues on how to approach a problem in the older one. I promise next year, though, I'll be working with a newer version.

                        • 9. Re: Want to automate searching by four fields via Value List using script...
                          philmodjunk

                          Apologies on My end. When I take the time to scroll back to the top, I see that you did indeed indicate what version. I missed that detail and when I read your more recent post that referred to it, I am afraid that I "punched one of my own buttons" in responding to you as users posting questions while not telling folks that they are using a much older version of FileMaker is one of my "pet peeves".

                          • 10. Re: Want to automate searching by four fields via Value List using script...
                            MattMinde

                            No apologies needed! I was the one who asked for help, and you really helped! THANKS!