CeeEssGee

Yeah.. I'm not sure about this export function. I've fiddled around with it a bit, but the main reason I gave up was because it couldn't directly export to an excel file. So.. I can export to a .tab file, and then import that (from excel?) to an excel file, and then import that excel file to a .fm7 file? Oookay, that sounds like fun.

Hmm, well, I guess at least I know it's a bug now, so I don't have to wrack my brain trying to solve it. Is there any way to put serial numbers in to excel, then? IE, make a calculation for every cell in a column to be (starting number) + row number? That would make this so much easier.. I think I'm going to use a rusty spoon as a deadly weapon otherwise.

Argh, this is driving me nuts. I contracted a few people to put in data on to a spreadsheet in Excel, and now I'm trying to import this data in to the "LawCases" database, and for some reason, I'm able to import them fine straight from the excel file, but when I try to import them from another .fp7 file that I have imported the excel data in to (in order to mess around with the serial numbers easily), the damned thing keeps giving me this message! I'm able to do it from the "Clients" layout, and the BoxesMatters layout, for some reason, but not any of the others.. Please help, this could take days if I'm not able to mess around with serial numbers on a seperate file (this one is way too complicated).

Anyone? Nothing seems to work.

Bleh, this is really annoying. I can't figure out why these account names won't show on the grouped report..

Ah, that helps, thanks! Whenever I make the summary reports though, it doesn't register the account names at all. Its in preview mode, the values and descriptions are all there, but there is a spot for account names and nothing there (this is from expenses). Also, how would I be able to filter this preview to only preview for that specific monthly report?

I'm working on a program for our office to make balancing the Visa a bit easier. Its fairly simple, and doesn't need many protections from user error, although I guess in the future a reliable annual report would be nice. This is what I have so far, but the one thing I am having trouble with is providing subtotals (For GST, Post, and Total) for each "category", being the account names. The post amount is the amount we need to pay, the GST is the tax (that we don't have to pay; it's the 8% PST tax that we do have to pay). Any ideas how I can get this to work, easily? I don't want to have to flip between three different windows just to put in an account name (like my "Law Office Database"), but to be able to just put it in on a quick drop down list. BalanceVisa.zip

Okay, I fixed it up with what you did on the last file, and now I'm pretty much ready to go. I put in most of our database, and it seems to be working fine. One problem though, is that when I add an alias to a client, if I look under the alias, it doesn't have that client listed under it's aliases. For example, if Bill has an alias Tom, and I add that, if I go to Bill's record, it will show an alias of Tom. If I go to Tom's record, though, it will not show that his alias is Bill. How do I fix this? Can this be solved by a few more script steps, or do I need to make a new TO? LawCases9.zip

I haven't seen your latest attachment yet, but I'm about to open it. Here's the one that I made (updated to take in to account aliases). LawCases8.zip

Yeah, this is a lot cleaner than mine, and actually works (which is definately a bonus). I really wish I could figure this stuff out, though. I finally understood how you are using the global variables on Friday, heh. Now I'm going to concentrate really hard, and try to figure out how to make the aliases function . Is this database really complex? Moreso than most? Oh, also, how would I go about putting a display of the range of Closed "IDs" in each box? That would be *very* useful for the list view. I'd have to somehow find the minimum value of all related records, and then a - (or equivalent), and the maximum value, right? (Edit: Nevermind, I figured that one out)

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

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.)

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

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.

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.

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

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.

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

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

> 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.

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

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

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.

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.

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.