0

Returning last matching entry in Record Link Table - BinaryText?

Colin Hynd

March 21, 2018 14:43

Answered

Follow

Is it possible to return the LAST matching entry from the Record Link table using the binary text command similar to =NL("BinaryText","Record Link","Note","Record ID",V7)

I'm trying to return 'notes' that have been added to Posted Sales Invoices but if there is more that one note, the above formula only picks up the 1st note when I could do with the last note being picked up.

My report is a credit control report that pulls all the outstanding invoices with a 'remaining amount' of not zero and that is marked as 'on hold' this then uses the reference of the invoice to match against the entries in the Record Link Table and populate the linked note.

Another option that I could use would be to generate a new duplicate line for all notes but as my report is based on the Cust. Ledger Entry table, I don't know if this is possible.

It is all not helped by the fact I can't edit the notes in my version of Nav16, I can only create new notes and delete old ones.

Can anyone help?

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Tony Phan April 13, 2018 16:50

  I just ran into this issue myself, hard to believe there isn't a better solution but the workaround that I found, albeit a bit inefficient, is to use a hidden column to first do an NL with a Last to pull the Link ID, then use that as the reference for your BinaryText instead. The Link ID is a primary key on the Record Link table so it's unique to that particular record.

  =NL("Last","Record Link","Link ID","Record ID","Sales Invoice Header: "&$H12,"Type","Note")

  =NL("BinaryText","Record Link","Note","Link ID",IF($C12 = "", 0, $C12))

• 0
  
  Colin Hynd April 16, 2018 07:42

  Thanks Tony - works perfectly!