No of available items - stock

Question

Hi guys,

I had some issues in my project and I have discussed it previously here

Here is my question

Say I have 3 tables

tblProduction

ProductNo ProductFor BatchNo Quantity
007361      James        10018    3000
-----------------------------------------------------

tblPurchase

ProductNo BatchNo Quantity
007361     10018    100

-------------------------------------------

tblSales

ProductNo BatchNo Quantity
007361     10018    2000

I need to implement this

Production + Purchase - Sales

and get the value for each and every product.So I can find the stock.

any ideas

Thanks

Answer 1

I need to implement this

Production + Purchase - Sales

and get the value for each and every product.So I can find the stock.

any ideas

Thanks

Hi Hungry

I am afraid i can't understand your requirement. Where and what you want to implement. Kindly provide more info on this.

Regards

---

Rashmi Ranjan Panigrahi

Answer 2

Hi Rashmi,

Sorry for not describing the requirement correctly.

I have shown you my 3 tables and sample data for these 3 tables.

Now what I want is get the available no of items from these 3 tables.

For that I need to use this formula

Production (Production table quantity value) + Purchase (Purchase table quantity value) - Sales (Sales table quantity value)

So finally I can have available no of items for each product.

Thanks

Please ask more for any clarification

Answer 3

Hi

I am giving you a sample scenario. You have to implement as per your requirement.

Lets take a Computed Property in your Product Entity called "BalanceQuantity". Write similar like below code.

partial void BalanceQuantity_Compute(ref decimal? result)
        {
            result = this.Quantity - this.Sales.Sum(o => o.Quantity);
        }

This means the balance quantity = (Your Product Quantity - No of Sales Quantity) for a particular product.

In this way you can add the Purchase and Production quantity as per your requirement to get the Balance Quantity.

Hope you can understand.

Regards.

---

Rashmi Ranjan Panigrahi

Answer 4

You'll have to clarify more. Do you want Production + Purchase - Sales for each product number or for each batch number? Are these three tables related to a Product table or a Batch table through the ProductNo or the BatchNo properties? What are the relationships among the Product or Batch tables and these three tables?

---

Justin Anderson, LightSwitch Development Team

Answer 5

I have a relation to product table thought Product No as well as Batch No

Answer 6

This works, but it is slow:

In your product table:

Create the three computed fields: ProductionQuantity, PurchaseQuantity and SalesQuantity = (LINQ Query where Id and BatchNo.)

Then add one computed field: InventoryQuantity = ProductionQuantity + PurchaseQuantity - SalesQuantity

I use to do this in my inventory system but when the users filled it with data, I turn to Views.

Harold Glenn Minerva

Answer 7

Hi,

Can somebody show me how can I do this

ProductionQuantity, PurchaseQuantity and SalesQuantity = (LINQ Query where Id and BatchNo.)?

is that writing  a LINQ query? can you pls show me an example.

Thanks

Answer 8

Solved

Thanks