I'm trying to create a Table with customer ledger entry and detailed customer ledger entry fields to show outstanding AR as of a certain date. I am calculating the amounts based on posting dates, so the for example the "Balance Due" field in the table does a Linksum on the Amount ($) field from the Detailed Cust. Ledger Entry table filtered up to the posting date range in the table filters.
If I also filter the table to only look at Open = TRUE in the customer ledger, then the resulting table excludes documents that have cleared AFTER the posting date (because they are now closed). If I take that Open = TRUE filter off, the report shows all documents, even those with zero balance as of the posting date. Over time those zero value ones will add up and the report will not be very useful.
Is there a way that I can have the table show only those documents with a >0 balance outstanding as of the posting date range? I can't figure out a way to filter on this as it is a flowfield.
Any help would be appreciated.
Date
Votes
3 comments
-
Jet Reports Historic Posts This "Filter a Table based on a Flowfield" in Jet Reports is good question quite similar with which I am also struggling. In a NAV report, to "filter" on a FlowField based on another field in the record the following code can be used in the OnAfterGetRecord trigger code:
IF "A Normal Class field" (some operator) "A FlowField Class field" THEN
CurrReport.SKIP {Avoids unwanted records.}
ELSE
(stuff happens);
In Jet Reports/MS Excel, the only solution I have thought of is to create a secondary worksheet with conditional (based on the balance outstanding value criteria) VLOOKUP to regurgitate only the desired records. -
Jet Reports Historic Posts Jet Reports has a number of pre-built, sample reports available for download. One of them is NAV018 - Accounts Receivable with Details which may be just what you are looking for. It may at least give you some ideas about what you can do with flowfields. I've attached this specific one for your convenience, but you can go to the following link to download all current reports:
http://www2.jetreports.com/l/3692/2012-02-16/qt2q8
The saved Table Builder xml file(s) can also be found at:
http://kb.jetreports.com/article/AA-01200
Regards,
KRD -
Jet Reports Historic Posts Hello
Could any one help to filter only the customers with entries in the aging bucket + 90 days and above , I do not want to bring in all the customers with balance , instead only customers with entries outstanding say over and above 90 days.
Please suggest how to filter the same.
Best regards
Ravi Chandra - Hong Kong
Please sign in to leave a comment.