Being fed to sharks.

[EmC]

Let me start by saying I have no expereince at all with FileMaker. I have this evaluation copy and need help in makeing it do what my cheap boss wants it to do.

I work at a law firm and my boss has recently made the decision to move all of our abstract files from paper to pdf. We have approximately 4000 files that have 5-30 pages in them. Many of them have multiple documents in them.

What I am trying to do is create a database that will allow me to attatch multiple documents to a record and be able to view a list of these documents so that the user can launch the document. I will need to add an unkown and random number of documents to each record so I can not just set the repition to a certain value(with my limited skills it looks like crap anyway.) The user needs to be able to accomplish all of this without switching back and forth between forms because these people are idots. .

Just an example of what I am trying to accomplish.

Main Table:

File Number: 00-1 <-Already assigned to the physical file folder and related to a billing account.

Date: 01/01/2000 <-Date the file was created.

Property Name: Text

Subdivision: Text

Lot Number: Text

Documents Table:

File Number: 00-1 <-File number that the document belongs to.

Index: 000001 <-Index value of the document being added, auto generated sequentially, related to file number.

Document: Container for the document.

When working with the Main table, I don't need a record in the documents table until I click on an add document button. At that point i need a new record in the documents table with index ++, and file number matching the file number from the main table.

Is it possible for the add document button to do double duty as an add additional document button? Suggestions?

How would I go about displaying the created list of documents and getting them to launch from that list?

Before i finish this long post, thank you for reading it and for any help. I am very computer literate and have taken a few programming classes so as long you don't fire things 40 feet over my head, I should be able to follow with little clarification. And be honest am I in too far over my head, or will I look back at this one day and say, "Hey that's the project that forced me to learn FileMaker, and now look what I can do with it."

EmC

[CobaltSky]

Hi EmC,

What you are wanting to do is certainly achievable - and probably achievable by you (as a relative newcomer to FileMaker) with a bit of patience and some furrowing of the brow...

Your first requirement is an all-purpose 'Add Document' button.

There are any number of ways to do this, but one of the most simple and straightforward is to create a script which switches to a layout based on the documents table, creates a new record, sets the key field value of the new record to the index number of the record to which it will relate, prompts the user to select the document to be added, then returns the user whence they came.

The script can then be set to run when the button is clicked (ie the button can be attached to the 'Perform Script' command)

So, to begin with, I suggest that you pay a visit to the ScriptMaker and familiarize yourself with the commands and the layout. Pretty soon you will see that commands such as Go to Layout[ ] and Set Field[ ] and New Record/Request and Insert File can be assembled into an automated sequence.

The only really tricky thing here is that you will want to set the button to pass the index number to the script as a script parameter, then have the Set Field[ ] command place the parameter inrto the appropriate field on the newly created record in your documents table (after it has executed the New Record/Request command). eg:

New Record/Request

