AnsweredAssumed Answered

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

Question asked by MattMinde on Mar 30, 2015
Latest reply on Apr 3, 2015 by MattMinde


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


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.