Multiple Invoices from One Order

[ryancgarrett]

I have a pretty simple database that we use to track customers, orders, invoices, and payments. I have an order form that shows all of the order details, and has a subform with services, qty, price, etc. Sometimes we need different products that are part of the same order to appear on two different invoices (say an order is for part 100 and part 101. If part 101 ships first it needs to be invoiced, and part 100 will be on a separate invoice when it ships later), so one order can have multiple invoices.



I'd like to create a solution where when a user clicks the invoice button on the order form, they are able to select which lines from the subform should be invoiced on that particular invoice.

What is the best way to accomplish this?

[orange]

I found a data model that shows orders and invoices. There isn't any description to go along with the model, but it may give you a starting point for review.
You may find some general commentary by using google.
Maybe someone with a background in finance/accounting will offer more focused advice.

Here is another model with some business rules/thoughts.

[ItsMe]

I think the problem is that you have a simple database that manages customers, orders, invoices, and payments. In my experience, a database that manages only a couple of these operations/entities is a complex database. So, to answer your question, you need to redesign your database. You need to start at the table level. When designing your tables, you need to consider the Business Rules as you define the entities and define how the entities relate to one another.

There is this thing called Big Design Up Front. Sometimes, BDUF is a yes or no question. I like to approach it as a scale of small to great. From a yes vs. no perspective, I do not prefer BDUF (no BDUF please). I would prefer to have real world trials of an actual application before declaring a product finished or even Beta. But, when I am considering the Business Rules and defining the Entities, I will try to determine how much up front design is necessary. Circumstances like yours require a greater amount of up front design than other circumstances might require.

In other words, it is now realized that implementing the application without first considering the Business Rule of 'Backorders' has created a large problem. I could be wrong. But I sense your issue is a common mistake. If the current application has been in production for a while, it will require some complex queries to migrate the current data into the new data structure/tables.

[orange]

I just read ItsMe's response, and I agree with the general approach to designing and developing a database.
Get your basic facts, build a conceptual model, gather some sample data or create some, identify some test scenarios and test those against the model. The model doesn't have to be too detailed to do some general proof of concept testing. If you have some things that you'd like to include in the application, but not a priority at the moment, you can include them as "black boxes" - you know what they are, and where they fit, so keep them visible in the model. The model serves as an "artist's concept", then, as more detail is added, it becomes the base for a prototype, and with experience and more detail, it becomes the blueprint of the final design and for development.

Good luck with your project.

[ryancgarrett]

Thanks for the answers guys. I'm actually still in the development phase on this database, and have seen this as a potential issue. The data models that you shared are very similar to what we have set up. Here is an image of our order form:



Basically, when I push the "Create Invoice" button, rather than automatically creating an invoice for all the line items, I'd like another form to pop up (possibly with a listbox?) that allows the user to select the line items they'd like included on the invoice being created. I'll probably add a hidden field on the line items with a boolean value so that the same line items can't be invoiced twice, or at least a warning will be displayed. Any ideas on how to achieve this?

[ItsMe]

You might want to include a Boolean to determine the status of an ordered item. You will have to look at your entities to determine if there will be a need for additional tables or not. I will typically use separate tables to store Sales Orders, Shipments/Fulfilment, Invoices, and maybe a few others.


It seems as though you found a template and are trying to make the template work for your situation. This is a difficult thing to accomplish. I see it this way. Either there is an off the shelf solution that suits your needs or you need a custom app. If there is not an off the shelf solution available, there is not a template available. I only consider templates useful as examples of how a single problem might be solved. I do not look at templates as a jumping off place.

[orange]

Have you identified the steps -each logical step - required to Create an Invoice?
How often do you invoice only part(s) of an Order vs entire Order (all lineitems)?

Can you show us your data model/relationships window?

[ItsMe]

@ryancgarrett

Try not to let me discourage you. There are plenty of people here that would love to help you solve the problems you are encountering, including myself. I just feel it Important to mention a few things. I hope to convey a message that illustrates the importance that you disclose to us where you are at and what you are trying to accomplish. I know it is hard to ask questions. I would add that it is important to try and place your questions in context.

Maybe you can try and answer Orange's questions and include a small backstory.

[ItsMe]

Edit: wrong thread

[June7]

Possible options for data structure:

1. field in tblOrderDetails for InvoiceID - no Boolean field

2. tblInvoices with a related tblInvoiceDetails - again no Boolean field as the OrderDetail status can be determined by query