0

List of Items Purchased from specific vendors

Olga Brichto

June 28, 2018 19:49

None

Follow

Need to pull all items purchased from vendor for a period of time - help please! 

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Harry Lewis June 28, 2018 19:55

  Hello Olga -

  If you are using Dynamics NAV, I assume you would want to query the Value Entry table where the Source No. is set to the ID of the vendor you want and the Source Type field is set to Vendor.

• 0
  
  Malcolm Johnson June 29, 2018 12:52

  Hi

  I would do this by

  1. In the Options Tab create a data filter, give this the Name 'DateFilter'
  2. In the body using Rows = 4 create a list of vendors based on the Item Ledger Entry using the Source No. as:

   =NL("rows=4","Item Ledger Entry","Source No.","Source Type","Vendor","Entry Type","Purchase","Posting Date",DateFilter)

  3. On the next row create a list on item sold as:

  =NL("rows","Item Ledger Entry","Item No.","Source No.",C8,"Source Type","Vendor","Posting Date",DateFilter,"Entry Type","Purchase")

  where C8 is the vendor No.

  then use NL First to get the item description and the NL Sum functions to calculate the Qty.   as:

   =NL("sum","Item Ledger Entry","Quantity","Item No.",$B10,"Source Type","Vendor","Entry Type","Purchase","Posting Date",DateFilter,"Source No.",$C10)

   

  

  Note you carry the vendor ID down onto the lines

  Regards

  Malcolm