0

filter by salesperson on a posted sales credit?

Devora Locke

November 05, 2018 18:33

None

Follow

I'm designed an orders report for our salespeople, which works well except I need to be able to apply a filter for salesperson. Easy enough to grab this from the sales invoice line table, but my problem is that in posted sales cr memos, this does not exist.  Without it what I get is every credit for the time range specified instead of only the credits for that sales person's customers. 

I thought that the lines in posted sales cr memo were pulled in from the sales line tables when the credit is created, and salesperson is a listed field in that table per Jet.  But it blows up.  Thought maybe I could tie it back to the original invoice number instead, but in our ERP system the invoice # is in a description field!!  That's right,.. sigh,.. And yet the credit memo pulls the line item from sales invoice line, which will populate a salesperson code when your report on it for an invoice. 

Any ideas, folks? Is there a better table to use to report on credits to a sales  account? 

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Heather Rowe November 05, 2018 18:50

  What database are you using?  that might help to narrow down the search a bit.

  If we can't find anyone else using the same system, we may have to go at this a different way - such as examining how the Invoice# appears in the description field of your posted sales line. 

• 0
  
  Devora Locke November 05, 2018 19:01

  Dynamics NAV 2016 on SQL 2014

• 0
  
  Heather Rowe November 05, 2018 19:14

  Devora - I am also using Dynamics NAV 2016 - but ours does have some customization, so probably there are differences.

  Have you looked at the header table for the Credit Memo?

  In my system, the source table is SALES CR.MEMO HEADER - I have salesperson code in that table, and can then use that to pull in the lines I need from the credit memo lines table. 

  If I'm reading your issue incorrectly, and you actually need to tie the invoice back to the credit memo, I see your issue with the Posting Description field which (again in my system) is showing a description such as "Original invoice PINV003755".

  To get around this, I can use =NL(,"SALES CR.MEMO HEADER","NO.","POSTING DESCRIPTION","*"&G6&"*") - where G6 holds my invoice number PINV003755, the added asterisks before and after indicate that any characters can precede or follow the invoice number.  My formula returns the credit memo number, but it could be modified to pull lines if needed.

• 0
  
  Johannes Herlitzius November 06, 2018 08:30

  Hello Devora,

  I am as well using NAV 2016. You can use the field "Salesperson Code" from the Sales Cr.Memo Header, as Heather already pointed out. This is a standard field and should be available in your version as well.

  If you do not want to use Posting Description from Heather, you can try the following:

  In the Sales Cr.Memo Header there are these fields:
  - "Applies-to Doc. No" (which also includes document numbers from the sales invoice line)
  - "Applies to Doc. Type" (Applies to Doc. Type = 2 => "Invoice")

  So if you filter the field "Applies-to Doc. No" by "Applies to Doc. Type" = 2, you only get Document No from Invoices. Joining this field to the Sales Cr. Memo Line you have your link to the sales invoice line via "Applies to Doc. No from Sales Cr Memo Header" = "Document No" (from Sales Invoice Line).

  I hope this is not as confusing as it looks like ;) If you are interested and need help, I can try to clarify stuff and support my approach with some picutres.

  Let me know, if this helps.
  Best Regards,
  Johannes