Law Office Database

[CeeEssGee]

Hi, I have a rather long and detailed story (including my plans for the database), but if you want to skip it, go straight to the questions at the end.

I was recently hired for some temporary work at my father

[Ender]

I dont' have all your answers, but here's a couple:

1. What is the best way to make it so that the user can only see closed files in closed layout, and vice versa for open layout? What I

[Fenton]

There is an option for a field to be an auto-entered serial number. It automatically increments upon record creation no matter what. There is no user interaction needed or wanted; if you show the number it is non-enterable except for Finds (easily done in FileMaker 7, fairly easily in 6).

This is the backbone of relational databases. Even if some number has to be sequential (ie., no gaps), or if you need some number to match your filing system, you're better off making another field to do that. The best policy is: don't mess with the serial ID.

The only difference between an "open" and "closed" record* is a checkbox "close." That would be a number field, with an attached "Boolean" value list (that was the guy's name). It will have a single value, 1. So it's either 1 or nothing.

*We say "records," not "files" when talking about FileMaker records. You're using "files" 'cause it references a paper "file." But a file on a computer is not paper; best to use "record" when talking to FileMaker people.

Another date field could auto-enter the current date when the "Closed" field becomes 1. You could edit the date if needed. But you'd be protected from lazy people who only check the box and forget to put the date.

The easy way to always deal with Open records is to create a calculation field:

OpenMark =

Case ( Closed = 1, "", 1)

Evaluate Always

Exclude the Closed records from canned Finds (using the OpenMark = 1). Exclude them from portals; using a relationship that includes Open (or not Closed) as a criteria in the relationship.

I don't see the real need for a "closed #." Yes, you could build one, and yes, it could increment (but I wouldn't use that for relationships). But what is it for? Is it needed for filing? Do you put the "closed" paper files somewhere different from the "open" ones (as opposed to all just being alphabetical)? If it's not needed for paper filing, it's not needed in the database, IMHO. You've got a Serial_ID, you've got a Closed Date.

