File question, please help

[Donnie]

Hello everyone.

I am attaching my file that I have created for my Martial Arts school. I didn't want to have to pay 300 bucks for one. What I have so far is fairly functional. It records all of my student info, and I can invoice a student.

I guess my questions are as follows:

1. How does my overall format look? I know it's rough, but as long as it's functional, that's all I need.

2. If you go to the Report menu at the top of the main layout, then click on Payment listing, it gives you a report listing of all of the payments a student has submitted, based on their invoices. While one student will have at the very least, one payment per month, I would like this report to show what it is right now, but then as a summary, I would like to show the total of the payments listed for each student. I have a field called Total Payments Taken, but can't get it to work as I would like.

I'm sure that I will have other questions, but that's about where I'm at at this point. I can take any comments you all have, good or bad. I just am getting tired of working on this at the moment. The attached file has one student in it, and a couple invoices, plus the payments. My current file houses all of my students, plus the pertinent info on those students.

Thanks so much for everyones help. I have written to this forum before, and really appreciate the help that everyone gives in making FMP creation much easier.

Donnie

Mayes Martial Arts Academy zipped.zip

[Fenton]

1. The overall look is fine. Though the Invoice layout is not really printable, if you want that. The logo on the page could stand to be smaller (though it's a great logo), as could the title. The portal doesn't need to be such a dark gray. Or you could put a dark gray background behind the portal, set to non-printing.

2. Your "total" field was a calculated total of the Payment field in that one record, ie., pretty useless. What you'd probably want is a "Summary" type field, which adds up the records in the found set (more or less).

If it is put into a Subsummary Part, and the records are sorted by the "break" field of the Subsummary part (StudentID in this case), then you get a subtotal by student. That's what I did, in the Payments Report layout, using the Payments Report script.

There are 2 Subsummary parts, one "leading," one "trailing." The "Grand Total" is the same Summary field, but in a Trailing Grand Summary Part (you get 1 of those).

There is also a Title Header part, which appears on the 1st page (where you put the title, date, whatever). And a Header part, which appears on every other page (can be smaller). The "labels" in Layout mode are in each part. Buttons go in the Header, 'cause the Title Header and Subsummaries only appear in Preview mode (where buttons are useless anyway).

This is the basics of a FileMaker report. You might want to downsize the font size and choose a serif font; everything is bigger when it prints; this is not a "browsing" layout. Also the Print Setup and Print were set on my Mac; they'll need to be reset on Windows (though both were pretty much at the defaults). I also set the Layout Setup, Print to Fixed Margins. If you set all these details your prints will be consistent on almost any printer.

Mayes Martial Arts Academy2.zip

[Donnie]

Fenton,

Thanks a ton for the reply, although it is a humbling thing to see that I have worked for what seems like FOREVER on this, and you replied back with a functional and great answer in less than 5 hours from my original post. Shows that I have a bit more to learn huh? <grin>

So I don't have to have a Payments Line Items table associated with the Payments as I do with the Invoice table?

What about my relationships? Did you check those out? Are they somewhat valid or am I breaking the law of relationships?

Do you mind if I use your solution for the student report? It looks tons better!

How hard was that to come up with? I know not hard for you(less than 5 hours), but for a semi-average user of FMP?

Thank you again.

Donnie

[Fenton]

The Relationships looked fine. The names are a little plain, ie., just the name of the table. But in this case I think that's OK, because the relationships are all one-to-many; it's pretty obvious what they are if you know the basic structure, which is a common one. If you add any other relationships to these tables however, you're going to have to name the relationships to specify either what fields are involved or what they're purpose is. You seem to have grasped the basics of relational design right away.

I don't think you need a Payments Line Items table. A payment is a payment here. It is not tied to any particular Invoice or Invoice Line Items. If the payments are less than the invoice total, they owe you money. You can look to see what the difference is; but that's all the detail you really need.

Of course you can use my report. But you should study it a bit to see what the parts are. The Subsummary parts have a "break" field which is part of their definition (StudentID). This, when sorted by that field, allows them to break the records into groups matching that ID. You can in fact delete the Body entirely (duplicate the layout first), showing only the total for each student.

Also look at the Summary field's definition.

It is your basic FileMaker report. You may want a similar one for Invoices. It doesn't take long to build, after doing several. That same part structure will do reports in many different tables/files.

There is one odd problem in the file, which is that you've built "new record" into your navigation scripts. Normally you'd have a separate "Go To" script and a "New" script, often putting the New script on the final layout. I noticed, after bouncing around a bit, that I was creating new blank records right and left. Not so good.

On a related note, there is a better mechanism for moving data than "copy/paste," using a global field to pass it. It's pretty easy in 7. Perhaps tomorrow I'll have some time. But I have to fix something I just found in one of my files; it was working before -!

[sweetpea]

I cannot understand how to set up a template for organizing books and pamplets with the titles of all the articles within. When I have completed my template and the entry of information i would like to be able to search the files and know which articles or chapters will be found where. My FMP manual does not seem to have any step by step instruction or any examples on setting any of this up. I'm sorry about the simple question. Is there anyone willing to help me?

Thank you

Percymae

[Fenton]

This is kind of a new thread. but it's along the same lines I guess. Rather than try and explain much I hacked one of FileMaker's Template files, to add the related level for Articles. It's not fancy, and it has all the "file" stuff of the original.

Basically you have 2 tables, with a 1 to many relationship from Documents (books, etc.) to Articles.

DocumentsArticles.zip

[Donnie]

Fenton;

Did you get your file fixed? Did you have a chance to finish your other thought about my mess? If not, no biggie, I totally understand about having problems.....<grin>

Thanx.

Donnie

[Fenton]

Yes, I got my file fixed :-) Except it wasn't really broken. I'd just neglected to add the developer full-access account (me) to the calculation in a script doing a fancy record-level restriction filter; it wouldn't Show All Records for me, so I thought the whole thing was broken somehow.

