5 Replies Latest reply on May 2, 2014 9:49 AM by philmodjunk

    Multiple file search and report

    GlenEvans

      Title

      Multiple file search and report

      Post

           Hi All, 

           I am completely new to Filemaker, I would like to know if what i am trying to achieve is possible.

           I have a spreadsheet with a list of ID numbers, and an index from neofinder which has the file name and file paths of files  on a server. ( this is exported as a spreadsheet also)

           I am trying to pull a report which will use the ID number and search the File Name column of the neofinder index spreadsheet. Giving me a "yes or No" as to whether a files are held for that ID

           I would also like to pull a report of all the file paths for that particular ID into another report.

           Any help you could give me would be greatly appreciated. 

      Screen_Shot_2014-04-30_at_17.09.26.png

        • 1. Re: Multiple file search and report
          philmodjunk

               From what I see in the screen shot, I am inferring that the spread sheet located behind the other lists values that correspond to the text to the left of the underscore in the file names listed in column A.

               Import your spread sheets into two tables in FileMaker.

               Define a calculation field for the table of filenames and file paths as:

               FileID:    GetValue ( Substitute ( FileNameField ; "_" ; ¶ ) ; 1 )

               Define a relationship between the two tables as:

               Index::FileID = FileNamesPaths::FileID

               In Index, you can then define a calculation field as:

               If ( IsEmpty ( FileNamesPaths::FileID ) ; "No" ; "Yes" )

               Clear the "do not evaluate if all referenced fields are empty" check box.

               For your report, you don't indicate into what kind of report that you want to pull that data, but  a Summary report with a sub summary layout part "when sorted by FileID" can group your records by common FileID Value to show what files are found with a given FileID value.

          • 2. Re: Multiple file search and report
            GlenEvans

                 Thanks PhilModJunk,

                 That works great for that example :-)

                 I do have some other filenames that are not in that format though i.e 0126516234_my file.pdf

                 They can be in any format like below 
            LF NEW_8110262 G MOORE DISC 1  LF NEW.pdf 
            AlanJackson_5099902933429_CD.pdf

            Is there a way to search within the field? so searching for 
                  

            8110262
                 within the field 

            LF NEW_8110262 G MOORE DISC 1  LF NEW.pdf

                 Thanks Again for you help :-)

            • 3. Re: Multiple file search and report
              philmodjunk

                   It is possible, but the more "messy" your data (the more inconsistent the format from example to example) the more difficult it is to isolate just that value from the rest of the text.

                   From your examples, the only consistent pattern that I can see is that this is a string of strictly numeric digits of at least 7 digits in length. Does that match all the examples you can check?

                   If you have FileMaker Advanced, you may need to construct  a recursive custom function to parse the needed digit string.

                   Do you have advanced?

                   Without FileMaker Advanced, you may need to run a looping script to extract this data and put it into a field.

              • 4. Re: Multiple file search and report
                GlenEvans

                     Yes thats correct its either 7 to 14 digits within  index.

                     I only have pro not advanced :-(

                • 5. Re: Multiple file search and report
                  philmodjunk

                       I strongly recommend that all people developing anything beyond the most simple of FileMaker databases scrape up the needed $$ to acquire FileMaker Advanced. The tools it comes with--including the script debugger and data viewer can save you hours of time figuring out why/how something works or fails and also make it easier to learn how many parts of FileMaker work if you are new to FileMaker. The fact that you can add custom menus and custom functions to your database files--which then work when users open the file with Pro, makes it an even better value.

                       Here's script that can loop through a found set of records that also loops through the characters in the file names to parse out the first number with 7 or more digits embedded in that text:

                       Go to Record/Request/Page [First]
                       Loop
                          Set Variable [$K ; Value: 1 ]
                          #Get a list of values separated by returns where one of the values should be the ID number
                          Set Variable [$FileName ; value: Substitute ( YourTable::FileName ; ["_" ; ¶ ] ; [" " ; ¶ ] ) ]
                          Loop
                              Set Variable [ $ID ; value: "" ]
                              Exit Loop IF [ $K > ValueCount ( $FileName ) ]
                              Set Variable [ $ID ; value: GetValue ( $FileName ; $K ) ]
                              Exit Loop If [  $ID = GetasNumber ( $ID ) And Length ( $ID ) > 6 ]
                              Set Variable [$K ; value: $K + 1 ]
                          End Loop
                          Set Field [YourTable::IDField ; $ID ]
                          Go to Record/Request/Page [Next ; Exit After Last ]
                       End Loop