Thank you Jamie. Although your post did not get me the answer I needed I find the "SQL=" function worth some time experimenting more with.
Thank you Jason. Placing the link after the 1st table's parameters makes sense now. My function now works!
Date
Votes
5 comments
-
Jet Reports Historic Posts Official comment My code will be a little different as my global dimensions are not the same. You need all of your filters for the top table defined before you make the link. The filters on my GL Table below are Doc Date, Global Dim Code 1 (Department Code), GL Account No, Global Dim Code 2 (Area Code) and then Link to the second table. After you link to another table the linked table fields become your new filters. Since your first filter is "link" you are only seeing the second set of filters for the remaining options. Also the global dimension options which will have the same name that they have in NAV and not "Global Dimension 1".
=NL("sum","17 G/L Entry","17 Amount","55 Document Date","*","23 Department Code","PROD","3 G/L Account No.","67400","Area Code","30","Link=","113 Sales Invoice Line","3 Document No.","=6 Document No.","3 Document No.","108000..1081000")
Try it with the filters in a different order and let us know how it goes.
Jason -
Jet Reports Historic Posts When I am figuring something out I usually do it in SQL and then make the equivalent query in Jet. I have a simple join of two tables that I cannot seem to replicate with LINK in Jet. When I do the link I am not able to filter on fields from both tables (the fields just don't show up). My question is 2 fold.
1) Is there a way to take SQL and have it translate into a Jet function?
2) How can I translate this SQL into Jet. Something is not correct with what I have been doing:
SELECT cast(a.Amount as float) as Amount
FROM [G_L Entry] a
LEFT OUTER JOIN [Sales Invoice Line] b
ON a.[Document No_]=b.[Document No_]
WHERE a.[Posting Date] BETWEEN '8/1/2014' AND '8/31/2014'
and a.[Global Dimension 2 Code] = '621'
and a.[G_L Account No_] = '40100'
and a.[Global Dimension 1 Code] = 'TECH'
and b.No_ in ('55800CP15','55806','55820CP20','55840CP15','55840','55840CP20')
Can someone help me write this in Jet? I got this far but then start running into trouble being able to select the filterable fields I need such as Global Dimension 2 Code:
=NL("sum","G/L Entry","Amount","Link=","Sales Invoice Line","Document No.","=Document No.","Posting Date","10/1/2014..10/31/2014") -
Jet Reports Historic Posts mcauthron,
Are you familiar with the Jet "SQL=" function? If you're doing a lot of SQL work, you might find it quite useful.
Here's a KB link:
http://kb.jetreports.com/article/AA-00567 -
Jet Reports Historic Posts SELECT cast(a.Amount as float) as Amount
FROM [G_L Entry] a
LEFT OUTER JOIN [Sales Invoice Line] b
ON a.[Document No_]=b.[Document No_]
WHERE a.[Posting Date] BETWEEN '8/1/2014' AND '8/31/2014'
…
and b.No_ in ('55800CP15','55806','55820CP20','55840CP15','55840','55840CP20')
McAuthron,
In Jet the idea of a LEFT OUTER JOIN doesn't really exist. The closest you have to that is an InclusiveLink= which you'd use exclusively in the NL("table") functions.
ex. if you're using NL("table") to G/L Entry and you build an InclusiveLink= on Source No. to get Customer Name, but you'll still include records where Source Type is Customer OR Vendor (or any other source type).
If I'm reading your SQL code correctly, the LEFT JOIN is superfluous and all references including filters to table B can be ignored because in your SELECT statement you only show fields from Table A.
Therefore the LINK= in Jet Essentials is unnecessary because LINK= more closely approximates an INNER JOIN.
If you intended to filter the list of Amounts returned by Item No.'s in the Sales Invoice Line, you'd need an INNER JOIN in your SQL code and the Link= would be the appropriate method of getting this done. -
Jet Reports Historic Posts jaew@OR,
The requirement of including of the word "OUTER" varies based on what type of server you are on and how it interprets joins. Yes, in MS SQL "OUTER" is implied so you don't have to put it in although it is a good habit to get into. It helps those without a lot of SQL experience follow what you are doing and makes your code usable on more than one paltform. Some other types of servers are more picky about specifying the exact type of join.
In this case the the LEFT JOIN/LEFT OUTER JOIN is not superfluous. The field No_ does not exist in table A so you have to join to table B in order to be able to filter on the field.
You do seem to have a valid point about the LINK= being more of an INNER JOIN, however. Thank you for your input.
Please sign in to leave a comment.