New First Forum user with a fun question!

[rblloyd]

Well as the title explains I am new with Filemakerforum.com and from what I can see I am excited to get started here! Who know maybe soon I will be capable of answering a few questions as well! Anyway to the question!

I am currently designing a database which in all effect is an invoice system that has several related database which the invoice page pulls from, mainly customer information, job site info, product info and trucking comp info(the database will be used to manage material hauling).

I would like to bill my customers weekly for all of the invoices that they accumulate through the week! It is possible that the cust may place 10 orders throught the week and I dont want to print each out and send them, rather I would perfer one page that summerizes in statement form the weeks charges. Which would be the best way to do that? I have included a copy of my database so you can better understand the situation. Thanks for all of your advice!

Randy

seattledirt.zip

[Ender]

I'd suggest a Billing table to record the Bills that are sent out each week. But first, you should probably put the Materials Ticket Line Items in a related table (rather than the repeating field you now have). This will allow you to show the Line Items from a specified date range or show the unbilled Line Items in a new Billing record for that Customer. The exact relationship will depend on what your process is.

[rblloyd]

Ok I completly understand why a statement table would be benifical but Im not sure how or why it would help to make a table for the line items. What are your thoughts how would you design this? I have already made a Statement forum that will be used as a template and I was thinking of writing a script to write to the forum. I have included a newer file, what do you think?

seattledirt2.zip

Edited March 17, 2007 by Guest

[Ender]

Since a Bill (Statement?) is not the same as a Materials Ticket (as you said, the statement will be sent out weekly to bill for all the invoices of that week), it should really be stored as a separate entity (table).

One reason for the Line Items table is to make it possible to print your Statement in a detail format that shows more than just the Materials Ticket totals, but can also show the items that were part of the Materials Tickets. Even if you don't see this as immediately useful, it's good to have some flexibility for the future.

In general, repeating fields should not be used for user editable fields. They have limitations that make it difficult to view and manipulate their data in different ways. Portals of related items are much more flexible, as their records can be viewed from different contexts and filtered and sorted in different ways.

If you're not familiar with portals, I'd encourage you to check them out in the application help or within these forums.

[rblloyd]

Ok I understand where you are coming from I will redesign the database with a statement table and a materials list table, but how do we tell filemaker to pull all of the records for a given company in a given week? I asume that is what a script is written for correct. Essencially that is the greater question in my mind at this point. Thanks for all of your help!

[Ender]

You can use a range to date relationship from Statement to Materials Ticket, and tag another TO of Materials Line Items off of that to see the Line Items. This way, you can have a portal to either the Materials Ticket records or the Materials Line Items records (or both) as well as add aggregate calcs like Sum() and Count().

The exact details will be easier to talk about once the structure is in place. It will also help to know what the layout of the Statement should look like. And what's the procedure for billing for a new week? You could bill one customer at a time, but it could also be done for all customers at once. Do you want to bill all unbilled Materials Tickets, or just the ones for a specified date range (week)?

[rblloyd]

Well I would love it if the system would automatically print invoices for all cust based on Materials tickets that have not been paid for by cc, cash, check. In other words all invoices that were marked Customer Acct in the terms feild. I this all sounds a little more difficult than I thought, maybe im in over my head on this one! I thought I was ok up to this point.. Thanks so much for taking the time!

[rblloyd]

Anyone else with thoughts or help feel free to post! I need it! Thanks