External Script to Create Cash Receipts Batch

It's me again.

Looking into using VBA or potentially a vb.net windows form app to potentially create and update Cash Receipt Batches & Deposits. External scripting documentation seems to be pretty limited and I am not sure where to even start when it comes external Sage automation.

If it's even possible, I'd prefer to use (VB's, not Sage's) GetObject() to get the active instance of a running Sage 100 workstation application and use it's active connection to perform these tasks... If not, then can someone explain how to create the scripting object and sign into Sage?

Once I have an active/logged-in session, is it business as usual for setting the _bus object handle [i.e. oSession.AsObject(oSession.GetObject( ... ))] and using .SetKey() and .SetValue() to insert/update records? 

And are there any special considerations I should be made aware of when using external scripts that differ from something like a button script? i.e. do I need to log off the session before destroying it? Will logging off also log the user out of a true Sage instance they were running (if not using GetObject from earlier)? etc

Appreciate the assistance.

  • 0

    So after some consideration I figured I would just create a vb.NET Windows Form App. This would provide the user with a UI and would store their credentials within the compiled application instead of a raw text script file. I found this post that gave me a major head-start on how to start up a Sage Script object and get logged in.

    I essentially wrapped all Sage's objects into their own classes to make building this form application easier later on if/when needed in the future.

    I know vb.NET isn't well discussed here so I will try to visualize the current flow of my application the best I can... As I still need some guidance with actually creating new Cash Receipt Batches and Deposits, but hopefully I can explain what's going on. Essentially it's not much more than what has been done in the original post.

    Dim SageApp As New SageApplication

    `SageApplication` is basically a custom wrapper for the ProvideX.Script library. When initialized, it will automatically perform the .Init() method and initialize the Session object (which also has been wrapped into a new class, which will be shown next).

    From here, calling we can call SageApp.Session which will return this wrapped version of Sage's session object:

    This class is the wrapped version of the private variable `oSession` ( which derived from oSession = oApp.NewObject("SY_Session") ).

    During it's initialization, it initializes and stores the UI object and then calls the `SetApplicationProperties` subroutine, which is pulling stored values from the application's settings (which would be filled in by the user from textboxes on the compiled application). This logs in the user, sets the company code, module code, and module date.

    I hope this made some sense. So in a nutshell, we are logged in and module has been set... I guess I was imagining using a _bus object, but David's script is using a _ui object. Is it still going to be possible to create a new Cash Receipt Batch? And is it going to be possible to add the deposit details from where I left off? Am I stuck with only using _ui objects and therefore can't use the _bus' .SetValue() methods?

  • 0

    I'm curious... why?  What benefits do you hope for, instead of just using the proper Sage 100 workstation software?

  • 0 in reply to Kevin M

    We have a vendor that accepts payments in an online portal. They drop a daily csv file to us via sFTP. Sure it would have been nice to just schedule automated VI imports, but it can't ever be that easy...

    First, the idea of raw VI-importing is dropped cold before we can even get started because we use batches. Management doesn't like using anything other than the 'Next' button to assign batch numbers and deposit numbers, which VI can't support because it can't pass the auto-generated batch/deposit numbers from CashReceiptDeposit over to CashReceiptHeader even though it can generate them.

    Second, we need a way to quickly locate payment exceptions and an easy way to correct them. The summary view this program could provide would help make that process run smoother. The deposits would be imported with the corrections made first rather than fumbling around in different deposits using the Data Entry UI that have to be viewed separately from one another and with very limited information in them.

    Third, these payments need to be grouped together in a way they would hit our bank. There may be 2 or 3 different payments containing a number of different invoice payments each day so there needs to be 2 or 3 deposits created based on some preset rules. These rules could be applied before importing.

    Even if the end still resulted in this exporting a clean CSV file, it still was able to handle the above tasks first. The file would now include assigned batch and deposit numbers and it would have a static file name that VI would know where to look. It would allow for visual verification and confirmation of the deposit from a summary view perspective before importing the file.

    If you really want to know the details, it usually involves a management meeting on process improvement ideas where everyone in that meeting starts sharing their programming ideas but doesn't know what "Hello, World!" refers to. I come in after the fact when it's time to implement them.

    Non-portal deposits are to still be manually entered from Sage. This was intended to automate the majority of the vendor's payment file.

  • 0 in reply to SoonerFan21

    OK, makes sense.

    For importing cash receipts, I usually run the input data through a scripted process (VBScript... or import raw data to a UDT then trigger a Visual Cut report based on the UDT data...) to shape the batches and assign the deposit #'s, then output a single CSV for VI automation.