The following formula will return a sorted list of customers which have Sales Invoice Lines AND Sales Credit Memo Lines within a given date range=NL("rows","customer",{"no.","name"},"+Name","*","link=customer","sales invoice line","shortcut dimension 2 code","=no.","posting date",$F$6, "link=customer","sales cr.memo line","shortcut dimension 2 code","=no.","posting date",$F$6,)
Is there any way to get a list of customers which have Sales Invoice Lines OR Sales Credit Memo Lines within a given date range?
Thanks
Joe Kelly
Date
Votes
7 comments
-
Jet Reports Historic Posts Hi Joe Kelly,
Yes you can do this with NP(Union) which will get the union of 2 lists. So basically you would do something like this:=NL("Rows",NP("Union",NL("Filter","customer","no.","link=customer","sales invoice line","shortcut dimension 2 code","=no.","posting date",$F$6),NL("Filter","customer","No.","link=customer","sales cr.memo line","shortcut dimension 2 code","=no.","posting date",$F$6)))
There are some caveats to this solution. You can only return 1 field from NL(Filter) so you'll have to use an NL(First) to get the name. Also, you can't sort the result of NL(Filter), so the results will be sorted by the customer numbers not the names. Otherwise, it should give you what you are looking for.
Regards,
Hughes -
Jet Reports Historic Posts I am trying to do something similar, but I want the sum of a field that matches certain criteria from three different tables. (Item Ledger Entry, G/L Entry, Service Item)
Basically, I want to generate a report that gives me a total list of units sold by class that ties back to our G/L. From what I have gathered. My Item Ledger Entry table is tied to my Service Item Table by the field "Serial No." The G/L ties to the Service Item table by the field "Source No."
How can I create a union that sums up the result of these filters?
V/R,
Gustavo -
Jet Reports Historic Posts Hi Gustavo,
So do the entries in the 3 tables actually overlap? The way to do this if they don't overlap (or if you can create simple filters which exclude the entries from the other tables) is just to use 3 NL(Sum) formulas and add them together in Excel. If the entries overlap in some way, how exactly do they overlap? How you write the formulas would depend on if/how they overlapped.
Regards,
Hughes -
Jet Reports Historic Posts Well, turns out I need to do some more investigating to see how exactly I can tie these tables together. I thought I could use "Source No." to tie the G/L entry table to the Item Ledger Entry table, but thats not the case.
back to the drawing board… -
Jet Reports Historic Posts Ok, so its been a while but I'm back to this issue.
I am trying to do the following:
I need to grab all of the transactions for a certain date range (03/01/12..03/31/12) and G/L account (512300) using the "G/L Entry" table. However, I need to include several extra fields for each of these transactions that is not found in the "G/L Entry" table. They are in the "Service Ledger Entry" table. Both tables are tied by the "Document No." field, but that field is not a unique field and there may be multiple transactions in both tables that have the same document number. The unique field in the "service ledger Entry" table is the "Entry No." field.
I have tried to link these tables several different ways, and I can't get the sum of the transactions to tie back to what the g/l account shows in our COA.
How do I link two different tables together so that I can include fields from both while doing an nl(rows) in my report? -
Jet Reports Historic Posts So let me try to understand what you are saying here. You are going to replicate transactions from the G/L Entry table using NL(Rows). Then for each transaction in the G/L Entry you want to get something from the Service Ledger Entry table. Are you trying to get multiple records from the Service Ledger Entry for each G/L Entry record? Or are you trying to get a single record from each Service Ledger Entry which corresponds to each G/L Entry record?
It sounds from your description like Document No. isn't a sufficient linking field for the 2 tables since you say there are multiple G/L Entries and multiple Service Ledger Entries for each document number. Often Document No. and Document Type are used together as linking fields in NAV (such as with Sales Header/Sales Line) so you might try that. I'm assuming you know how in Jet to link the 2 tables, but you're just not sure what links the two NAV tables together, is that right? If not, I can help with the Jet side. I'm less good with the NAV side here.
Regards,
Hughes -
Jet Reports Historic Posts Well, I dont really understand how linking works and how to best use it. I am scheduling a jet training to learn but it wont be till end of month. Would you mind giving a quick skinny on how it works and when best to use it?
I do know how to do it but I dont know why I'm doing it this way or what is actually happening…
V/R,
Please sign in to leave a comment.