Date and Report question

[bvdsluis]

Hello everybody,

For a friend I made a simple and ugly database (I will make a nice interface later). My problem is that he needs to track the customers who have not paid the whole bill after 30 days. As you can see in my file in layout#1 and you push the red LP button you will get a list of the customers who didn't pay everything but even the customers who came yesterday. What also would be nice is a total of the bills who are not paid within the 30 days, the total of the Late Payments report. I have searched the whole weekend at this forum and also in the Filemaker pro bible but I could not find the solution. I have tried a lot but no result. I am sure it is a basic thing but for some reason I don't see it.

Probably this is so simple and that is why I cannot find the solution in all these resources but I am an absolute beginner and my friend cannot wait to start to use this database. So who can help me?

Thanks in advance,

Bob

FileMaker Version: 5

Platform: Windows XP

Borad.zip

[Ender]

Usually this type of system is handled in two or three tables. One for the Customer, one for Invoices, and one for Payments (Invoices and Payments could be in the same table, just accessed through two different relationships.)

A Customer can have many Invoices and many Payments. Invoices add to their Balance Due, Payments reduce the Balance Due. Balance Due is calculated or scripted in the Customer table. Each Invoice and Payment has a Date.

Getting the total Balance Due is pretty easy. But finding Balance Due Over 30 Days can be a bit more tricky. It depends on the dates and amounts of the Invoices and the Payments.

[bvdsluis]

Hello,

Thanks for your reply, I have seen in other examples that the data goes in different tables, but I thought in this case it is not necessary. What would be the benefit? If this approach is wrong than I can start over, no problem as long as the database is working properly

Bob

[Ender]

In a flat file (a single table,) your users end up doing a lot of duplicate data entry, and many things are harder or impossible in a flat file.

Example: You want to track invoices and payments. To do this in a flat file you would have one record per invoice or payment. Every time a customer is billed or makes a payment, you have to reenter the Customer Name, Phone, and Address. There is not an easy way to make a Customer ID (which is essential.) The only way to view balances is in Preview Mode with sub-summaries, or self-joins (assuming you get the Customer ID problem figured out.)

Alternatively, you could make your records "Customers" and use repeating fields as line items for invoices and payments--but this has issues too.

Much better to use multiple tables.

[bvdsluis]

Thanks again for your reply,

This is the first database I have made and I followed the way it was done in the Filemaker bible, a flat file. I hope I can find somewhere else a better tutorial because this book doesn

[Ender]

You should study up on portals and relationships. Once you get the basic idea of how these work, this problem won't seem so hard.