0

filter item entries which have remaining quantity at a historical date

Gideon Mandel

July 10, 2020 10:55

None

Follow

Hi,

I would like to filter/select all item ledger entries which meet the criteria that the quantity of the entry until a chosen date in the past is higher than zero.

In other words for example I would like to select today all item ledger entries which have a remaining quantity as at 31 May Higher than zero (Meanwhile the remaining stock as at 10 July of this entry maybe zero).

So I can use NL("Sum","item ledger entry","quantity","entry no.","12345","posting date","..31/05/2020") to determine for each entry what is the quantity as at 31 May and then hopefully filter the result - entries that still have quantity on that date.

is that possible?

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Yuko Saito July 13, 2020 03:11

  Hi Gideon, 

  I think this will be better done by having a data dump of ILE into a table, then create a pivot table which you can filter by "before" data (to include all the posting before a particular date of interest"

• 0
  
  Gideon Mandel July 13, 2020 13:07

  Hi Yuko,

  Thank you for your reply. do you mean a manual data dump? I run this report on regular basis which means I will have to create a new data dump (with updated new entries) every time, no?

• 0
  
  JaeW_atOnyx July 14, 2020 09:48

  Gideon Mandel, you can use NL("Table") to 'do a manual dump of the table".    The Table Builder tool will help you select the columns and filters of interest, if you don't know how to write an NL("Table") function by hand.

   

  I've done a couple tutorial videos on your topic of interest:

  https://www.youtube.com/watch?v=BcTt9d8eRu4&list=PLUy_qbtzH0S5_vK2PbuFHlDyoHiyaWz-G&index=21&t=179s

   

  Given that the Item Ledger is an aggregation of the Value Entry table, you'll want to validate whether Remaining Quantity is a FlowField by using the Browser.  

  In doing so you'll also confirm whether the Remaining Quantity is a calculation that accepts a Date Filter argument.

  https://www.youtube.com/watch?v=TQ4RAuqnUy8&list=PLUy_qbtzH0S5_vK2PbuFHlDyoHiyaWz-G&index=18&t=21s

   

  Hope this helps!

  jae@onyxreporting.com