simple Invoice layout, which TO?

[flybriguy]

Hi, I'm trying to create a new database for my invoicing. I'd like a little advice on "where" or which Table Occurance to make my layout. I've begun familiarizing myself with relational databases and portals but before I go into any details I'll explain my needs. First, I was doing my invoices through Word documents, meaning I would manually enter each time the invoice numbers, client info, and services provided (this as you might know can be redundant if you've worked for the same client before). With auto-enter functions (serial #'s, dates) I know that much of what I manually type in can be simplified. So . . . . what I'd ideally like to have is: with each "new record" creation have an invoice number auto-generated and "only" have to enter the client info (name, address, t#, email) if it has not been entered before - if it has been entered before then it would be nice I can have that info appear with the least amount of repetitive typing/or entering data. This is all I need for now. On which TO should I build my layout around? (later I will also like to just enter numbers for calculations for the invoice amount such as 1 x day rate= amount, or 2 x day rate (for 2days) = amount, and then a total but is less important for now)

For now, I've created 2 tables for my invoice database. 1st is the client Table with: id name (auto-enter serial #), name, address, tel#, fx#, email. 2nd table I have as the Invoice table with: id invoice (auto-enter serial #), id name (which I think I will use a foreign key to relate to the id name in the client TO), and date (with auto-enter creation date). From here on out how else should I approach this? Should I also create a separate table for "services rendered?" To be specific I am a photographer who just at this point invoices at a simple date rate with only 2-3 different price points and day rates depending upon the client/project.

I have just started using Filemaker 8 now as well.

-brian

[Stephen.S.]

You're really going to need 4 tables; Clients, Invoices, Invoice Line Items (or just Line Items) and Services.

In your Clients table, you would relate Client ID with Client ID in the Invoices table. The Invoice table should generate it's own Invoice ID which will be related to the Invoice Line Items table through Invoice ID. Services would be related to Invoice Line Items using a Services ID relationship.

Basically, you enter the Client information in the Clients database. You will probably click a "New Invoice" button which will set a field in Invoices triggering a lookup of the client information you want from the Clients table, at the same time generating a new Invoice number. On your Invoice layout will be a portal from Invoice Line Items. When you enter a unique service code (Product ID), it triggers a lookup from the Services table for information such as the name of the service, the cost, etc. The Services table is where you would enter all the information about the services you offer much in the same manner as a product catalog; Service ID, description, cost, etc.

Hope that helps a little.

[flybriguy]

Stephen,

thanks for replying . . . everything makes sense except for a couple of things as I'm still learning this program. Not sure how to go about creating the New invoice button as well as the scripting (?) for the lookup of the client information and how it would be automatically displayed. Would the button be on the client or invoice layout?

Also, I'm assuming that with each new client I will have to enter all their info separately through the client table. Is there a way, however, to newly enter a client info directly through the invoice TO (through a portal perhaps) and have it create a new record in the client database for it to be used in the button fuction in it's lookup of client info?

The model you provide is very much similar to the examples I am learning through an online tutorial (lynda.com) as it too provides separate tables for invoice line items and services, but it is still confusing to me how or why there is a separate table for line items. What is this for, or why cannot there be a direct link between the services TO and Invoice TO itself? If anyone clearly explain, it would be nice. Thanks.

-brian

[flybriguy]

You will probably click a "New Invoice" button which will set a field in Invoices triggering a lookup of the client information you want from the Clients table, at the same time generating a new Invoice number.

Anyone know what this is? -specially the lookup of the client information bit . . . . . as it is right now I'm entering the client information through a portal if it's new (thus allowing creating records in the related table) but then entering the client id number through the portal if they're already in the client database. Could there be any other way?

Edited June 1, 2006 by Guest

[Stephen.S.]

What you're looking to do is have the Invoice table look up information from the Clients table. This way if the person should move, change phone numbers, etc., the information does not change across all invoices associated with that client.

Look at the attached file. Create a few new clients. Then click on the New Estimate button for the client you want to create an estimate for. The script simply copies the Client ID from Clients, creates a new record in Estimates, pastes the Client ID, etc. A lookup takes place pulling in the desired information. You can see a few fields of information on the right side of the Estimate Form layout. That information was looked up from Clients, and is now part of the record in Estimates. You could accomplish the same thing using Set Field instead of Copy and Paste.

New_Invoice_example.fp7.zip

[Stephen.S.]

Brian:

The Button would be on the Client layout.

You wouldn't want to enter the Client information in Invoices because every time you created a New Invoice for John Doe, a new record for John Doe would be created in Clients. Hence, 10 Invoices for John Doe.. 10 entries for John Doe in Clients. You only require 1.

The separate table for line items is because each Client can purchase more than one item on a single Invoice. To capture each item (line), you need a record (line item) for each.

You have 1 client who you can invoice 1 or more times. This requires separate Invoices for each transaction.

You have 1 client who can purchase 1 or more items on each Invoice. This requires separate Lines for each item purchased.

[flybriguy]

Stephen,

This helps me a lot . . . thank you very much.

brian park