Jump to content

Help with invoice schema - multiple orders and payments on one invoice


Conartist
 Share

This topic is 5645 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have been an avid reader on this forum and other help sources (books, the white pages, anything I can get my hands on). One thing that has constantly stumped me is the discussions on properly setting up your database in regards to invoices and how they pertain to my business model. For example I've kept this comment in my files:

""When printing an invoice, you don't print from the invoices file, because FileMaker didn't design portals to be printed. Portals don't slide in all scenarios and don't page break gracefully. Instead, you use the Go to Related Record script step to locate all the related records in the line items file and print from the line items file. It's almost always best to print from the child file rather than the parent if you have to print multiple related records."

I have been working with FM for about a year now and am planning a 2nd build of my database that tracks orders for a trade show decorating business. When I first started working here I was handed a very barebones database that was built by someone who really didn't know what they were doing. Since I really didn't know what I was doing either I started working with the poorly structured databse before I knew better. I have made many many changes in the past year to improve the structure of the database. But I have not yet figured out the best way to structure my invoice data.

My biggest issue with understanding the proper way to work with invoices is that most people discuss invoices as only a report of what the customer owes. Because of my business model, my invoices are more like a statement of the customers activity (multiple orders AND multiple payments) over an entire event (trade show). Because I want to show multiple orders and multiple payments on one invoice I have been stuck with a two portal layout. I know this is not the recommended structure and it does cause issue for me when the rare occasion occurs that I have more orders or payments than my portals have lines! Since I'm getting ready to build my second version of the database and one of my biggest shows of the year is coming up...I'm really anxious to get this resolved.

Here are some pictures of what my invoices look like currently:

Anybody have any suggestions? I want to make sure my data is structured properly to prevent errors in the future and also to resolve my printing issue (when orders exceed the amount of portal rows) but I don't want to lose the invoice that has both orders and payments....I appreciate any help and conceptual discussions.

Link to comment
Share on other sites

Another structure that you might consider is using the same table for both Product Line Items and Payments. The interface for entering the information can still keep them separate (in two separate portals, using relationships filtered for each type.) This would allow you to print from the Line Item/Payment table and summarize the two however you wish (by Date or by Type, or whatever.) The only trick is to make sure the Payments are given a negative value when added together with the Line Items, so that the summary Total balances out.

Link to comment
Share on other sites

This topic is 5645 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.