0

Vendor Debit Balances at Date Nav 2009R2

Gasper Jubani

May 01, 2018 10:25

None

Follow

Hi we are still in on Nav 2009 where there is no Balance At Date field on the Vendor table. I am currently trying to generate a Vendor Debit balance report for a specific date (in the past). Using the below function (found here) gives me an error saying that FilterField is missing:

=NL("Rows","Vendor",,"DataSource=","NE","Company=",$C$13,"=NL(""Sum"",""Vendor Ledger Entry"",""Amount"",""Company="","""&$C$13&""",,""Posting Date"",""""&$C$4&"""",""Vendor No."",NF(,""No.""))","NUMBER&>0")

Would need this for several companies which actually have a shared Vendor table.

Any help would be greatly appreciated.

 

 

 

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis May 01, 2018 13:57

  Hello Gaspar -

  Your missing filter is due to this double comma...

   

• 0
  
  Gasper Jubani May 01, 2018 14:15

  Thank you Harry!

  Did correct that and then got on to create the report.

  Cell F19: =NL("Rows","Vendor","No.","DataSource=","NE","Company=",$C$13,"=NL(""Sum"",""Vendor Ledger Entry"",""Amount"",""DataSource="",""NE"",""Company="","""&$C$13&""",""Posting Date"",""""&$C$4&"""",""Vendor No."",NF(,""No.""))","NUMBER&>0")

  Cell G19: =NL("Sum","Vendor Ledger Entry","Amount","DataSource=","NE","Company=",$C$13,"Vendor No.",F19,"Posting Date",$C$4)

  When refreshing did get below error. Any ide why please?

  An unexpected error has occurred:
   COM object that has been separated from its underlying RCW cannot be used.
    System.Runtime.InteropServices.InvalidComObjectException
     at Jet.Data.Nav.NavDataSourceClient.ExecuteQuery(IQuery query)
     at Jet.JetDispatcher.a(du A_0, IJetDataSource A_1)
     at Jet.JetDispatcher.a(du A_0, Boolean A_1, Boolean A_2)
     at Jet.JetDispatcher.f(du A_0, Boolean A_1)
     at Jet.JetDispatcher.e(Object[] A_0)

• 0
  
  Harry Lewis May 01, 2018 14:55

  Hello Gasper -

  What version of Excel and the Jet Excel add-in are you using?

  Excel:

  

  

   

  Jet Excel add-in: