Open Forum

Expand all | Collapse all

Aged Accounts Receivable Question

  • 1.  Aged Accounts Receivable Question

    Posted Apr 02, 2019 08:14 AM
    Edited by Kelly Smith Apr 02, 2019 08:23 AM
    Good morning,

    I am using NAV2017 and I'm wondering if there might be a better report than the Aged Accounts Receivable report to get the additional information below:

    1. Original Amount either in addition to the Balance or in place of the Balance.  We have customers that partially pay.  Ideally it would be great if I saw Original Amount and then the current balance shown under the appropriate aging bucket of 0-30, 31-60, etc.
    2. Both Invoice Date and Due Date.  Currently I can only age the receivables by one or the other so I am running two reports and copying/pasting information from one report to the other to get both. 


    Thanks,
    Kelly

    ------------------------------
    Kelly Smith
    Plastic Industries
    Nashua NH
    ------------------------------


  • 2.  RE: Aged Accounts Receivable Question

    Posted Apr 05, 2019 11:15 PM
    ​Kelly,

    Did you get this resolved?  You should be able to write a Jet Report to accomplish this.  Are you using a reporting tool?

    ------------------------------
    Cliff McDaniel
    Director of Operations
    John H. Carter Co., Inc.
    Baton Rouge LA
    ------------------------------



  • 3.  RE: Aged Accounts Receivable Question

    Posted Apr 08, 2019 08:08 AM
    Hi Cliff,

    No, I hadn't.  I do use Jet Reports, though I'm pretty inexperienced with them.  Predominantly the training that happened with them came before I started, and so I just run existing reports - I've never written any or been trained on how.  I was hoping there was something native to NAV because these things seem like they should be basic wants for an A/R Aging.  I can explore the Jet Report option and see if I can figure it out.


    Thanks,
    Kelly

    ------------------------------
    Kelly Smith
    Plastic Industries
    Nashua NH
    ------------------------------



  • 4.  RE: Aged Accounts Receivable Question

    TOP CONTRIBUTOR
    Posted Apr 09, 2019 11:18 AM
    Kelly,

    If you want to create yourself a reusable template in excel, you could use the open Customer Ledger entries exported to excel.​ You could create a column at the end to calculate the aging bucket and then run a pivot table off of that data. Next time you want to see it, just delete the lines in the data tab and paste in the new ones. The ledger entries have the document number, original amount, remaining amount, document date (posting) and due date.

    Hope this helps!

    ------------------------------
    Amanda Mayer
    New View Strategies
    Milwaukee WI
    ------------------------------



  • 5.  RE: Aged Accounts Receivable Question

    Posted Apr 15, 2019 10:10 AM

    This requires some setup (and it may already be set up at your company), but we find it to be much better than the built in report.

    Make a new query object in the object designer and compile and save it. (Query export attached as Open Customer Ledger Entries.fob.txt, just take of the .txt extension to import it, and screenshot of the query (that shows the filter on the ledger entries table to show only open entries) is Query Screen Shot.png)

    In NAV, make this query available over web services by going to Departments/Administration/IT Administration/Services/Web Services. There will already be a bunch of default objects there, and you need to add the query you just made and then make sure it's published. (Screenshot: Web Services.png). If you like, you can change the base URL and ports that are used here for this in the NAV Administration tool (Screenshot: Odata Services.png), but default NAV installations will have this already configured.

    The attached Excel workbook grabs the query results through this web services line via the OData URLs shown. Excel uses Power Query to get the information, and then we do some data transformations on it, and then report the results in the first sheet of the workbook. It won't work, right away, because you have to supply the URL for your company. In Excel, go to Data (tab) > Queries and Connections (group) > Queries and Connections (button). When you click that button, you'll get a fly-out window, which shows two queries that are used in the workbook. Double click Aged Accounts Receivable and you'll get a new Query Editor window that takes over Excel until you close it out.

    In the Query Editor, highlight the first step in the query (Labeled Source) and update the formula there, replacing the bolded text with the OData V4 URL from the NAV Web Services that you set up in the third paragraph (again, Web Services.png):

    = OData.Feed("OData V4 URL From NAV Web Services", null, [Implementation="2.0"])

    Once you do that, you can preview the results, and hitting the Close & Load button will load that query data to Sheet 1. Clicking Data (tab) > Queries and Connections (group) > Refresh (button) will go and query that URL you made, get live results from NAV, and load them back in. The slicers up top, perhaps obviously, will allow you to filter certain lines away. For instance, you might not want to go after late payments until they're, say, 15 days overdue. We've added this slicer to keep some of the noise down.

    Lastly, modifying the Aging Period (Days) value from the 30 shown here, will change the size of the "overdue buckets" that are shown.



    ------------------------------
    John Sauber
    Sauber Mfg. Co.
    ------------------------------

    Attachment(s)



  • 6.  RE: Aged Accounts Receivable Question

    TOP CONTRIBUTOR
    Posted Apr 15, 2019 10:48 AM
    John, this is awesome! I have a question, how does the query deal with partial payments that cross months? In other words, can you get aged reports that tie back to any date or is this more of a working aged receivable?

    What you shared and your instructions are fabulous!

    ------------------------------
    Kim Dallefeld
    Kim@Dallefeld.com
    Dallefeld Consulting, LLC
    Member of Dynamics Consulting Group
    Ft. Worth, TX
    2019 NAVUG Board of Advisors
    Past NAVUG Board Chairman
    NAVUG Programming Committee
    ------------------------------



  • 7.  RE: Aged Accounts Receivable Question

    Posted Apr 15, 2019 11:36 AM

    All it looks for are open customer ledger entries, and grabs just the "Remaining Amount" field, in terms of what dollars it totals up.

    We don't really deal with partial payments, so I'm not able to really get into that in any meaningful way. I'm not sure what is meant by "tie back to any date." The workbook simply calculates how many days the payment is overdue (which is tied to the Due Date field, of course), and goes from there. Were you able to get this working for your company? I think you might want to try and get it working and go from there. With partial payments, if that's information that's available in NAV somewhere, you can modify the query or the workbook to include that information and filter or slice on that.



    ------------------------------
    John Sauber
    Sauber Mfg. Co.
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role