Skip to main content
Solved

Calculation to show only parts available in all but one warehouse

  • 3 August 2023
  • 6 replies
  • 47 views

We enhance some parts and once enhanced they are moved to warehouse ‘B’ (still under the existing part number) and stored there until sold. 

I am trying to produce a report from a GI that shows how many parts we have without including the enhanced parts from warehouse ‘B’.  To confuse things in this report we also want to show the demand on sales orders across the company including warehouse ‘B’ so I can’t do a standard condition on the GI to exclude warehouse ‘B’.

This is a summary report so one number per part for stock and demand.  If it helps all data is coming from InventoryItem and SiteStatusSummary.  

How do I do this?  I have tried some IIf statements in the GI on the field but I get either 0 or the total including the stock in warehouse ‘B’.

Could you share the GI as I believe there is a number of ways of doing this using either IIf conditions on the fields on the results not to include Site B or you could use the SiteSummaryStatus and join the InSiteStaus on warehouse B to get the stock quantities from there or you could use the InSiteStatus and ISItemPlan as well.

Could you share the GI as I believe there is a number of ways of doing this using either IIf conditions on the fields on the results not to include Site B or you could use the SiteSummaryStatus and join the InSiteStaus on warehouse B to get the stock quantities from there or you could use the InSiteStatus and ISItemPlan as well.

Thank you for your response. What is the best way to share the GI?

Export as an XML and then attach the XML to this post

 

XML is attached.  

‘Qty on Hand’ and ‘Qty available to sell’ needs to show parts in all but Site B.

All the others need to show demand or PO’s for all sites.

Any help at all is much appreciated.

Try this i added the InSite table so i could get access to the SiteCD. If you look at the results grid as i am unsure which lines you want to exclude so i changed the logic on lines 8 and 10 so you should be able to follow the logic - (Change the B to whatever the warehouse code is)

 

You can save this then use the Import from XML to load it.

Try this i added the InSite table so i could get access to the SiteCD. If you look at the results grid as i am unsure which lines you want to exclude so i changed the logic on lines 8 and 10 so you should be able to follow the logic - (Change the B to whatever the warehouse code is)

 

You can save this then use the Import from XML to load it.

dcomerford, you are a genius!!

I am getting someone else to check the numbers but all checks I have done show this has worked like a charm.

Thank you so much.  Apologies for not coming back to you as fast as you responded to me, unfortunately I have been dealing with issues all week.

Reply


Terms & ConditionsCookie settings