1 2 Previous Next 20 Replies Latest reply on Feb 9, 2016 2:16 PM by disabled_morkus

    Searching

    bowmankelley

      Hello,

       

      I have inherited a number of filemaker databases and I need to be able to search through the raw data for a specific string. It seems that there not an easy way to do that. I can't just open the database itself as view the tables because by default the database opens up Filemaker Pro and show me the UI, in the same way as it would when I look at it from the front-end. Any idea how I can do that? Is there a plug-in available or a script repository that I could use to search? I have 40 individuals databases and need to search without having open up each one. Thanks for the help

        • 1. Re: Searching
          siplus

          define "raw data" in the context of a Filemaker database.

           

          For example: if you are looking for something in scripts, you can select all scripts, print them to a single PDF and search inside the PDF with Preview.

           

          If you are looking for more than that, you'll probably have to use FMP Advanced, produce a HTML DDR and search for stuff in it.

           

          If you are looking for a particular word in the contents of a field, you can select all records and export all fields to an Excel file and do your search in Excel.

          • 2. Re: Searching
            peterba1

            If you are looking for the contents of a field, Control-F and type the string you are looking for!

            • 3. Re: Searching
              beverly

              Yes. control-F is the windows version of command-F on osx:

              Keyboard shortcuts (Windows)

              Keyboard shortcuts (OS X)

               

              There is also a menu for Find/Replace (where you can just find and not replace)

               

              Control-Shift-F (windows)

              Command-Shif-F (osx)

               

              If you are just using Find, these symbols/operators (like wildcards) will also help:

              Refining find requests in FileMaker Pro using find operators | FileMaker

               

              and this:

              How to conduct multiple find requests in FileMaker Pro | FileMaker

               

              beverly

              • 4. Re: Searching
                siplus

                ehm... no.

                 

                View-Find mode - which is cmd-F on Mac and Control-F on Win - will search in a specific field. And if you look for a substring you have to search for *substring*

                 

                Edit - Find/replace - Find/Replace.. - which is cmd-shift-F on Mac and ctrl-shift-F on Win - will search in all fields for you, without needing the * wildcard, but it will only search in the fields that are present on the layout you are on.

                 

                so my point still holds.


                Moreover, Finding in find mode will leave you with the searched fields indexed, even if you did not want that, which is bad, given that the OP's problem  looks like being a one time job.

                • 5. Re: Searching
                  beverly

                  OP says:

                  need to search without having open up each one

                   

                  Any answer will be they must be opened, whether for scripts & fields or for DDR or for export to Excel, right?

                   

                  40 individuals databases

                   

                  whew!

                  • 6. Re: Searching
                    peterba1

                    It looks like you will need an applescript. (OSX right?)

                    1. Put all 40 files in a single folder and make an applescript refers to the contents of the folder as a list that hen opens each one in a loop.

                    In Filemaker in the file, it goes to each layout and searches for the string.

                    The loop should stop or pause when the found count exceeds 0.

                     

                    I have written such scripts for myself often, but as an amateur scripter, would be unable to do that remotely.

                    • 7. Re: Searching
                      user19752

                      If OP is afraid of changing databases, use copy of all files.

                      If they have same table scheme, you can import all 40 files into one file before search.

                      • 8. Re: Searching
                        siplus

                        I had to do that on 86 files once, to get rid of all ä ö ü in Field names....

                        • 9. Re: Searching
                          peterba1

                          I am working on an applescript that will do the lot without having to modify any of them, however i am struggling with the part of the script that searches every field on the layouts.

                           

                          So far, it looks like this:

                          tell application "Finder"

                            --activate

                            set the target_string to "••••"

                            set the_result to the text returned of (display dialog "Enter your search text" default answer target_string)

                          end tell

                           

                          if the_result is not "" then

                           

                            tell application "Finder"

                            --activate

                            set this_folder to (choose folder with prompt "Pick the folder containing the files to process:")

                            set these_files to every file of folder this_folder whose name contains ".fmp"

                            repeat with i from 1 to the number of items in these_files

                            tell application "FileMaker Pro Advanced"

                            open item 1 of these_files

                           

                            repeat with j from 1 to the count of layouts

                            go to layout j

                            --next step is to search each field.  Shouldn't take long

                           

                            end repeat

                            end tell

                            end repeat

                            end tell

                          end if

                          • 10. Re: Searching
                            peterba1

                            tell application "Finder"

                              set the target_string to "••••"

                              set the_result to the text returned of (display dialog "Enter your search text" default answer target_string)

                            end tell

                             

                            if the_result is not "" then

                            set this_folder to (choose folder with prompt "Pick the folder containing the files to process:")

                              set these_files to every file of folder this_folder whose name contains ".fmp"

                              repeat with i from 1 to the number of items in these_files

                              tell application "FileMaker Pro Advanced"

                              open item 1 of these_files

                             

                              repeat with j from 1 to the count of layouts

                              go to layout j

                              tell application "FileMaker Pro Advanced"

                              tell database 1

                              set a to the name of every field in current layout

                             

                              repeat with i from 1 to the number of items in a

                              try

                              set b to the name of cell i

                              show every record of database 1

                              show (every record whose cell b = the_result)

                              exit repeat

                              on error msg number n

                              end try

                              end repeat

                              end tell

                              end tell

                              end repeat

                              end tell

                              end repeat

                              end tell

                            end if

                            • 11. Re: Searching

                              Why not create a simple JDBC connection to the FM database (assuming it has that enabled or you can enable it) and then query the data using regular SQL from a remote UI? You can also scan each table's data, edit it, do SQL UPDATE, DELETE, and INSERT, etc.

                               

                              FileMaker includes an excellent JDBC driver with the installation files.

                               

                              ODBC may be another option.

                               

                              - m

                              • 12. Re: Searching
                                beverly

                                remote UI

                                 

                                may be the problem here, if only FileMaker is being used. Can you query FM from FM with ODBC?

                                 

                                beverly

                                • 13. Re: Searching

                                  Looking at the original posting again, I may have misunderstood the OP's posting.

                                   

                                  For any approach I can think of, each database would need to be open somehow. Perhaps an AppleScript or KeyBoard Maestro Macro or something like that.

                                   

                                  ----------

                                   

                                  At a high level, the way I would search multiple (open) databases programmatically would be to use JDBC.

                                   

                                  1. Connect to each open FMP database one at a time.

                                   

                                  For each database ....

                                   

                                  2. Programmatically get the database metadata to read the list of the tables in each database. Standard JDBC includes DatabaseMetaData, which I'm confident FileMaker supports since there are all those sync products out there.

                                   

                                  3. Then, do SQL queries using LIKE "%<SearchString%" on each table as a starting point.

                                   

                                  It's definitely a few hours of work, but would be super fast in execution.

                                   

                                  If there's a FileMaker Plug in that won't break the bank ($$$) that will do this, I'd like to hear about that, too.

                                   

                                  ----

                                   

                                  Whatdoyathink?

                                   

                                  Thanks Bev,

                                   

                                  - m

                                   

                                  P.S. Some products out there, like Navicat, has a "Find in Database" ... a string in an entire (MySQL, SQL Server, Oracle, DB2, MariaDB, ...) database with a simple or RegEx string and return a list of matching tables/records where the string is found. You can then click each match to open up that record. Very simple and powerful.

                                   

                                  (I'm not suggesting that Navicat would be a FMP replacement, far from it, just additional information here.)

                                  • 14. Re: Searching
                                    peterba1

                                    in case you did not get it.

                                    The script opens every database in the folder from 1 to 1,000

                                    finds every layout name and goes to each one in order

                                    gets every field name and searches every one for your text string.

                                    This version stops the first time it finds your string, but could easily continue to find all occasions of every string in every database in the folder.

                                    It does open the databases, however, as I don't believe you will find any way of accessing the data without doing so.

                                    I searched a trial folder of 50 databases for one string in 3 seconds.

                                    1 2 Previous Next