0

Filter of Vendor Codes with Debit Balance at specific date

Tom Beynon

May 12, 2025 17:23

None

Follow

Hello,

I'm trying to create a filter of vendor codes that have a debit balance at a specific date, which can be used in an =NL("SUM") function.

I have been able to get a listing of the vendors, using a row replicator function, as follows: =NL("Rows","Vendor",,"=NL(""SUM"",""Vendor Ledger Entry"",""Amount (LCY)"",""Vendor No."",NF(,""No.""),""Posting Date"",Options!$F$2)","Number&>0").

I've then used textjoin in cell B13 Excel to create the list of vendor numbers to use in my -NL("Sum","Detailed Vendor Ledg. Entry","Amount (LCY)","Posting Date",Options!$F$2,,"Vendor No.",$B$13).

While this works, I'm after comparative positions, and therefore the sum of debit balance vendors at multiple dates, and the vendors at each of those dates will be different, so i'm trying to avoid using the row replicator/textjoin approach to replicate multiple tables of vendors at different dates.

All help greatly appreciated.

Thanks,

Tom

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  CascadiaDataworks May 19, 2025 15:01

  Why not use the Balance on Date flowfield on the Vendor table?  You can pass in the date filter and set the filter to be greater than or less than 0 depending on your need.

• 0
  
  Tom Beynon May 19, 2025 16:20

  Hello,
  
  Thanks for your suggestion, I've looked at this and it doesn't appear to give the correct balance.
  
  Below is a snip from our Vendor table filtered to 31/01/2025, Vendor No. E00143 has a zero balance and E00146 has a balance of £140.36
  
  

  The next snip is from our aged payables at the 31/01/2025 and Vendor No. E00143 has a balance of £1,771.49 and E00146 isn't on the listing, as they had a zero balance.
  
  
  If I look at detailed vendor ledger entries with a posting date filter, i get the same balances as the aged payable listing.

• 0
  
  CascadiaDataworks May 19, 2025 17:24

  Are you using Balance or Balance on Date with a DateFilter?  The flowfield of Balance on Date is just the Sum of Detailed Vendor Ledger Entry with filters for Vendor Number, the upper Limit of the Date of the Date Filter you provide and a few other things but aren't relevant to your query

  

• 0
  
  Tom Beynon May 21, 2025 11:25

  Hi,
  
  Sorry, new to Jet, and missed the Flowfield part of your original response; not come across these before!

  On the Jet Function Wizard, I've selected "Flowfield" for the What, "Vendor" for Table but within the Flowfield drop down, there is only Balance, Balance (LCY), Balance Due and Balance Due (LCY). There doesn't appear to be a Balance at Date (LCY) option.
  
  I'm using Microsoft Dynamics Business Central (cloud version), if that makes a difference?

• 0
  
  CascadiaDataworks May 21, 2025 15:00

  Might be best if we can chat in person.  Happy to give you 15 minutes of my time and walk you through this.  Reach out to bryan dot robinson at cascadiadataworks dot com if you are intersted.