Smartlist – Customer Statement past due invoices

  • Smartlist – Customer Statement past due invoices

    Posted by Christine Polifka on January 17, 2024 at 3:27 pm

    Good Day everyone,

    Has anyone created a customer AR showing all past due invoices and amounts using a smartlist to export into Excel?

    Thank you

    Christine Polifka replied 3 months, 1 week ago 4 Members · 6 Replies
  • 6 Replies
  • Pamela Stephenson

    Member
    January 17, 2024 at 5:01 pm
    Up
    0
    Down
    ::

    Christine,

    I have used smartlist to generate an AR balance. I have it search for transactions that don’t have a zero balance on the current trx amount field. I am sure you can add some date field to the search criteria to for example, get items older than the prior month. You just have to know that returns, credit memos and payments will show as positive figure in Excel, and adjust for that. (Or include only invoices).

    Hope that helps.

    • Christine Polifka

      Member
      January 18, 2024 at 12:33 pm
      Up
      0
      Down
      ::

      Pamela,

      Thank you so much for your response and providing direction on this.

      As I am a visual learner, I had found Receivables Transactions by Customer that was created by our company.

      would this work? See Column display Name, example (attached)

      For the credit memos, what do these represent. As our company creates returns to the customers. Not sure what you mean by credits. Is this to write off amounts from AR Statement?

      Thank you,

      Christine

  • Jo deRuiter

    Member
    January 19, 2024 at 9:43 am
    Up
    0
    Down
    ::

    Hi Christine!

    In SmartList, the Receivables Transactions SmartList (SL) is what you want to run. To pull currently open (due) receivables open the Receivables Transaction SmartList list by clicking on the expansion + button. Here you see a list of previously built reports – the asterisk * is a default report provided by Microsoft. Any of them with an Asterisk were built by Microsoft – and they are okay, but for adequate reporting you’ll likely want to add columns.

    For the SL you are wanting, in the command bar click COLUMNS and then ADD and highlight and choose any of the aging buckets – i.e. 31-60 days, etc to see the clients balance in that bucket – otherwise you want to pull in the following columns:

    Document Amount – the original invoice or credit memo amount

    Current Transaction Amount – the amount left owed on the document

    Due Date –

    Void Status

    Posted

    At least these columns – but choose any others you want to see – you are NOT altering this for anyone else – only you can see it.

    Now, in the command bar, choose ‘Search’ and in the first search criteria choose the column “Current Transaction Amount” and in the filtering command choose ‘Is Not Equal To’ and enter zero (0) – this will filter out all transactions that have been fully paid.

    In Search, also choose to search on the column Void Status and choose either ‘is equal to’ ‘Normal’ and the next search field, choose the column Posted and filter that to ‘is not equal to’ ‘No’

    You are now left with a list of exactly what is left due in Receivables – but importantly this is a snap of what is due RIGHT NOW – not yesterday or next week, for that you must run the Historical Aged Trial Balance report in the Sales Module.

    Good luck and let us know if you have any other questions!

  • Christine Polifka

    Member
    January 22, 2024 at 10:57 am
    Up
    0
    Down
    ::

    @ Jo de Ruiter and @Pamela Stephenson,

    I appreciate your help with this. A lot to learn in GP for AR is a new role I am in.

    • Beat Bucher

      Member
      February 7, 2024 at 10:33 pm
      Up
      0
      Down
      ::

      Christine,

      Since you’re quite new to this position (and likely GP), you may not know yet the full extend of the community power and all the shared support..

      Have a look at this blog site from a respected GP expert that’s been around the community for 20+ years: Victoria Yudin | Receivables SQL Code

      This site is a treasure chest for GP users (and fans), and if you have someone that can help you with creating SQL views in your system, then you’ll be in heaven with her scripts. You can use those scripts both in SmartList Designer or Builder (eOne), or even use them directly in Excel refreshable workbooks.

      Good luck!

  • Christine Polifka

    Member
    February 20, 2024 at 3:14 pm
    Up
    0
    Down
    ::

    It is great to have additional resources with GP Expertise.

    Thank you for sharing your knowledge and recommendations.

    It is fantastic to have treasure chest for GP users!! I will bookmark this for future.

Log in to reply.

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!