Total Inventory

[moj6054]

Hi,

I want to know qty in hand after sold qty according with my database.

I have this tables:

Board:Board_ID,Board_Name,FirstQty,Price,cStockLevel

Invoice:INV_ID,INV_Date,cTotalInvoice

InvoiceLineItem:INL_ID,INV_ID,Board_Name,Qty,Price,cLineTotal

cLineTotal=sum(FirstQty*Price) cTotalInvoice=sum(InvoiceLineItem::cLineTotal) cStockLevel=FirstQty-Sum(InvoiceLineItem::Qty)

I need to know after sold, qty in hand =?

thank you.

[imoree]

Wow; THis is quite a vague post. But I will attempt to say this is what i am getting from your post.

Fields = qty, Inventory total

Formula after finishing sale would be qty - Inventory = total

So if you have 10 items on hand and in your sale you have 2 items in order,then

your calculation or the field total would be a calculation that would take ( 2( qty) - onHand(10) )

Perhaps this may help, but if you are more specific ,then we can help U better.

: )

[moj6054]

Hi,

I want to know qty in hand after sold qty according with my database.

I have this tables:

Board:Board_ID,Board_Name,FirstQty,Price,cStockLevel

Invoice:INV_ID,INV_Date,cTotalInvoice

InvoiceLineItem:INL_ID,INV_ID,Board_Name,Qty,Price,cLineTotal

cLineTotal=sum(FirstQty*Price) cTotalInvoice=sum(InvoiceLineItem::cLineTotal) cStockLevel=FirstQty-Sum(InvoiceLineItem::Qty)

I need to know after sold, qty in hand =?

thank you.

[moj6054]

Wow; THis is quite a vague post. But I will attempt to say this is what i am getting from your post.

Fields = qty, Inventory total

Formula after finishing sale would be qty - Inventory = total

So if you have 10 items on hand and in your sale you have 2 items in order,then

your calculation or the field total would be a calculation that would take ( 2( qty) - onHand(10) )

Perhaps this may help, but if you are more specific ,then we can help U better.

: )

Hi,

I want to know qty in hand after sold qty according with my database.

I have this tables:

Board:Board_ID,Board_Name,FirstQty,Price,cStockLevel

Invoice:INV_ID,INV_Date,cTotalInvoice

InvoiceLineItem:INL_ID,INV_ID,Board_Name,Qty,Price,cLineTotal

cLineTotal=sum(FirstQty*Price) cTotalInvoice=sum(InvoiceLineItem::cLineTotal) cStockLevel=FirstQty-Sum(InvoiceLineItem::Qty)

I need to know after sold, qty in hand =?

thank you.

[LaRetta]

If FirstQty is your beginning stock level then it appears that cStockLevel is your quantity in hand. cStockLevel=FirstQty-Sum(InvoiceLineItem::Qty)

You do not have an Inventory table? If you fully aggregate calculations, it will get slower and slower over time particularly with a lot of LineItems. Using the aggregate method also means you can not adjust your stock level when you take inventory nor does it allow for damaged goods or tracking of purchases.

I find it best to use an Inventory table. Each month, after month-end and invoices are frozen, I write to the Inventory table. The totals are double-checked to summary totals via script. I then restrict the LineItems to Products relationship to current month to get the 'current aggregated' totals by usual means. The Inventory is also yearly 'brought forward', deactivating all prior Inventory lines and only aggregating the current year forward. This maintains history but keeps the number of Inventory lines to aggregate very small. Then calculation in Products would be:

Inventory::sBalance - LineItems::sQuantity

... based upon your restricted relationships both directions. That is rough explanation but it keeps the system fast. It allows direct entry of adjustments into the inventory table, such as adding stock purchases, physical re-takes, expired product and damages but provides quick retrieval of a Product's stock level.

[efen]

duplicate post

http://fmforums.com/forum/topic/82477-total-inventory/page__fromsearch__1

