I am trying to generate a report showing me brand new customers in the Opportunities table.
This is what I am working on.
Cell K10 show me all the accounts in the Opportunities
Cell L10 show me all the accounts in the Opportunities in the specific date range
Cell M10 show me all the brand new customers I have, based off posted invoices.
What I need is “if they are a brand new customer, and they exist in the opportunity table in the date range, then show up on the report”
The issue is the Opportunity table, because there is no customer No. field in here, only a contact no. field.
to get the customer to contact relation, the contact business relation table is used.
But the main thing is that i need, “if they are a brand new customer, and they exist in the opportunity table in the date range, then show up on the report”
Take a look at the attached spreadsheet.
NAV 5.0 SP1
Jet 9.1
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi cveale,
I believe you should be able to get the list of customers you want with a formula like this:=NL("Rows","Customer",,"Link=","Contact Business Relation","No.","=No.","Link=","Opportunity","Contact No.","=Contact No.")
This gives you a list of customers where there is a record in the Contact Business Relation table where the No. in that table equals the number in the Customer table and there is a record in the Opportunity table where the Contact No. on the Opportunity table equals the Contact No. on the Contact Business Relation table. Then you should only be getting Customers with a corresponding entry in the Opportunity table. Does that make sense?
Regards,
Hughes -
Jet Reports Historic Posts This may just work perfect, I will test it out.
The only issue is that I only want new customers to show up, based on a date range.
If a new customer buys in the first date range, then I don't want him to show up in the second date range, but he is not a new customer anymore. -
Jet Reports Historic Posts Hi cveale,
Okay I see your point. You'll need another link to only get customers where they have an entry in the Sales Invoice Header table within a certain date range. You can definitely do that but I think you'll end up running out of filters with a single NL function so you'll have to use the NL(Link) function which is exactly the same as a Link=, just defined in a separate function like this:
E10:=NL("Link","Sales Invoice Header",,"Sell-To Customer No.","=No.","Salesperson Code",$B$3,"Posting Date",NP("Datefilter",$H10,$I10))
F10:=NL("Link","Contact Business Relation",,"No.","=No.","Link=","Opportunity","Contact No.","=Contact No.","Creation Date",$J10,"Campaign No.","CCI","Salesperson Code",$B$3,"Closed","False")
M10:=NL("Count","Customer",,"Link=",E10,"Link=Customer",F10)
With this approach you are defining 2 separate links from the Customer table. One link goes to the Sales Invoice Header so you only see Customers that have sales within a certain posting date range. The other link goes to the Opportunity table via the Contact Business Relation table so that you get Customers coming from a certain campaign, salesperson, etc.
Notice that in the 3rd formula, the 2nd Link= is "Link=Customer" which tells Jet to start a new link from the Customer table rather than continuing from the Sales Invoice Header table which is the end of the first link. Does this work for you?
Regards,
Hughes -
Jet Reports Historic Posts After thinking about your problem a little more, I think you may still have to use the NP(Difference) in order to get the Customers with sales in one week who do not have sales in previous weeks. You may still be able to use the Link= but you will probably still have to use an NP(Difference) to accomplish the task.
Regards,
Hughes
Please sign in to leave a comment.