How to create a grid/matrix view?

[RWX]

I'm trying to create a grid/matrix-style view for my current FileMaker project and would welcome any thoughts on the subject. Let me explain...

I'm developing a system for a school. When a student signs up for classes, he/she has the option of paying over time. The student must be paid up by the time the class starts. So, I have an Invoices table in my file, a Classes table, and a Join table connecting the two. I also have a Payment Schedule table where the broken down payments due are stored.

Let's say that John Smith takes Business 101. Business 101 costs $500. John will make five payments of $100. John also takes Public Speaking. Public Speaking costs $750. John will make three payments of $250 each.

Right now I have the payments displayed via a portal. I also have a report that groups payment by date, and essentially looks something like this:

Payment Due Date: 10/1/2004

Business 101: $100

Public Speaking: $250

TOTAL DUE: $350

Payment Due Date: 10/15/2004

...

Standard stuff. :-)

So, here's what I'd like to be able to do: I want to take the payment information and create a grid-style view. The grid would like something like this:

Class | 10/1/2004 | 10/15/2004 ...

Business 101 | $100 | $100 ...

Public Speaking | $250 | $250 ...

(Of course things would line up neatly on the grid...)

Since the number of classes on a contract and the number of payments a student can make isn't limited, the grid should be able to expand in either direction as needed. The grid sort order should be based on class name descending and due date from left to right.

Has anyone reading this created anything like this in FileMaker? Tips? Suggestions? Is there a plugin available to create such a view? Right now the only solution I've come up with is to use a table of repeating fields. To avoid data entry errors, I won't allow anything to be entered on the grid. It is simply for display/printing purposes. My current thinking is to create a table containing global repeating fields. When the user clicks the grid view, a script would run and build the grid.

I can forsee a number of problems with this approach. First, I will have to limit the number of classes on the grid and the number of payments, as these values will need to be specified when creating the repeating fields. If I allow for a maximum of 10 classes to be displayed and 20 payments per class (10 global fields with 41 repetitions each), my grid will look pretty bad if there are only two classes on it with three payments each. There will be lots of empty space. I suppose I could creat different views for printing and direct the user to the correct view based upon the number of classes and payments on the grid, but this seems rather excessive.

So, I ask you, what are your thoughts???

Thanks!

-Rob

[transpower]

The problem is with the database architecture. You need to have a line items table for the invoices. Then you need to add a payment field and payment date to the line items table--payments should be distributed to various line items. Then display this table in Table View, sorted and selected by student, and you will have your grid.

[RWX]

If I'm not mistaken (maybe?), my Join table is the line items table to which you refer. Invoice records are joined to Class records via the Join table. Since multiple payments are allowed per class, a fourth table, Payment Schedule, is where the actual payment amount and due date is stored. These records relate back to their respective Join table records (class enrollment record).

I haven't used the Table View much, but it seems that it would not work here. I have a single Invoice record related to two Join records, each of which is related to a Class record. For the payments, I have multiple Payment Schedule records related to each of the two Join records. How would the Table View display this? What if the Join record for Business 101 has five Payment Schedule records, wherease the Join record for Public Speaking only has three? What if one of the Public Speaking payments skips a date; in other words, Business 101 is paid on 10/1/2004, 10/15/2004, 10/30/2004, 11/15/2004, and 11/30/2004, whereas Public Speaking is only paid on 10/1/2004, 10/30/2004, and 11/30/2004? I don't see how the Table View could be used to display this...

Any clarification re: the Table View would be greatly appreciated. As I said, I haven't used it much, so perhaps I'm completely missing something.

-Rob

[transpower]

Is each invoice for one class or for multiple concurrent classes? If the latter, then you should have a line items table, one invoice line item for one class. Then when payments come in, they have to be applied to the individual classes. Then it's easy to construct the grid from your line items table.

The invoice line item table should have studentID, invoiceID, classID, invoiceDate, lineItemAmount, paymentDate, paymentAmount for this line item.

[Reed]

There is something like this in the book "Filemaker 5.5 Technique for Developer" by Chris Moyer and Bob Bowers. They call it "bucket reporting" and they had a couple of different ways to do it: One was to create a calculation field for each of the columns in your report, that would calculate amounts for each bucket (month, person, quarter, year etc.) Then have a summary field for each of the calc fields to do totals and subtotals on the rows. The other technique involved exporting data to a utility table and I can't remember right now exactly how it worked.

You might also want to check out endoshin's fast summaries technique which allows you to create cross-tab and other types of reports in Filemaker.

http://www.onegasoft.com/tools/fastsummaries/index.shtml

HTH,

Dana

[RWX]

Is each invoice for one class or for multiple concurrent classes? If the latter, then you should have a line items table, one invoice line item for one class. Then when payments come in, they have to be applied to the individual classes. Then it's easy to construct the grid from your line items table.

The invoice line item table should have studentID, invoiceID, classID, invoiceDate, lineItemAmount, paymentDate, paymentAmount for this line item.

I hate to say it, but I still don't quite follow. :-( Each invoice may contain multiple classes. Someone may sign up for three different classes on a single invoice. Each Invoice table record is joined to each Class table record via the Join table. I consider this to be the line items table. The fourth table I mentioned, the Payment Schedule, is where the required payments for each invoice line item (Join table) are stored. When payments are received, they are entered into a Payment Register table and applied to the correct Payment Schedule record. Payment Register entries may be split so that there might be several payments per Payment Schedule record (in case a student can't make the full payment one month, for example). This part of the system is rather complex, but my client has insisted upon things being set up this way. But that part of the system works. In fact, it all works. I'd just like to be able to create this grid view.

The grid is not concerned with payments received. It is only concerned with the payment schedule, when the student needs to make a payment and how much he/she needs to pay. This is determined at the time the student enrolls. After the student has signed up for the class, the payment schedule is agreed upon between the student and the school. The payment schedule might be a single payment in full or it might be ten payments over the course of three months.

I'm confused by what you mean by an "invoice line item table." I consider this my Join table. This is where the line items (related to Classes table) for each invoice record are stored. But this has nothing to do with the payment schedule. Payment date and payment amount fields in this table would allow for a single payment to be made. However, I need multiple payments, hence my use of a fourth table, the Payment Schedule.

I still fail to grasp how to create this view using the Table View. :-(

-Rob

[RWX]

There is something like this in the book "Filemaker 5.5 Technique for Developer" by Chris Moyer and Bob Bowers. They call it "bucket reporting" and they had a couple of different ways to do it: One was to create a calculation field for each of the columns in your report, that would calculate amounts for each bucket (month, person, quarter, year etc.) Then have a summary field for each of the calc fields to do totals and subtotals on the rows. The other technique involved exporting data to a utility table and I can't remember right now exactly how it worked.

Thanks for the reference. I'll see if I can find that book somewhere. A calculation field might be the answer. I've been going over all sorts of possible ways of accomplishing this and I can't seem to find a remotely easy one. It's either calculation mania or heavy-duty scripting. Unfortunately there doesn't appear to be any sort of matrix/spreadsheet plugin for FileMaker either. It appears to be a snap in 4D (via a plugin), but alas, not FileMaker.

-Rob