I did some fixes, modifications on your file, basically separating the "navigation" from the "record creation." It's not perfect, but it's closer. Most of the navigation uses the simple Go To Related Record ["relationship"], with the option in 7 to specify what layout to end up on. Simple but effective; going from a Student to their Invoices, from an Invoice back to its Student.

Your Payments are related to Students really. They can be assigned to a particular invoice, but that is really almost optional. Because no calculations or relationships rely on that fact. Which is probably for the best. If payments must be tied to and calculated against specific invoices it's more work, and not the way most small businesses work. Right now you can see all payments a Student has made. I put a portal on the Invoices layout, to see that last few payments.

You should use a graphic app to resize your logo appropriately. It would draw faster.

Mayes Martial Arts Academy2.zip

[Donnie]

Fenton;

I opened it up when I got home yesterday, navigated around a bit, really liked what you did with it. Thank you. I haven't had the chance to really dive into it yet as we got called away yesterday in a hurry, but I plan on doing that tonight. Thanks again. I'm sure I'll have more questions once I get into it.

Donnie

[Donnie]

Ok, I've looked around, imported all my data into it, entered back invoices and payments so I have a lot of data in there now. After this weekend's demo we did, I had one parent that paid me $50 for her two kids for the month. Then also wanted me to order a uniform top for one and a complete uniform for the other. I charge $45 for two family members, so the extra $5 went towards what was still outstanding. So on the invoice screen, I have put in there for the extra items they want.

So, after all that, here's my question. How hard is it to add a field in there called "due" or something like that, that would show what is still due on that particular student? I really liked the "Goto related record" in the scripts that you had changed. That made a ton of sense and was very useful in the changes you made. Thanks for pointing that out to me.

Anyway, so far, it's working well, and I love it, but like I said, I'm sure I'll have questions here and there.

thanks.

Donnie

[Fenton]

It's fairly simple (in version 7) to create such a calculation in the Students table.

_cBalance = Sum ( Payments::Payment Amount ) - Sum ( Invoice Line Items::Item Total )

The Payments relationship is actually Payments_StudentID. Using the InvoiceLineItems Table Occurrence is a new capability in 7. We specify that Table Occurrence, but it's not directly connected to the Students table. Following the path, its first "leg" is Invoice_StudentID. It then continues to the InvoiceLineItems table. In 7 we can just specify the last TO in the line, and get the relational result of the entire path from our beginning location.

Because the first relationship connects to all invoices for that student, the next leg is going to connect to all invoice line items for that student.

You could also get this result in Invoices, but you'd need a self-relationship on the StudentID. It would be more involved, not really worth doing unless you really want it there.

MayesMartialArtsAcademy3.zip

[Donnie]

Fenton;

I really love the addition to the unpaid balance. I have added a menu item for unpaid balances to show who has an outstanding balance, and also from the invoice layout, I have added a button to Print invoices that will go to a different layout, then supposedly print open invoices. But.........when I print either invoices, or reports to a .PDF file, I get the jarbled mess that is included with the attachment. Am I missing something as far as printing to PDF thru FM7? I don't recall having these issues with FM6. I don't have a printer on my machine, so I like to print everything to PDF so that I have a copy on the system, and can email it to students.

In the attachment is live data so that you can see what I am working with.

Thank you again so much for you help and assistance, I have learned a great deal from it.

Donnie

MayesMartialArtsAcademy.zip

[Fenton]

I'm not sure exactly why you got garbage, but there were a couple small things. For one thing, I don't know whether you're on a PC or Mac. I'm on Mac 10.3.4, which prints it fine just the way it is.

