0

Summing quantity by another table

Jet Reports Historic Posts

January 26, 2015 22:23

None

Follow

Hi All

I'm not even sure this is possible. Been looking at this for a couple of hours.

I'm trying to sum the Item Sales (LCY) by a field "Weight per 1000 in another table called CPS Attributes.

=NL("Sum","Item","=NF(,""Sales (LCY)"")*=NL(,""CPS Attributes"",""Weight per 1000"",""No."",NF(,""No.""))",C6)

The No. Field in both Tables represents the Item No. Am i losing the plot or what!

Thanks in advance

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts January 27, 2015 23:32

  Hi Chris,
  
  First, it looks like you have an extra = sign. I would remove the = sign before the NL for the CPS Attributes.
  
  Second, as I look at the function, it looks like "C6" is a listed as a FilterField but there is no filter value. What is that referring to?
  
  If I copy and paste your function into my worksheet, remove the extra "=" and "C6", it works for me. (after I change the Weight per 1000 to something in my DB)

  • jfx.png
• 0
  
  Jet Reports Historic Posts January 28, 2015 09:36

  Hi,
  
  as JET-btr says, it's not clear what is in C6..
  
  regards

• 0
  
  Jet Reports Historic Posts January 30, 2015 10:33

  Thanks Guys
  
  The equals sign was the problem

• 0
  
  Jet Reports Historic Posts October 21, 2016 13:58

  Hello All…. I'm new to the forum, and to Jet Reports so please forgive me if I use the wrong terminology..
  
  I'm attempting to create an Inventory As of Date report for month end closes and reserve calculation analysis.
  
  I'm using the Item Ledger Entry Table with field key (Item No., Location Code, Lot No.) to get the unique records and filters on the posting date through ".. mm/dd/yy".
  
  Attempting to create a method of summing the total from the Value Entry table for the G/L postings that correspond to the same record entries in the Item Ledger Entry. I know that the link between the two tables is the Item Ledger Entry No & Entry No.
  
  The Lot No. isn't available in the Value Entry table, so I was attempting to create an array to get the corresponding records in each table, and then get a total on the fields I need from Value Entry.
  
  I think post points me in the right direction, but I think I need to make a subset of records somehow to get the total.
  
  Any thoughts?
  
  JMData Consultants