Jump to content
Salesforce and other SMB Solutions are coming soon. ×

adding con


Recommended Posts

I have a problem. I need to add to QtyReceived in inventory to work against QtySold as I need a running total to show an accurate OnHand number in the inventory. I tried using a summary filed as Total Received but it does not work as I need running tallies coming from one field existing on multiple records. In every way, the QtyReceived-QtySold works without issue vis a vis portals and inventory but without a working TotalReceived-QtySold in inventory, the OnHand will be inaccurate whenever a new product is received from, in this case, the OrderProducts table. Any help on this would be terrific as I am no maven in scripting or in calculated fields. :)




Bob Moran

Link to comment
Share on other sites

I have trouble envisioning your database structure. Please tell in more detail which fields are in which table and which result you are after.

Link to comment
Share on other sites

Hi khoe,


Ok. I order a product via an line item Order Products table that connects buyer to products. In the Order Product table is a join table, ProductOrder. In it is ProductID, ProductDescription, UnitPrice, ExtendedPrice QtyReceived, QtySold and OnHand. The ExtendedPrice * QtyReceived, as you would know better then I, creates the extended price.


I have another line item table, ProductSell, which connects to the same products table via a join table titled ProductSell, and it has the same fields of ProductID, ProductDescription, RetailPrice, QtySold and ExtendedPrice with QtySold doing the same job as QtyReceived in generating the ExtendedPrice of the products being sold to a customer.


In every way, the OnHand calculation works (QtyReceived-QtySold = On Hand for any given instance but... When a another order is placed, the QtyReceived figure changes, as it should and, if the QtySold does not change, the OnHand will properly change as per the new Product Order QtyReceived problem but this does not work in the long term, as you would know, as I need a TotalReceived field accurately tallying up the reorder of product from which I subtract the QtySold when a product is sold. The Summary Total field works for a portal but does not when adding up QtyReceived figures keyed to a product located on multiple records. As stated before, the DB works perfectly save for this problem.


Any help on this would be terrific. I know enough about scripting and calculations to be dangerous but not great. HTH




Bob Moran

Link to comment
Share on other sites

as you would know better then I
never overestimate anybody, especially me :P Let me see if I understand your description:


[b]Buyers[/b] ---------< [b]OrderProducts[/b] >----------[b]Products[/b]-----------------<[b]ProductSell[/b]
[i]buyerID            buyerID_fk     
                  productID_fk            productID                  productID_fk[/i]
                                          ProductDescriptiom        ProductDescription      
                                          UnitPrice                  RetailPrice
                                          ExtendedPrice              QtySold
                                          QtyReceived                ExtendedPrice

Link to comment
Share on other sites


I'm not sure if I understand that structure.

So you are buying and selling goods and you want to keep track of this, an order form can either be you buying something or you selling something, and the transactions must be reflected in the OnHand field yes?


Sorry for all the baby steps but I need to understand fully before I give advice.

Link to comment
Share on other sites

Ordering or selling products is not an issue here. The problem I am having is when a new order of the same product is placed, I want to add up the Qty Received's on both orders to create a TotalReceived number that is subsequently subtracted by QtySold to generate the correct OnHand number from all the items received of the specific product. That's all I need but for the life of me, I cannot get the right TotalReceived number. I've tried Total of QtyReceived and Running Total and it does not work. In the OrderProduct table, I use a portal as folks will order more then just one product so I don't know if that is throwing me off.


Again, thank you so much for helping me out.

Link to comment
Share on other sites

I want to add up the Qty Received's on both orders to create a TotalReceived



In the OrderProduct table, I use a portal as folks will order more then just one product

Portals will not interfere with the database itself, they are merely elements of the user interface.

However I think your structure is wrong or I am misunderstanding it. Either way, I'd structure it like this:


Buyers ---------< OrderProducts ----------< ProductSell >---------------- Products ----------------<ProductBuy
buyerID           buyerID_fk
                 orderID                   orderID_fk                                              orderID_fk
                                           lineitemID                                              lineitemID
                                           ProductID_fk                  ProductID                 ProductID_fk
                                           QtySold                       QtyReceived               UnitPrice
                                           RetailPrice [lookup value]    UnitPrice [lookup value]  QtyOrdered
                                           SaleAmount                    RetailPrice                   ExtendedPrice 



And then the calcs you asked for could be:

ExtendedPrice = QtyOrdered * UnitPrice
TotalReceived = Sum ( ProductBuy::QtyOrdered ) 
TotalSold     = Sum ( ProductSell::QtySold )
OnHand        = TotalReceived - TotalSold

If you really need a running total in a list view, then a different approach may be called for.

If you need more assistance or if I am wide off the mark please post again.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use