Hello,
I'm trying to create a basic table that will give me inventory totals based on a specific date. What I would like to do is exclude items in inventory that have a total of 0 (zero). What I've done in the attached example is used the table builder to pull a list of items from the Item table and linked that with the Item Ledger Entry Table where I can find my inventory quantities. At this point, I keep coming up with #Value errors when I nest a linksum into the Table Function in D15 as well as nesting it in the Link function in E12. In the attachment, I have the linksum in D15 with a #Value error.
If anyone could provide any tips or assistance here, it would be greatly appreciated.
Thanks in advance!
Karl
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi Craft,
Is there a specific reason you are using the Table Builder over any other tool? The issue I can see is if you have items at different warehouse locations, you aren't going to be able to take that one item and associate it with multiple locations as constructed in the Table Builder since it seems you are staring from the Item table. That could be a one to many relationship.
That said, you can always find the QOH for a specific date by using the "Net Change" field in the Item table with an open ended Date Filter (i.e. "..05/27/15") and even apply a location filter to it as well.
See the attached file where I built a Grouping report that shows the QOH by location by item. For each item, I listed every location it is found at. For each location, I showed the current QOH and the historical QOH using the Net Change as well as the SUM of the ILE which return the same values. I also filtered out any items where the QOH for a specific date was 0.
You can then use the concept of "Sort by sum" (Search the message board for step by step) to then using that calculation for the filterfield with a ">0" as the filter value. (built into example but takes some understanding of what is happening)
Hope that gets you started. -
Jet Reports Historic Posts Thanks for the advice. I didn't realize that the Net Change field on the Item table could be filtered by date. That's a huge help and opens up the inventory report to be used a lot faster with a replicator. I was using a Table function so that I could include slicers and also, a little research into the LinkSum function showed that I was able to exclude quantities of zero for the users but it doesn't sound like that is going to be possible. Ultimately, what I'm trying to accomplish is generate a list of items in stock by variant but not include items and variants that we do not have in stock.
Is it easy to exclude sums of zero using the Sort by Sum method? I've never tried it. I've used the Sort by Sum concept in the past to create top ten lists or sort sales by a value highest to lowest.
Thanks for your help!
Karl -
Jet Reports Historic Posts Yes, using the sort by sum (without the sort) can be used with a ">0" as the filter value. In the example in the message board it uses "*" to not filter anything but you can apply other values too. I built that into my example so you should be able to see it working.
A lot of people don't know about the Net Change field but it is very helpful.
Please sign in to leave a comment.