[Lee Smith]

I want to know qty i

Please do not multiple post your questions, I have merged your two topics.

[Lee Smith]

Automatic message

This topic has been moved from "FileMaker Product Family → FileMaker Pro 11" to "Database Schema & Business Logic → Calculation Engine (Define Fields)".

BTW, welcome to the Forum

The General topics, are intended for discussions about the applications, and not for How-to questions on creating solutions.

Lee

[moj6054]

Thank you for answer,

My question is, If Firstqty = 15 and I sold 10 of inventory, How I can set new qty for Qty in hand? I should use of a script?

[LaRetta]

New quantity in hand can be a calculation (unstored) so it just stays updated as I suggested above, depending upon how you are structured but ... if you have a field called QtyInHand in your Products table, you would create script which you place on your Products layout which sets the field like this:

Set Field [ Products::QtyInHand ; FirstQty - Sum ( InvoiceLineItem::Qty ) ]

Can you zip and attach your file or provide more information about how your relationships are set up? :)

[moj6054]

Thank you, My Question:

Why in Order layout, Qty don't like my formula in script

And can you tell me with a easy simple about transaction and global filed?

Board.zip

[LaRetta]

You do not have an auto-enter serial on your Order table. If you add it

"And can you tell me with a easy simple about transaction and global filed?"

I do not understand what you mean here. Can you explain more?

[moj6054]

I set OrderID = InvoiceID in script,

I want to know more about global filed and transaction inventory,

[moj6054]

Hi,

I send my database and have some question:

1- Please tell me more about use of global filed(may you send a sample o f use it?)

2- I want to know more about transaction-inventory ,with a sample,please.

3- according with my script: why product name don't change?

4- I want in order, cQtyOrder used for Order Qty, why doesn't it happen?

thank for your answer.

Board.zip

[LaRetta]

Why are you creating two tables (Orders and Invoices)? Why not just one? If one acts the quote and one the invoice, they still can be one table with a flag field when it becomes an invoice. But your setup seems even reversed ... you are making an Invoice into an Order?

I have no time today to assist, sorry. I will put together a sample tomorrow. :^)

[imoree]

The Entire Structure of this file is quite overloaded and sorry, is not readable.

Think you need to look @ FMP sample invoice database.

Think of this.

Customer orders item A ( cost $14.95), qty ( 2) , extended Price ( = 2 x $14.95) = $29.90

IN Items table ( AFTER YOUR SALE - your script should

go to layout ( Items , Item A ( qty ) - LineItems Qty ( 2)

If( LineItems::Qty < Item A qty ) ; Generate Error

Else

Subtract Qty Ordered from Items Qty.

GTRR

Show Items in a new layout ( list)

Enter PReview Mode.

-i

[moj6054]

Hi,

I send my database again, and thank for your help,

In my database, I use a script for automatic order, if my product's qty less of invoiceline qty, a new order make according with invoice ID.

and in Invoice, I see a "OnOrder" for that product.

My question is: According Microsoft access example, "Northwind" , I want after purchased a product, this new qty product added to it.

How I can do this?

Thank you.

Board.zip

[LaRetta]

My question is: According Microsoft access example, "Northwind" , I want after purchased a product, this new qty product added to it.

I do not know the example in which you are referring. If Orders represents placing an order to restock the product then you shouldn't run a script to create an Order every time a product is added onto an Invoice. Instead, you should run it weekly or monthly and accumulate the total quantity you need to order and create ONE Order for the product (or one order for several products by the same vendor).

Regardless, the available quantity must be a dynamic calculation and, if it is then you can just check the calculation to know whether there is sufficient quantity to sell the item on an Invoice.

You still have not explained why are you attempting to create an Order every time you create an Invoice. Please explain:

1. Why the Orders table exists?
   
2. Why you are inserting the InvoiceID into the OrdersID? If two invoices are created in the same day for Board A, you don't want to create two orders for Board A to your supplier do you? You only want one order with the total of the two invoices.
   

I need to understand this relationship and your business logic involving your Products. :^)

[imoree]

I do not know the example in which you are referring. If Orders represents placing an order to restock the product then you shouldn't run a script to create an Order every time a product is added onto an Invoice. Instead, you should run it weekly or monthly and accumulate the total quantity you need to order and create ONE Order for the product (or one order for several products by the same vendor).

Regardless, the available quantity must be a dynamic calculation and, if it is then you can just check the calculation to know whether there is sufficient quantity to sell the item on an Invoice.

You still have not explained why are you attempting to create an Order every time you create an Invoice. Please explain:

1. Why the Orders table exists?
   
2. Why you are inserting the InvoiceID into the OrdersID? If two invoices are created in the same day for Board A, you don't want to create two orders for Board A to your supplier do you? You only want one order with the total of the two invoices.
   

I need to understand this relationship and your business logic involving your Products. :^)