Set Field [Documemts::Index#; Get(ScriptParameter)]

With a bit of luck, the vertical distance to the above description is less than 40 ft, and with a bit of stretching and jumping and maybe some standing on chairs, you'll be able to make progress.

Once you've wrangled with the first problem, your next problem will seem easy. To display a list of related documents in the main layout, you wil require a special layout object called a portal. Portals can be set up with a scroll bar so that they show a scrollable list of related records.

In layout mode, you'll find a tool for 'drawing' portals at the lower right of the block of tools on the Status Area (next to the button tool, in fact. When you use the portal tool, you'll be prompted to select the appropriate relationship and related fields to display in it.

Once you have your portal in place showing a list of documents for the current, you will be able to place a button into the portal row that opens the file for that row (row = related record in the documents table).

If the documents have been inserted as file path references (by having the 'store only a reference to the file' option selected when they were inserted) then opening the file will be a simple matter of using the Go to Field [ ] command with the 'Select/Perform' parameter enabled. If you will be storing the files within the field then you will have an additional hoop or two to jump through to launch the files.

The above should give you a few ideas to work with. If you've read through to this point without reaching for the smelling salts, then you're probably well on your way.

[Fenton]

The 1st thing you'll have to understand is how to create related records, as Ray says. There are various ways to do this, but they all result in the same thing: a new record in a "child" table with the "parent" ID in its own field as a "foreign key."

It can be done by just typing into a portal (works well if there are not more child records than portal rows), or with a button running a script.

The next big question is where/how to store these documents. In 7 there are several possibilities, each with pros and cons (otherwise there'd only be one method :-). If you simply Insert as a Reference,* with the PDF files local, then you'll see them on that machine, but no one else will. Not good. If however you do the above, and the files are on another machine, mounted on everyone's desktop, then everyone will see them; as long as you never move the image files.

[The machine with the FileMaker files has OS file sharing OFF]

There is also a possibility of simply calculating the path to files in order to show them, in the form:

image:/volume name/folder path/file name

This can be a calculation, with the result Container (not text). The advantage of this method is that the volume can be either a global field, or edited in the calculation, so you can easily move the files to another "file server" machine without breaking the references.

If the folder structure of the pdfs is organized, and the files are named in a series, so the entire path can be calculated, without having to Import Folder in order to get the path, then all you have to do is put the files where they belong, and they'll "automatically" show.

Another glitch you're going to run into is that PDFs are unique. They are multi-page images. Unless you Insert Quicktime, you are only going to see the 1st page in the database. If you do the above you will see multiple pages, but as a "movie" (little bar at the bottom to navigate). But I doubt that such a movie is going to print multi-page pdfs. You will want to open the file itself, then print that. Another factor, which may or may not matter, Insert Quicktime as a movie (which is what it does with PDF files) is always an referenced file, cannot be embedded.

I recently added this PDF documents capability to a solution for a small non-profit group. They're not very network saavy, nor highly organized, nor did they want to spend much money. So I decided to embed the PDFs as files. I wanted them to be able to get a glimpse of what the PDF looked like, as well be able to take it out and read the whole thing, as well as print it.

I used a Perform AppleScript step, to choose the file and duplicate it to a fixed shared location,* then called a FileMaker script to actually Insert the file.** I inserted it twice actually, once as a Picture, which gets the 1st page, and once as a File. It may be a little wasteful as far as disk space goes, but it gives you everything you need, in a fairly unbreakable package.

The interface was fairly simple. One button to "Add" a file, one to "Open". If it was a JPEG file that they'd chosen, it would use Insert Picture,*** but would not Insert File; not really necessary. If they clicked on a button to "Open" it, it would Export Field Contents of the JPEG, if that was what it was, as a file, and open it, with Preview (usually). But, if it was a PDF, it would export the File field, so they could see and print the multiple pages.

* fixed shared location: imagemac:/Macintosh HD/Users/Shared/Import.pdf <--dummy file, is created on the fly

** You must call the FileMaker Insert script from a script with a Perform AppleScript step. Otherwise AppleScript may not have finished moving the file. FileMaker and AppleScript work very well together, but each tends to be impatient, not always waiting until the other is done.

*** Another little glitch. You have to leave the extension on the file to Insert (".jpg", ".pdf"), or else FileMaker doesn't know what to do with it. It inserts it as "Container contents unknown." So you need a dummy file and Insert step for each extension, and a routine to know which to use. If you also want to do ".png", ".gif", ".tif", etc. then you need ones for them also. One of these days FileMaker may actually let us script the darn import/export file references; but not yet :-|

All and all you've got some experimentation to do before you decide. Do a search here in Container fields, or Importing. I may get up an example of this Insert PDF method, in the next few days. It's buried in a (large) solution right now.

[EmC]

Hey guys, thanks for the replies, they have been very informative. I think I have gotten it to the level I need. I am just running into two problems. I have been reading around and noticed that it is considered bad form to copy a field then paste, because it destroys the users clipboard, I have yet to figure out any other way to do this though.

This is the script I have on the button.

Copy [select; AbstractingFiles:FileNumber]

Go to Layout ["Documents" (Documents)]

New Record/Request

Paste [select; Documents::FileID]

InsertFile [Reference; Documents::Document]

Show Custom Dialog {...}

Commit Records/Requests []

Go to Layout ["AbstractingFiles" (AbstractingFiles)]

 

The second is just an interface thing that is irking me. I am listing the added documents in a alternating color portal list box. When I add a file the colored background is covered up by the file name, any way to change this?

Here is what I have so far, It works like I want, but it is all function no form for now.

File

[CobaltSky]

Hi EmC,

Answers to both your questions:

1. Instead of using the clipboard, set the value of AbstractingFiles:FileNumber into a global field (in either table - or elsewhere in the solution, it doesn't matter because global fields are accessible without a relationship). Then, once your script has created a new record in the documents layout, have it set the value from the global into the key field.

That will give you something like this:

Set Field [AbstractingFiles::gTempText; AbstractingFiles:FileNumber]

Go to Layout ["Documents" (Documents)]

New Record/Request

Set Field [Documents::FileID; AbstractingFiles::gTempText]

InsertFile [Reference; Documents::Document]

Commit Records/Requests []

Go to Layout ["AbstractingFiles" (AbstractingFiles)]

2. Select the field(s) in the portal (in layout mode) and then go to the pattern palette which is to the right of the fill color tool. From the pattern palette, select the transparency option which is at the top left.

Your fields will then allow the portal row shading to 'show through'.