0

Multiply fields without primary key

Jet Reports Historic Posts

February 20, 2013 20:14

None

Follow

Hi

How do I create at formular like this

NL("Sum";"Transfer Line";"=NF(;""Quantity (Base)"")*NF(;""Outstanding Quantity"")")

when using a SQL database without at primary key?

Do anybody know how to define the field cache in this situation?

/Carsten

• Facebook
• Twitter
• LinkedIn

3 comments

• 
  Jet Reports Historic Posts February 21, 2013 16:07 Official comment

  Carsten,
  
  There is a page about that on the Jet Reports help website here:
  
  http://help.jetreports.com/12.5/Essentials/SQL=.html
  
  Does that help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts February 20, 2013 20:29

  Hi Carsten,
  
  What you are using there is a calculated field, not a field cache. The answer is that you can't use calculated fields in a Universal connector; they are only available for NAV connectors. If you're using a Universal connector, the only way to achieve this sort of result is to write it as a SQL query and use SQL=. The syntax is something like this:
  
  =NL(,"SQL=SELECT SUM([Quantity (Base)]*[Outstanding Quantity]) AS FieldAlias FROM [Transfer Line]","FieldAlias")
  Does that help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts February 21, 2013 07:36

  Hi Hughes
  
  Thanks a lot for your answer. It do help me as I can see it works but I also understand I have to lean a lot more about SQL to proceed.
  Maybe you can give me a quick answer on how I get the filters with the formula?
  
  Traditional I would state ….."ProductID";$B65;"OnDate";"Menu!$J$2"). How will I incorporat this in my line, there now looks like:
  
  =NL(;"SQL=SELECT SUM([Quantity]*[Price]) AS FieldAlias FROM [OrderDetail]";"FieldAlias")
  
  Regards
  
  Carsten