There's some confusion about Enter Find Mode [Pause, Restore]. This is not really what you want, because it leaves you paused in Find mode, then prints. That could be why the garbage. In this case I think you'd want just Perform Find [Restore]. This just does the Find, leaving you in Browse mode. Then you print from there.

Go To Layout ["Invoices"]

Perform Find [Restore]

Print

You don't really need the Invoices Report layout. The Invoices layout prints just the same; all the extra stuff is either off the page to the right, or explicitly set to "Do not print" (Format, Align/Printing).

On Mac X the "print to pdf" option can be saved directly in the Print [Restore] step, as pdf printing is built in to the operating system (a mixed blessing). The default setting prints all the invoices. If you want to just print the current one, you'll have to save that setting, in the Print dialog (drop down to FileMaker, instead of Copies & Pages).

You may also want to double-click the Body part, and set it to "page break after 1 occurrence." Right now it does that anyway, because of the combined size of the Header and Body, but it's better to set it explicitly.

[Donnie]

Fenton;

Ok, one last question, then I'll promise to try and not bug you. I have it tweaked how I want it, reports coming out when needed, I just want one more thing on it to make me happy and I'm struggling with it.

On the Invoice screen, I want the Age_of_Invoice to show time it has been open, which it does right now with the calculation:

Get ( CurrentDate ) - Invoice Date

This works great, but after I close the invoice, as days go by, this number still calculates and changes as it needs to. I don't want this, I want it to stop the calculating once I change that drop down menu to Closed. I put in an If stmt that read:

If ( Invoice Status = "Open" ; Get ( CurrentDate ) - Invoice Date )

and this worked fine, but once I Closed that invoice, the Age_of_Invoice field was blank since I didn't have a 2nd result on the If stmt. I thought I had done something like this for work and our KnowledgeBase for open/closed projects, but I didn't. So here I am, spending my Saturday trying to figure out what I need and pretty much admitting defeat.

Oh, I printed my invoices and reports on a printer and they work fine, but still struggling with printing to .PDF. I'm on and XP machine, version 7, and using the full version of Acrobat 5, so I'm not sure what's going on with that.

Thanks.

Have a great weekend!

Donnie

[Fenton]

You want the age of an invoice is after it's "closed" (which means "paid" in your case, right)?

Wouldn't it just be the closing date - Invoice Date. So you'd need a Date Closed field.

Case ( Invoice Status = "Open"; Get ( CurrentDate ) - Invoice Date;

Invoice Status = "Closed"; Date Closed - Invoice Date;

GetAsDate ("")

)

This would have to be Unstored, because of the CurrentDate. So I wouldn't use it much for Finds, etc.. I'd use the status and date fields directly; a little more work perhaps, but much faster.

In fact, my experience is that scripts should use date fields more and status fields less. Data entry people tend to put the date in, but then neglect to update drop-down status fields. In other words, if you have an Invoice date, and a closing date, then it's probably closed, though status may still say "Open."

An observation. Your financial structure has a dual nature. By having "open" and "closed" invoices, you are acting like payments and invoices are tightly tied together; but they aren't.

To tie them requires a more complex structure, with controlled transactions, than simply adding up all a student's invoices, adding up all their payments, and taking the difference as the balance.

This overall balance, while being slower to calculate, is more likely to be accurate, as it doesn't really depend on either dates or status being entered correctly; just money.

This is one reason accounting solutions get kind of complicated, trying to be absolutely accurate and fast at the same time. In your case, with a fairly small number of entries, speed should not be a big problem; accuracy is always critical.

I'm not saying don't have a "closed date," but I would not rely on it absolutely.

[Donnie]

Fenton,

Thank you so much for you help in this endeavor. I am very happy with my solution and am now to the point of tweaking when I need a change. I have been entering information in, past and present and have a pretty good amount of data I'm working with. I actually had to use it last weekend at class, when a parent wanted to know about some of their payments. I was able to shoot them a report of all their payments and info, that was a great feeling.

I understand what you are talking about in regards to the closed date and all that. I'll ponder that before I go any further with it, but thanks for telling me how if I decide to do it.

Have a great day!

Donnie

[Himitsu]

Hey Donnie, I don't know if this would be something you would like on it. But I added a portal that would show you all the invoices and payments that the the students make. It is on the old version that was on the first part. I tried to add it to Fentons but it is not modifable so if you like it, maybe you can just add it to the newest one you have. Have fun and good luck.

MayesMartialArtsAcademyzipped.zip

[Donnie]

Thanks for that. I like the looks of that, as it gives me all the info I'd need on one screen if that's what I ultimately wanted. Thanks for the idea!

Have a great day!

Donnie