5 Replies Latest reply on Mar 30, 2016 5:28 AM by DanielShanahan

# Q: Global Stock Count Calculation

I am trying to bring together a 'Global' stock count for each product record in an 'invoices' style solution.

I have a number of factors that affect the stock count but effectively it is ( stock purchased - stock sold ) with a few variables in between.

Tables Associated:

• Purchase Orders
• PurchaseOrderLineItems
• Invoices
• InvoiceLineItems
• Online Orders
• OnlineOrderLineItems
• Products
• ProductOptions (this holds size variations for each product e.g. Small, Medium, Large etc)

Each parent table here has a boolean field called 'Deduct Stock' (except purchase orders).

Doing a simple 'Sum' calculation works but does not take into account the 'Deduct Stock' variable. By using an 'if' statement only repeats once, so it takes the 'deduct stock' value from the first record only and sums all records based on that result.

This is what I mean:

Let ( [

\$purchased    = Sum ( PurchaseOrderLineItems::Qty_S_Received ) ;

\$invoices    =

If ( Invoices::Deduct_Stock = 1 ; Sum ( InvoiceLineItems::Qty_S ) ; 0 ) ;

\$onlineOrders = Sum ( OnlineOrderLineItems::Qty S ) ]

; \$purchased - ( \$invoices + \$onlineOrders )

)

In the above calculation, if the first record deduct stock is 1, it assumes everything is 1. If it isEmpty, it gives a "?".

My question is: How can I make this work using native calculation? Or do I need to be using a relational eSQL query to calculate?

• ###### 1. Re: Q: Global Stock Count Calculation

Do you have a Transactions table that tracks the movement of inventory?  If so, you can determine what you have on hand, available, allocated, etc.

I created a short video of various ways to update stock quantities.  There is another method that isn't in the video but is in a download file.

• ###### 2. Re: Q: Global Stock Count Calculation

I do not have and inventory dedicated transactions table no, I was hoping to achieve everything solely in the Products table. The inventory count only needs to be very basic, I don't have to really allocate stock etc...

Would you recommend a dedicated table to track inventory? How would you implement this in my solution where stock count is only affected if the 'dedicated stock' field = 1?

• ###### 3. Re: Q: Global Stock Count Calculation

lcot17 wrote:

Would you recommend a dedicated table to track inventory?

Yes, even for a basic inventory solution I do recommend a Transactions table.  In my opinion, it is an essential component of an inventory solution as it shows the movement of inventory, no matter how slight.

How would you implement this in my solution where stock count is only affected if the 'dedicated stock' field = 1?

If you have FileMaker Pro 14, take a look at the Inventory Starter Solution.  The Transaction data must be entered manually, but it gives you an idea of how to track the movements.  Make sure to create separate records for transactions going out and transactions going in  (see image).

HTH.

• ###### 4. Re: Q: Global Stock Count Calculation

DanielShanahan wrote:

Yes, even for a basic inventory solution I do recommend a Transactions table.  In my opinion, it is an essential component of an inventory solution as it shows the movement of inventory, no matter how slight.

I have spend a few days now playing around with a few ideas I had in order to do what you suggested. I am struggling to think up how I can best approach this query. I see what you mean with the transactions table but that is simply for individual products.

My business has two invoice types: 1) Standard Invoice and 2) Consignment Order

1) This is a standard process. An order is sent and stock is deducted from the count as 'sold'.

2) This is an ongoing process. An order is placed, stock is therefore 'in-use'. At the end of the term (could be any length of time), the remaining 'unsold' stock is returned and therefore 'available'.

Without doing an individual transaction for each product, how would you best approach this? To include a transactions table? How do I best approach a consignment order transaction where stock is 'in-use' and then either 'sold' or 'available' when returned?

• ###### 5. Re: Q: Global Stock Count Calculation

I would have several fields associated with each inventory item:

Available (what can I sell or lend on consignment)

Allocated (what has been promised but not yet picked up or shipped.  Note that if your business is a point of sale, you probably don't need this field)

On Hand (Available + Allocated.  Again, if you have a point of sale business, you don't need Allocated and therefore don't need On Hand).