Purchase Orders and Invoice reconciliation

[AndyCan]

Hi guys,

This question is based on the purchase cycle of a firm and how to manage the reconciliation of Purchase Orders (POs) that we generate with the corresponing deliveries and Invoices of our suppliers. I have chosen not to manage delivery notes in the database and am only concerned with tracking invoices since an invoice has all the info of a delivery note and much more.

Basically we've been running our bar without POs. An employee simply does a cursory inventory and calls the suppliers to request more liquor. I would like to change this process so that we used printed POs which can then be used to check the delivery when it arrives. The purchases are made on a weekly basis with the product being delivered a day or two later.

I first thought of adding a separate table named Purchase Orders, but the more i look at it, the more i realise that it would be almost identical in design to the existing Invoices table that we already have.

What i'm thinking is that since the Purchase Orders and the Invoices we receive from our suppliers are almost identical, i should manage all the information in one table (obviously with supplier details, product details, etc in their own related tables). If i'm right in my logic, we could minimize the employee time spent on inputting the invoice details. Instead, the employee managing the purchases would input the details of the order and when the product arrives, it would simply be a case of ticking off items, adding the supplier invoice ID number, details of who received the delivery, etc. Thus our PO is 'converted' to an invoice. In reality, there would be no 'conversion' but both the PO and Invoice details would be in the same table with a few additional fields to separate the two.

I guess i would have to add a 'received' boolean field to the table to manage receipt of deliveries and to generate the data needed to display an invoice. Or perhaps better still one field for 'qty ordered' and another for 'qty received' in case an order for a particular product is only partially fulfilled. Ie. we ordered 24 bottles of vodka and the supplier only delivered 18.

Is this a logical way of managing the design? Unfortunately we often don't recognize the shortcomings of certain designs until they are used in production and that is what i'm hoping to avoid with a bit of advice from the guys at FMForums!

Thanks as always for your time in helping me with this one.

Andy

PS. I've added a jpeg of our Invoice layout. A large amount of the product info is auto populated with lookups and calculations. Don't be shocked by the prices... They are in Pesos not Dollars. If we had the kind of money to make 800 thousand dollar liquor orders i would have hired a filemaker consultant a long time ago! Unfortunately we do not. ;)

Edited August 18, 2008 by Guest

[AndyCan]

Quick update...

I'm leaning towards having two tables; one for Purchase Orders and one for Invoices. These two tables would have the minimum of necessary information, eg. PO ID, Employee, Date and InvoiceID, Received By, Invoice Number, etc respectively.

These two tables would become the parent tables of what is currently INVOICE LINES. Thus it would only be necessary to add a few additional fields such as _fk_PurchaseOrderID, QtyDelivered, PODate, etc. to INVOICE LINES for this to work.

How does that sound? Let me reiterate that i'm not asking for any help in building this solution... What i'm looking for is for someone with experience to point out any possible flaws in this way of doing things. This is a time sensitive project and i'm afraid i don't have the luxury to design it all to only then realise that the solution won't work in production. On any other occasion, i would agree that trial and error is the best way to learn.

Thanks again,

Andy