Hi good morning! Have you ever encountered a probl...
# administrations
Sammy
07/16/2020, 5:47 PMHi good morning! Have you ever encountered a problem on a search where the numbers and information on the detail search is correct but when tried to sum up the detailed results something in the count gets off? How did you resolve this?
c
Chloë
07/16/2020, 6:29 PMTo better understand your situation...When you are summing the results, are you using the Sum summary type in the results and that's where you're noticing the numbers are off? Or are you summing by manually adding up each line of the results?
s
Sammy
07/16/2020, 6:59 PM@Chloë thats correct. Its when using summary type in results
c
Clay Roper
07/17/2020, 2:14 AM@Sammy using saved searches to get inventory balances can be tricky. What's your search type?
s
Sammy
07/17/2020, 2:15 AMHi @Clay Roper its item search type. Then I do case when on results tab for the conditional logic of location qty available vs. Bin qty available
c
Clay Roper
07/17/2020, 2:21 AM@Sammy Do you have
ADVANCED BIN/NUMBERED INVENTORY MANAGEMENTADVANCED INVENTORY MANAGEMENTs
Sammy
07/17/2020, 2:25 AMYes the advanced bin/numbered inventory management is active.
Sammy
07/17/2020, 2:32 AMI thought of so many things to do, one is use the dense rank function in the case when so thatbthe duplicate row will just result to 0 so when I sum up the qty count will not double but looks like dnse rank cannot be used for aggregate functiona
Sammy
07/17/2020, 2:32 AM*functions
Sammy
07/17/2020, 2:41 AM@Clay Roper i know my issue is because the Bin number associated to the item (that only exists in 1 location) is repeatedly shown on the location where it does not belong to and i am not sure how to eliminate them. To illustrate,
I have bin ABC and bin CDE for example in location A and bin ABC is associated to the item (showing on Bin numbers subtab).
When i do the search, the search looks like this:
Location A ---- bin ABC ---- 50 (bin qty)
Location A ----- bin CDE --- 49 (bin qty)
Location B ----- bin ABC --- 8 (location qty)
Location B ---- bin CDE ---- 8 (Location qty)
Bins abc and cde should not tie up to location B
I tried to do case when to only associate the Bin number to its actual location but then when summing up the extra row is still counted
c
Clay Roper
07/17/2020, 2:41 AMTo get an accurate snapshot of the inventory on hand / available (the definition of which is different depending on where you look and how you're looking...) requires you to either sum the impact of posting inventory detail transactions up to a certain point in time, or to use a little trick I discovered.
That trick is:
• Navigate to an inventory/assembly item
• Select the Inventory Detail subtab
• Under the Inventory Detail sublist, click Customize View
• Click More Options
This gives you access to the "Bin Item Balance Search" type which isn't otherwise available through the Saved Search UI, and has so far (in my account at least) proven to be accurate in terms of inventory balance per bin. It doesn't give you access to inventory numbers to my current recollection, but I might be mistaken about that at the moment.
👍 1
Clay Roper
07/17/2020, 2:43 AMOnce you customize and save that search, you'll have the ability to Save As and utilize that search type for similar purposes
s
Sammy
07/17/2020, 2:45 AMOoooh @Clay Roper thanks for the tip! I will try to check the search and see if I can join that with Location Qty as well so I can conditionally retrieve Qty available as from Bin or from Location level depending on my location. Coz my requirement is if Location A, get the Bin qty available else other locations I am ok with just the location qty available
c
Clay Roper
07/17/2020, 2:51 AMGood luck! 🤞
👌 1
s
Sammy
07/17/2020, 5:09 AMThanks @Clay Roper finally solved it! I had to use distinct and that did the trick! Thanks 🤗
🎉 1
2 Views