Need help with a script invoked on the button click to validate the customer's open invoices

SOLVED

Hi friends, 

We have a business requirement to have a vb script that would pop up a message box on the sales order entry as soon as we tab out of customer no field or set as a user-invoked button. 

If the customer has any open invoices that are past due?  The logic should do a table-level query to AR_OpenInvoice where screen.customerno = table.customerno and table.balance > 0 and table.invoiceduedate < currentdate.

In case anyone has come across a similar requirement or solution, could you please share your findings. Also if there is alterate solution approach, please recommend and advise.

Thanks

AJ

  • 0

    What's wrong with the Sage credit limit checking feature (which has a few options, described in the help files)?

  • 0 in reply to Kevin M

    The business wants to have a system provision where the efficiency of the team is controlled as well. That's why exploring a solution where everyone in the team(growing) pays more attention to the default processing of the sage functionalities.

  • 0 in reply to AJ@Marki

    A custom pop-up doesn't make people pay more attention.  Something new becomes old eventually.

    Restrict the number of users who can override a customer credit limit, and SO's can then only be created on Hold without that permission.  It's hard to ignore orders on Hold (which cannot be printed / shipped).

    Sure, you can script a lookup of open invoices, and present a list to the user (or click into the customer record under certain conditions) but what does that achieve that is not possible using the regular program?

  • 0 in reply to Kevin M

    Hi Kevin,

    How do we use similar logic as the Open Invoices lookup from the Credit button... I have not referred to the help files earlier for the seeded vanilla functionality. 

    Thanks

    AJ

  • +1 in reply to AJ@Marki
    verified answer

    AJ,

    Dealing with credit checks / open invoices is not a unique function, and the default Sage program can do that without any kind of customization or scripting.

    AR Options control whether there is an automated credit check for customers (for a warning when customers are over their limit / overdue).

    The exact settings to use are for you to customize for your business goals.

    Clicking on the Customer No link opens Customer Maintenance, with full functionality and complete details on open invoices.

    What you ask is fairly simple for someone who knows scripting in Sage 100 (pop-up a text list of open invoice details), but I wonder if it is worth the effort considering the Sage default interface is much more feature rich and requires no script.

  • 0 in reply to Kevin M

    Hi Kevin,

    I totally agree with the approach/suggestion, I am fairly new to Sage scripting so it doesn't harm learning and trying the requirements as such. 

    I am trying to capture this as a button event which evokes the validation for the particular Customer no and then fetches if there are any open invoices with current balance > 0. 

    Not sure if it works like running a SQL statement on the base table or not ?

    Thanks

    AJ

  • 0 in reply to AJ@Marki

    Scripts in Sage 100 come in three varieties:

    • Business Object events (Pre-Write, Column post validate...).
    • UI events (panel post load...)
    • Button scripts

    What you can do in a script is only limited by the Sage 100 BOI (Business Object Interface) and what is possible in VBScript.  (Of course an ODBC query is one potential approach, but is not necessarily the best strategy).  For a beginner list of functions in BOI scripting, search the KB for article 43033.

    Edit:

    As David posted elsewhere, I'm reminded that Sage U has a course on BOI where you can also learn the basics of scripting.  I have not taken that course, so I often forget to mention it.