Forum

Items stock query

Kasih Ratu	#1 Oct 19, 2012 10:02 AM
Just came Points:: 0 Joined:: Oct 8, 2012	Salam, friends! I need to get quantity of items in stock for a specific items set. I use Managed Application Demo. Could someone please help me?
	Profile

ivan avdonin	#2 Oct 19, 2012 10:13 AM
Active user Points:: 0 Joined:: Sep 26, 2012	On pane "Sales" choose report "Inventory balance". There you can make filter "In list" for items and creat report.
	Profile

Kasih Ratu	#3 Oct 19, 2012 11:13 AM
Just came Points:: 0 Joined:: Oct 8, 2012	Good, thank you!
	Profile

Kasih Ratu

Oct 26, 2012 10:01 AM

Just came

Points:: 0
Joined:: Oct 8, 2012

Hi!
I used this topic and this topic to create my query:

Code

SEL ECT
   Items.Ref,
   InventoryBalance.Company,
   InventoryBalance.QuantityBalance
FR OM
   Catalog.Items AS Items
      LEFT JOIN AccumulationRegister.Inventory.Balance AS InventoryBalance
      ON (InventoryBalance.Item = Items.Ref)

But it returns NULL instead of 0 for items having 0 Inventory balance...

Profile

Alexey Alexandrovich

Oct 26, 2012 11:44 AM

Active user

Points:: 0
Joined:: Nov 1, 2011

Hi!

Use ISNULL function:

Code
ISNULL(InventoryBalance.QuantityBalance, 0) AS QuantityBalance

Profile

Kasih Ratu

Oct 26, 2012 12:06 PM

Just came

Points:: 0
Joined:: Oct 8, 2012

Thank you, Alexey! I changed my query in the following way:

Code

SEL ECT
   Items.Ref,
   InventoryBalance.Company,
   ISNULL(InventoryBalance.QuantityBalance, 0) AS QuantityBalance
FR OM
   Catalog.Items AS Items
      LEFT JOIN AccumulationRegister.Inventory.Balance AS InventoryBalance
      ON (InventoryBalance.Item = Items.Ref)

Now there is one more question: I need to filter the result by company. How can I do that?

Profile

Alexey Alexandrovich

Oct 26, 2012 05:36 PM

Active user

Points:: 0
Joined:: Nov 1, 2011

What do you want to get as result?
The list of all Items with balance for one particular company?

Code

SEL ECT
   Items.Ref,
   InventoryBalance.Company,
   ISNULL(InventoryBalance.QuantityBalance, 0) AS QuantityBalance
FR OM
   Catalog.Items AS Items
      LEFT JOIN AccumulationRegister.Inventory.Balance(, Company = &Company) AS InventoryBalance
      ON (InventoryBalance.Item = Items.Ref)

Profile

Kasih Ratu	#8 Oct 26, 2012 07:17 PM
Just came Points:: 0 Joined:: Oct 8, 2012	Yes! Thank you, Alexey!
	Profile