I agree LaRetta - the relationship does not make sense to me as well. to me it is a simple

Customers->>Orders -->> Line Items <<---- Products

[moj6054]

Please see this sample,I want to design my database according with this sample.

Northwind.zip

[LaRetta]

This only helps if someone has Access? I no longer have Access, sorry. Can you just answer my questions that I have asked over and over? Otherwise someone else will need to assist. :hmm:

[moj6054]

I create order table for, That product's qty less than order Item lines, I mean, I have a product (A) with 10 qty,

When a sell man choose this product for invoice line Item, for example 11, a new order create according with this invoice ID and Date for clear, so I need a script for check this qty, and create automatic order, and when I buy this product, add to qty product,

I send access sample,because that create this sample very nice.

I hope ,I answer to your question

[LaRetta]

Hi Moj,

Just because you place a restock order for a product does not mean it is instantly back in stock and available for resell, does it? So you should not count it in your cQtyInHand calculation as 'available' just because it exists on a reorder Order form. Your product quantity should not increase until the product is physically received and a human checks it off of the packing slip as it is unpacked. Only then is that quantity available for resell.

In the same light, you normally would not create a reorder for a product every time someone buys it. Instead, each product should contain a field called LowStock (number) which means that when your balance on hand for that product hits this number, it is time to reorder. If Customer Smith orders 5 Widgets and Customer Brown orders 3 Widgets, you do not want to create two orders (one with 5 widgets and one with 3), do you? Ideally you should create ONE reorder with 8 widgets. And if that manufacturer (or jobber or whomever you get your products from) also supplies you with other products, you should check their quantities and add those other products to the same reorder form, shouldn't you?

How you get your products and do you have multiple vendors? Also, it appears that you allow backorders on your invoices so it will help if you explain your business policy on backorders. Now, this is business as usual ... but I still lack understanding of your business situation so it might not fit. You had also asked for an example of how I would structure an Inventory process. I would be more than happy to provide an example but I am currently swamped and that is not a simple thing to put together (it is liken to asking for example on payroll) ... maybe I can git er done this weekend.

In meantime, please answer the questions I have asked here and explain more about your business and how your products are handled. I believe we are also dealing with a bit of language barrier, also suggested by your Price field in Boards, so please take your time and tell me about your products and why you want to place individual reorders every time someone buys a product from you. :^)

[moj6054]

Thank you,

according with your suggestion, My real problem is this.

how i can design my table for this problem? I saw in access sample, a "transaction table", between order and product design, and when , enter a date for buy the product, then qty add to stock.

I want to know how can design this table for my problem,

[LaRetta]

If you will be creating one order for every single time a single Invoice then why not just duplicate your Invoice and call it an Order?

[moj6054]

Hi,

If you can see the access sample, every thing will clear for you.

[LaRetta]

I can't. I guess someone else will have to assist, sorry.

[moj6054]

hi,

How I can create a script for add qty (order) to product qty?