Subtracting Inventory from a Purchase Order

[pkudrle]

I have created an Inventory and Purchase Order Databases. The PO Database

uses the records from the Inventory as a value list. What I would like to do is

subtract the overall quantity listed in the inventory as I make purchase orders.

So if I order 5 widgets, I subtract 5 widgets from the inventory. My solution

,and I want to check this, is the following:

Create a Calculated Field in the Inventory Database that Totals the Sold Quantity

from the PO Database. Then add a Calculation to the Quantity Field

in the Inventory that would subtract the Initial Quantity from the Sold Quantity.

Do you see any hickups, is there an easier way?

[Ugo DI LUCA]

Hi,

It depends on :

- what exactly are your Inventory database (Is it a Product file, a real related Inventory/stock file ?) and Purchase Order (Customer Purchase Order I assume)

- where is your calc made (in the Line Items, with Sum (related records)...?)

- Do you handle some returns ?

[pkudrle]

I guess it would be a combination. The Inventory File

contains basically

The Product, Descriptioin, ID, and Initial Quantity.

I am wanting to add a sold quantity to compare with initial

and to use for inventory. There are no returns.

The Calculation could be anywhere. My solution of having

the calculation in the Inventory was something

I thought of as a possibility. But my assumption is that there

are some standards to doing something like this and I would

like to know what they are.

Thanks,

Paul

[Ugo DI LUCA]

Hi Paul,

Rereading my answer

where is your calc made (in the Line Items, with Sum (related records)...?)

I meant in the Product/Inventory file using Sum:: Line Items records.

[pkudrle]

My guess is that I would create a calculation field with

sum(Relatedrecords) in the inventory database. The

Calculation would find all instances for the product where

is was purchased in the PO Database and add the together to

get a total. How Do I write the Calculation to find

the specific Product in the PO Database?

Hopefully this answers your question and I am not causing

more confusion.

Paul

[Ugo DI LUCA]

That is were I get confused. How can you have a PO and a Inventory file without a Line Item File. How is your PO file set ?

[pkudrle]

I apologize for any confusion I am creating. I am still

new to creating databases so there could be some jargon

I am not used to. So let me explain my PO and Inv Database.

In the PO Database I have a Ship To, Bill to Area with

appropriate Fields. In the Order Entry I have ProductID, ProductDesc, Quantity,

Price.

My ProductID is a value list based on the records from the

Inventory Database. When I select an item in the

Product ID, I automatically get the ProductDesc and Price

through a lookup.

In the Inventory I have a field for Initial Quantity and

Quantity Sold for every record.

When I select a Quantity from the PO Database I want it

to add to the total in the Quantity Sold field in the

corresponding record in the Inventory Database.

Maybe I should forget how I think it should happen and ask

for an explanation to how its normally done?

I am concerned now that what I am creating could send me into some troubled

water which I am unaware of. Especially with the confusion

I am causing. If its still unclear I will upload the file

for people to check out.

Paul

[Ugo DI LUCA]

No, it is OK,

We are just using different terminology. So your line items is The Order Entry File and your Inventory file is your Product File (that holds all product prices, description + the actual inventory).

Now an inventory is usually based on bought - sold items. Where are your bought records ?

In a standard (at least it seems standard for me) Inventory file, the calculation for Stock/Inventory counts would be a Sum(LineItem::TotalQuantityPurchased) - Sum(LineItem::TotalQuantitySold) where ::LineItem stands for a relationship to the Line Item using the Product_ID.

Your Initial Quantity should equal the first quantity from your first Purchase Order. You would regularly adjust your stock (returns, loss, brokens) with a new entry in the line items.

[pkudrle]

I have created an Initial Quanity Field in my Inventory Database, so I guess that would be similar to your bought records.

OK I see where your headed, but,

I need the Sum(LineItem::TotalQuantitySold) to be for specific Items. So that when someone purchases Widget '04, and Widget '05, the calculation in the Inventory field looks for the specific Widgets to subtract from.

Paul

[Ugo DI LUCA]

The key for relationship "LineItem" from Product to Line Item is Product_ID, so your calc would be :

Initial Qty - Sum(LineItems::Qty sold).