(You could alternatively "archive" the Closed records to another table/file, probably at the time of closing. It would be a bit more work then, but would save you from always having to exclude them.

It's a 6 of one, half-dozen of the other kind of thing. The kicker is that you must build a routine to "undo" an archive operation, in the case of a mistake. Oops, wrong one; how do I get it back?

Clients would be in their own table, with their own auto-entered serial ID. A client could have several cases (records) in the main Cases (whatever you want to call it, Files :-?) table. In Cases there would be a Client_ID.

You don't really need to "insert" the Client information. All you really need is the Client_ID (the serial number from the Client table), which you must have. Don't just use the name. Get comfortable with serial IDs. Do NOT allow users access to modify them.

You can then "see" any info about that client. If you must do high-speed searches on this info, in the Cases table, then you could "look it up," probably just the name, using the same relationship (Table Occurrence) back to the Client table, on the Client_ID. Version 7 searches pretty fast on related data, so it's really unnecessary to look up secondary data.

[Fenton]

I guess I'd only archive the Purged records :-) From my point of view, Purged is another checkbox. A record would be Closed first, then Purged (most likely).

You could alternatively use a text value list, with "Open," "Closed," "Purged." But you'd want to keep anyone from entering anything else, or modifying that value list later, 'cause that would wreak havoc with hard-coded Finds, relationships, etc..

I prefer the simplicity of a checkbox for a critical "flag" field like this. So you'd have "Closed" and "Purged", both numbers, either 1 or nothing. Easy to deal with, and the value list can't be changed later. It does take up a little more space on List views. You could put a calculation field there with the text result (hence non-modifiable).

A Boolean checkbox is also an easy place to attach a script, if that becomes necessary. It's actually good in any case, so you can Commit Record after the toggle, or check other data, whatever.

(Wish list: FileMaker adds a "Commit after running" checkbox option when you attach a script. Of course, they'd probably want the other options, no dialog (seems obvious), run with Full Access. I'd be happy with plain Commit, no dialog. It'd eliminate the need for many separate scripts that become 2 steps with Commit, such as Set Field )

[CeeEssGee]

I'm not quite grasping everything at the moment (way too tired), but it does seem like good advice. I still haven't really figured out the tables, although I did just watch the video, earlier.

Closed numbers are required. It's hard to explain, but they just have to be there, and they have to be *assigned* at closing. There is no way I'm allowing my "users" to alter any of the following, though: Closed #, Open #, Open Date, Closed Date.

Furthermore, files which are "purged" need to have their information available. Otherwise, a Bar audit would be very.. interesting. "Lost" status is also required, because it saves a lot of time looking for it (or assuming that it is purged, and making a client upset when we weren't supposed to purge a certain file). To purge a file (not a record) is to remove the majority, or all of the contents from the file. To purge a "record", is to just flag a record so that we know which files have been purged, and have the info ready (to submit to the bar).

There's reasons for why I need it to work this way, but how, well, that's another matter! I'm going to fiddle with the tables, though, and then see what happens.

Edit: files aren't always closed before they're purged. closing a file means sending it to storage (ie, paying more money), so, as long as we can legally purge a file, we often do, and not bother with closing it.

[Fenton]

Well, I probably spoke too soon, and perhaps with too many of my prejudices (Boolean checkboxes for everything :-). But I think you have the main idea about the interface, which is that it has to be logical, and that you have to control what data can be entered for critical fields.

The main point about the serial IDs versus these other numbers is that you can rely on a FileMaker auto-entered serial ID, for relationships, etc.. Other numbers may be useful information, may have certain requirements, need to be consecutive, etc., but they are less reliable for internal database operations, and, since you can have both with no problem, why not.

You need at least 2 tables, Clients and Cases, each should have its own serial ID, with Cases having its own ID, and the Client ID as a "foreign" key (not an auto-entered serial in Cases, but yes an auto-entered in Clients). That would be a consensus opinion here I believe.

It's somewhat confusing to understand exactly what you want to do with the database unless you distinguish between what you're doing with the "file" (paper) and what you want the record (in the database) to show about it. But I think I got it now.

[CeeEssGee]

I'm still not quite getting how to use the tables, so that the user can add in all of the information about a Client to the Record. Oh well, I'll get it eventually. Does this mean that there will have to be a seperate layout for entering in Clients, and entering in Records (files)? If that's the case, I think I'm going to keep the layouts the same for now, because putting in ~6000 files at the moment doesn't seem too appealing if I need to switch between layouts for nearly every file (unless the client repeats, which still doesn't usually exceed 20 records).

I've just realized a problem, and I know why it's important for there to be a filemaker made ID for every Record: we don't have a closed and open # for every file. This is why I have no idea exactly how many records/files we have, heh. So, I think it would be a good idea to assign a seperate serial ID to records, but have it as a hidden value, only used by scripts and such, rather than the users. Is it possible to put these values in to files already made?

[Fenton]

Yes, you can "serialize" existing records. First create the Serial_ID field. Click Options, click the Auto-enter option for serial number, increment by 1. Don't worry about the starting number, 1 is fine for now.

Put it on a layout (for its table).

Enter Browse Mode

Show All Records

Put your cursor in it (any record)

In the Records menu, choose Replace Field Contents...

Replace with serial numbers, start with 1, increment by 1

[x] Update serial number in entry options

Hit "Replace"

(One caveat: always remember this number if you do imports. It can update itself normally, for new records. But if you ever have to import into a "clone," it will need to be reset. Not to worry now, but something to know.)

The reason for the Clients table is so you DON'T have to enter data repetitively for a client with several "cases" (files, whatever).

There is a method to extract all unique client entries from your existing records. They could then be imported into their own Clients table, with their own auto-entered serial ID (an option during import, [x] allow auto-enter).

It's not completely automated. Because someone will have to review them first. You will see just how many typos in the names that you have. Those will have to be fixed, one uniform name per client -- in the originals. Then toss the others (in the summarized file).

Replace helps here also. Find the client (Finds are more sloppy than relationships). Find a correct name. Replace it into found set.

This is standard procedure for converting a "flat-file" solution to a relational database. I've had to do it for practically every client (or tell them how to do it; sometimes they're the only ones who know which is the right name; it's their data, and money).

Why go to all this bother? Well, you want a mechanism to fill in the client information. Any such mechanism will rely on the name being always correct. Or it will fail, silently. A Find could also fail if the typo is fatal. Wouldn't it be better to choose from a filtered portal, showing all possible matches to the name? Not hard to do, if you've got the tables :-)

Another similar problem is that people sometimes state their name differently, Richard, Rick, Dick, etc.. Without a real Client table, you're at the mercy of whoever types in what they say at the moment.

[CeeEssGee]

Well, after waking up, and reading through the tutorials (so far I've been using help, pretty much, but that isn't very helpful for relationships/tables), I've got it pretty much set! How can I deal with multiple clients per record/file? I'm using a pop-up menu in the Open/Closed File Layouts with all of the existing clients, including an other option to add another one.

[Fenton]

What do you mean, "multiple clients per record/file"? Do you mean you can have more than one client on a "case (file)" record?

(We have got to either come up with a different word for your "file" (papers), or just assume that that's what you always mean; it's all 1 FileMaker file, with several tables. I'm going to use the word "case.")

If so, then you need a "join" table, which is a small table with just the Case_ID and the Client_ID, and possibly a date, if you want to track when this particular client become part of a case. So a Case with 2 Clients would have 2 records, same Case_ID, different Client_IDs. They would show in a Portal in Cases. The relationship from Cases would automatically pick the 1st entry by default; but could address any number of a case's clients individually.

As far as picking a client, I much prefer a filtered portal. Value lists are both no fun for thousands of entries. Not to mention they are "ID antagonistic" (look crappy), and there's a sorting bug in version 7 (for sorting on the 2nd field).

Here's an example (from an earlier example file (which I could no longer find on Forums; munched in the changeover). It's for book titles, but it's the same principle.

[CeeEssGee]

Sorry, I know, it must be confusing, since I'm confusing myself.

What I mean by multiple Clients per case, is that in many cases, it is not just one client who we are acting for, but multiple. For example, mergers.

I'm going to take a look at this example, but to be honest, I don't really know what you mean. I ended up just making the client name a repeating field in "Files/records/open/closed", and made 3 different pulldown menus, each which stored 1 of the values.

I just need to have a list of previous clients, so that I can show all of their previous information in the main cases, and the option to add more, easily.

What I reallly don't understand is where the ID numbers come in.

[Fenton]

Post deleted by Fenton

[CeeEssGee]

Not clients who are "closed", but the actual case; the file, as I was calling it. The file is finished, all of the deals are done, that specific "reference" matter is completed. Thus, the physical file is "closed", and sent to storage.

Regardless, I digress, this FileMaker "file" is very helpful, thank you.

[Fenton]

I had one more little idea. You could just have the date fields: DateOpened, DateClosed, DatePurged. If you want a text field, for visibility and searches (3 date fields being somewhat awkward for users), then it should be a calculation field on the above.

Status_c =

Case ( not IsEmpty ( DatePurged), "Purged", not IsEmpty ( DateClosed), "Closed", "Open")

[x] Always evaluate

It is not enterable except in Find mode. Format, Field Behavior, [ ] No Browse mode.

DateOpened being auto-entered date creation (Options). Case is hierarchal, it works from top to bottom; Purged being the farthest "down" the event chronology, it is at the top.

The reason I go into this again is that it I often see files where there is a drop-down "Status" field, and date fields for critical events, but they do not agree, on many records either one or the other is wrong or missing. Best to force entry of the dates, IMHO. There are easy command keys to insert today's date, or give them a button [T].

[Fenton]

Here's a new example file. I added and changed a few things. While the drop-down arrow IN the portal was all very clever and all, I don't really like working inside portals that have "allow creation of related records" on. For one thing, you have to either have extra graphic objects, or create umpteen calculation fields to control them.

This new example doesn't have that problem, the graphics are just plain objects. The button to add a client is outside the portal. It will be much simpler in the long run. The scripts are quite different however.

I added another routine so that someone can use a new client record (which they created on-the-fly while trying to choose a client) to set into the current Case. Otherwise, if they don't see the client they want, then create a new one, they'd have to go back to the Case and start the choice routine again.

That, however, creates a somewhat dangerous situation; because the "current Case" could be other than they think, if they're allowed to wander around. So this layout is controlled by a Loop, just like the Choice layout. Neither is available from the Layout menu (only safer user layouts should be seen in Browse Mode; all can be seen by the developer in Layout Mode).

I also added a little routine to ANY script visible in the Scripts menu. It checks for Allow User Abort [Off], in which case it will not run its script. Otherwise the Loops to trap people on layouts have a serious backdoor problem.

The "go to layout" scripts have the step Adjust Window [Resize to Fit]. This works well on Macs, but gives you an extra menu bar on PCs. You may want to pull that off into its own little script, to call from other scripts, and toggle the behavior per platform. There's no real right or wrong for this. Try it on a PC.

LawCases2.zip

[CeeEssGee]

That was so helpful, I barely had to change anything (although, I will change many minor things, after I put in all of the data). There's just a few more things I want to do, that I am having a hard time with:

1. Is there any way to get to a "New Client Choice", to add to a case, without going to the ChoiceClient window? I have tried this, but every time it gets.. weird.

2. How can I make a related field, in clients, where it shows all associated clients? For example, one person owns many companies, and each one has a seperate client entry.

3. How do I make another table, in relation to cases, that has a list of all of the Box IDs (the physical box number where each physical "Case" is stored - and cannot be auto-inputed, since this is provided by our storage company), and a summary of the cases kept in them? This is also very useful, because instead of using that "close file" button from the cases, I could make my users (actually, more likely me) go to this "box" layout, where they simply add cases to a box, and then it closes the file. I can also add a button from there that adds these files to a new window, and creates a template e-mail to our storage company to pull those specific files to storage. I can do all of the last few options, but I can't seem to get this table working properly.

The file is attached, although there are no records (I removed them, for obvious reasons ).

SCBFilesPublic.zip

[-Queue-]

I'll answer a question you haven't asked. You don't need individual scripts for each letter button. You only need one script to Set Field [Clients::Last_g; Get(ScriptParameter)]. Each button will pass its letter as the script parameter in the dialog where you Specify the script to perform.

[CeeEssGee]

Hah, I figured there was a faster way to do that. Thanks!

[Fenton]

> 1. Is there any way to get to a "New Client Choice", to add to a case, without going to the ChoiceClient window? I have tried this, but every time it gets.. weird.

There is a very good reason that you can't easily get there. It's well-protected with a locking "Loop/pause" script. Because it will set the chosen Client into the current Case. Hence it is only safe to go to it FROM the current Case. Nothing else is safe, so I'm not allowing it.

You could copy the entire layout, remove the "+" button, duplicate and modify the > Choice script that goes there, then use it to Find Clients. But that's a whole different thing.

> 2. How can I make a related field, in clients, where it shows all associated clients? For example, one person owns many companies, and each one has a seperate client entry.

You need another table for Companies, and a join table, ClientCompanies. In other words, we don't know w/out more info.

> 3. How do I make another table, in relation to cases, that has a list of all of the Box IDs (the physical box number where each physical "Case" is stored

Basically, a Case can only be in 1 Box, right? And once it's in the box it's closed? I added something to at least add a Case to a Box (which has to be created first).

I don't see why you have both Serial and Open ID's in Cases. You should almost never have 2 auto-entered serial IDs in a table. It is redundant and confusing. Auto-entered serial IDs are the glue that holds relational databases together.

Oh yeah, I also added the parameter scripts to the "alpha" buttons; with a twist. If you hold the Option/Alt key, the letter you click will replace the field. Otherwise it will add onto what's in the field. So you can use them like a little typewriter.

And I changed the layouts a little. You can change 'em back if you want. I'm fussy that way :-/

[-Queue-]

Nice addition, Fenton. You could also remove the redundancy in the letter script and use Case( Get(ActiveModifierKeys) = 8; Clients::Last_g ) & Get(ScriptParameter).

[CeeEssGee]

> You need another table for Companies, and a join table, ClientCompanies. In other words, we don't know w/out more info.

This doesn't have to be just companies though. It could be spouses, children, aliases, etc etc, and these would *also* be a part of the clients table. I see what you're saying though, I could just create a copy of the clients table, call it aliases (or whatever), and create a bridge between the two (such as ClientsAliases)? I'm going to try that.

> I don't see why you have both Serial and Open ID's in Cases. You should almost never have 2 auto-entered serial IDs in a table. It is redundant and confusing. Auto-entered serial IDs are the glue that holds relational databases together.

I know, it sounds stupid, but it's the way it has to work. Not all of our files have Open IDs, and not all of them have Closed IDs. In fact, many of them don't even have a Reference Matter, or Client Name, but we still have to keep them (or at least keep records of them being purged/lost). The auto entered Open IDs and Close IDs are actually just a luxury, and I will probably have to include some option to overide them. The other IDs are the auto entered IDs so that EVERY record has one, so that they all relate. In fact, for that reason, I don't care if anyone sees the "hidden" auto entered IDs.

I really don't understand what was done with the boxes on this, could you please explain this?

Edit: Ah, I see now. Is there any way to make it show *all* of the cases in single boxes, though, rather than seeing "this case is in this box, this case is in this box" etc etc.

[CeeEssGee]

Here's what I've done to the tables, to take in to account all of the data that I will want related. What do you think? I know there's two transition tables between boxes and cases, but that is the only way I can think of doing them. Each Case has a Unique ClosedID, but that can't be on the same table as the cases, because it is only activated when closed. Each ClosedID has a unique box, but a box may have numerous ClosedIDs (each with a specific case, and various unique clients to that case).

From the Cases layout, I will want to see the specific ClosedID, and BoxNum (if any, for either). From the boxes layout, I will want to see every single ClosedID, Case RE, Case OpenID, and Client related to that box.

I know I haven't incorporated your keyboard idea yet, but it is good!

LawCases4.zip

[Fenton]

Post deleted by Fenton

[CeeEssGee]

Okay, this is amazing. I still can't figure out exactly why or how it works, but hey; it works!

[Fenton]

It's not really that amazing. About the only part of it that is sophisticated at all (but is basic to 7) is reaching "through" the CasesClients2_CaseID TO and the CasesClients2_CaseID TO to get to the Clients2ClientID TO, all just get the Client's name to show in the self_Box portal.

I believe that's similar to what you were doing with your graph, which I glanced at but didn't fully analyze (I could tell it had extras). Plus, you were using both the SerialID AND the ClosedID from Cases in relationships. As I said before, I think it's a recipe for disaster to use (and confuse) 2 auto-serial IDs from the same file; then use them in relationships.

The Client name in that portal may or may not even be relevant. I only did it because: 1. I wanted to show it was possible, and 2. There just wasn't any real recognizable info in the Case table itself, no Case name or anything. That's why there's no "show also field" in the CaseID drop-down; there's nothing really worth showing.

If you must add Cases from the box end, it would be better done, and I posted earlier, with a "choose Case" portal.

Personally I don't think it's such a great idea to add cases to boxes from the box end. You could possibly "go to all cases in this box" (not implimented). But why would you?

It seems far more likely that you would:

1. Close a case (mark it and/or date it closed; if both, make sure both happen, ie., [x] button script)

2. Find all closed cases

3. Add them to a box

[CeeEssGee]

Well, I don't need to necessarily add files to a box from the box view. What I do need to do, is when I close a case, always assign that case to a box. We usually like to look at a list of all of the cases in the box that we're assigning them to, at that time, though. I don't need to really see all of the "cases", just a highlight of certain information, in a list format (ie the portal) inside the box.

What I think I'm going to do (and I'm going to pretty much follow your ClientChoice window), is when I close a case, the add case to a box window will come up, but instead of a list, just the last box used. There will also be an option to create a new box to add cases to.

I could probably just make it easier by giving a custom dialogue prompting whether the user is going to be opening a new box to put records in, or would like to just use the last one.

[CeeEssGee]

I've tinkered around with this a bit, but I can't seem to get it to properly add cases to a box, or to use the custom dialogue to have a user input a box ID (this will definately not be an auto-entered, nor can I have filemaker chose how many cases to put in a box, which is why I was going to call it BoxNum).

Why is it that the box numbers are repeating? Is there any way to make it so that each ID is unique, and cannot repeat?

Finally, is there any way to make a "drag and drop" type function? IE, the ability to have multiple "boxes" open, with their files visible, and to drop closed cases inside.

LawCases5.zip

[Fenton]

I messed around with this a bit more. I duplicated the Client Choice layout and routine and modified, to "Add Case to Choose a Box," with the default the last box used.

The Box numbers are going to "repeat." Because this is NOT a Box table, it's a Box_Cases table. It's more or less a "join" file; but there is no Box table on the left side. It's simply implied, its functionality is handled by a the self_BoxID self-relationship.

If you want to record information about each Box, then you need a Box table. We're kind of faking it right now.

I don't guarantee that this is the absolute best way to handle the IDs and Closed Date. I'm still not clear on what exactly the ClosedID is. It is in BOTH Cases and Box_Cases; but it is not set it in Cases. It's the auto-enter serial ID in Box_Cases. That's a train wreck waiting to happen, until you figure out exactly what it is, where it goes, and how it is set.

The question is: When a Case is Closed, is it always immediately Boxed? If not, then ClosedID has no business in Boxes. Let Boxes have its own serial ID, a Box ID (which you just enter, from whatever these other people tell you, right?), and the CaseID. That's all.

I also changed your interface a bit. I don't like dark buttons. You can change it back, but that's the price for free advice :-)

[Attachment has been moved to later post.]

[CeeEssGee]

The ClosedID isn't really an ID as we have been using, it's more a number. A value which is usually increasing everytime we close a file, but is subject to human error (and thus, we cannot use it as a consistent ID). The same goes for the OpenID, and BoxID. I'll call all of those -num, rather than -ID to make things easier.

When a case is closed, it is always immediately boxed. We usually fit about 5-20 cases per box. But, the cases can switch boxes frequently, and we don't know all of the locations of the cases, heh. Trust me, if you think this is confusing, you should see our file card system! (Or rather, you probably shouldn't )

