4 Replies Latest reply on Apr 26, 2010 5:41 PM by FentonJones

    interfacing with files of correspondence



      interfacing with files of correspondence


      Hi, all.  I've been detailed in my department to work on the following problem.  Note that while I'm a programmer, I work on unix, in other languages and while I'm good with SQL databases, my knowledge of Filemaker Pro is pretty basic.


      For background, we are using Filemaker Pro 10, on an iMac OS 10.6.3.  The database is kept and used locally.


      This shouldn't be a complicated problem: I'm simply not sure what Filemaker Pro can or cannot do.  We have a database that's a list of customers, with potentially associated names, universities, departments, and license numbers.


      We also have a folder full of correspondence/email from these customers.  The folder contains folders, all named with either customer name or sometimes the university name.  WIthin each of these folders are individual files (each one an email/correspondence), and the name of each of these files contains some or all of the potential customer info (name, university, departments, license number).


      I don't know to what extent Filemaker Pro can be set up to go thru these folders and files, and set up links to each of these individual files based on the information from their path and file name.  I'm not even sure if Filemaker can store a link to an individual file (that could be clicked on when viewing that customer's record to show that individual file).  I assume I'd probably need to do a separate script to generate the list of file names etc for each customer.


      If Filemaker Pro can't link to a file on the local disk, then I'm guessing the other alternative would be to read the file into a text field so that it's actually part of the record itself?


      Any thoughts about this, pointers to examples doing similar things, or even confirmation about what Filemaker can or can't do in regards to linking to external files, would be appreciated.  I've gone thru the basic userinfo stuff that's provided with Filemaker Pro installations, but they are not detailed and don't get into this level of data management.




        • 1. Re: interfacing with files of correspondence

          You can store documents or links to documents in container fields.


          Another option is to store the file path in a text field and use the Open URL script step to open that file.


          Some file types can be displayed with the web viewer.


          Where you'll have trouble is setting up a script to walk through all the files and folders to build the links for you automatically. I suspect you'd need a filemaker plug in for this or possibly a utility script in apple script (macs only).

          • 2. Re: interfacing with files of correspondence


            Another option is to store the file path in a text field and use the Open URL script step to open that file.


            This seems like a promising approach: I assume I can list multiple file paths in such a text field?


            Can you point me toward (or give me a simple example) an example of a text container and how to apply the open url script step to each file path in the text field?

            Thanks so much... with an example like that I think I can figure out how to get this done. 


            The other question is that these emails are, of course, ongoing.  So for the user's POV, we'd like to set it up so that she just pastes in the latest file path of the latest email at the end of such a text field in updating the customer record.  Would the script step automatically recognize and deal with the newest added file path as well?


            THanks so much for any and all help,


            • 3. Re: interfacing with files of correspondence

              I wouldn't store multiple file paths in the same field. I'd build a related table of records and put each path in a separate record. You can use a portal to display all the document paths for a given parent record with a button on the portal row that uses Open URL and that row's path to open the original document.

              • 4. Re: interfacing with files of correspondence

                Here is a link to a FileMaker file, which has an AppleScript to Import all the file paths of a chosen folder (and its subfolders). It uses the Unix "Find" command, within the folder. In also uses the stat command to pull out the Size, Creation and Modification dates. It is I think the fastest way to do this. Not that it would matter much in this case, as you'd only be doing one client's folders at time. The Unix path returned is very similar to the URL you'd need, so a calculation can easily produce that.


                You can do the same routine with "vanilla" AppleScript, but it is much slower to get the size & dates. Its paths would be returned as old style Mac paths. It would however be a bit more flexible, if you wanted other info about the file.


                The one disadvantage of the "multiple records" vs. "multiple lines of a text field" is that it is a little more difficult to "synchronize" new data to old data. But just as you could overwrite a text field, you could Go To Related Record [ files records ] for a Client and Delete All Records (carefully scripted).


                In this example file I used Show All Records, and Import, matching records (on file path), with add New. If you were (in your file) to first Go To Related Record [ files records ] (as above), then do the Import step, and Import from only that Client's folder(s), you'd have the optimal speed.


                Import Files wAS