Proper Inventory Tables

[Jredeemer14]

Hello, I have a unique situation that I thought could require some outside help. I'm developing a database for a small appliances business and here is my situation: I currently have 2 inventory tables:

Inventory

Inventorysold

When items come in they are entered into the inventory table, when people checkout, new records are created in the inventorysold table, and every item has a serial number, so I tell filemaker to compare Inventory:serial number to the inventorysold:Serial number to determine if an item has been sold. Yet, this is a really messy setup and I'm sure there must be a better way to do this. Yet, here is the kicker. We have some items that come in that are just 1 item. Yet sometimes we could get 100 of 1 item, so Even though the relationship between invoices and inventory is through the serial number, sometimes I sell a model and I don't know the serial number right off because there are so many in stock. For example

Model : FGFL30TS

Serial: 4F232457775

or

Model FRT17IL6

Serial: 4F5555555

4F6345534

4F6767567

Any obvious suggestions? Let me know, Thanks: Jeff

[BruceJ]

The way I've done my point of sale applications includes Four tables:

Inventory (kInventoryID, Description, StartingNumberOnHand, Calc_StartNumb_minus_SUM_QuantitySold)

Invoice (kInvoiceID, CustomerID, Calc_SUM_TOTAL_of_LineItems)

Line Items (kInvoiceID_Child, kInventoryID_Child, QuantiySold, PriceEach_LookUp, TOTAL_Qty_x_Price)

Ok... here's the deal... in the Inventory you have a starting number of items on hand, simply subtract the sum of the quantity in the related line items.

If you really need to track serial numbers of items on hand and items sold... then you have to go a different approach and it's like having a unique inventory item for every single item.. pain in the butt.

If all you need to do is note the serial number of those sold, you can create a field in the line items to hold it. Of course, you'll need a new line item for each of the same items sold.

Note - be sure to work a way to reconcile the inventory through adding a field in the inventory that can be added or subtracted from the OnHand amount as surely items will disappear or reappear.

If an item is returned and placed back in inventory, just go to the invoice and change the amount sold to a Negative amount.

By the way... QuickBooks will do all this for you, but you don't get to bang your head against the wall figuring it out.

[Søren Dyhr]

sale applications includes Four tables

There is something about your counting here, Bruce?

--sd

[LaRetta]

If an item is returned and placed back in inventory, just go to the invoice and change the amount sold to a Negative amount.

Ummm, no, Bruce. Returns must generate another invoice (Return Credit Memo). You can't change an Invoice once printed, posted, paid etc.

I know you realize this and it was just your wording itself which wasn't quite right.

BTW, some companies require returned products to be returned against the exact invoice they were sold on. Why? Because matching the sku is the only way to know that the product returned was actually sold on that invoice and not an older product which was given to someone by their grandmother and sat on a shelf for 10 years. Because companies can refund on products within certain timeframes but not others. Then you have another table which relates to LineItems showing the return.

The reason some off-the-shelf accounting packages can't be used is because they are off the shelf. And businesses never follow an exact model (regardless of what Accountants wish for).

UPDATE: Besides, changing the item to a negative amount on the invoice would mean that you credited them for an item they never paid for (meaning they got their other items for less money). If anything, a new line should be added negative the product but you still never do that on the original invoice itself. Now I see why you recommend QuickBooks. :jester:

Edited June 19, 2009 by Guest
Added update & joke

[BruceJ]

I can't even count tables let alone write accounting type software! Ha

That's why I stick to medical/hospital stuff usually.

The fourth table was really a Customer table in my mind, but not really a must have unless you plan on doing something with customers repeat business. I personally hate it when I go to buy a simple little widget and the clerk wants all my personal info... I'm paying with cash and want to be anonymous, especially when I'm buying my tin foil hat!

[LaRetta]

That's funny! And I liked your story of the Soviets and the pencil as well - consider it stolen!