Oh, and the ClosedID in cases is a mistake. It's leftover from when I was using it on the same table.

[CeeEssGee]

Well, I'm still having a problem with the message "case is already assigned to another box". What's causing this? I see the code, but.. it doesn't have a box value yet. This happens whenever I try to create a new box and add it to there.

[Fenton]

Post deleted by Fenton

[CeeEssGee]

Well, I could always put in a boxes table. Going to try that in the morning.

[Fenton]

Yes, it really needs a Boxes table. Then a box could get a real auto-entered serial BoxID. If they need their own custom Box number, they could have it; but it should be separate. You could look it up into the Box_Cases table.*

The [ New ] button in the Box Choice would then need to bop over to the Boxes table in order to create a new box; get a new ID into a global, then bring it back for the new Box_Cases record. In other words, at that time you are creating 2 records, 1 in each table. Box_Cases is a normal join file, with both sides.

* I don't really see the point in letting the boxes, outside the database, determine what the number inside the database is. This is a hold-over from pre-computer days and should be phased out. If they want to put more data on the box labels, such as date created, etc., fine, let the database do it.

[CeeEssGee]

I know, but, our storage company is very anal about this and won't let us chose the numbers on the boxes. This is fine though, because so far they have been very good as far as service goes.

So, it would be very silly to have data different on the boxes, than the database, because we use the database to send in for the physical boxes, so it needs to be consistent. So, yeah, I do need these arbitrary Serial IDs in the database, but there's no reason anyone should actually know what they are (except for me, when I want to alter the matching IDs on the join tables.)

[CeeEssGee]

Here's what I've done with it to this point. It's not pretty, and it doesn't really work, but it does incorporate nearly everything I'm aiming for.

(I'm still working on it, especially with having the scripts that join the alias to clients, and boxes to closed to cases)

LawCases6.zip