Home Blog Automating Processes in Acumatica with PowerShell – Part 2

Automating Processes in Acumatica with PowerShell – Part 2

Robert Waite | November 16, 2020

Editors Note: In Part One, Robert covered setting up & configuring a payment plug-in, identified the key steps to automating the payment process using Postman, and finally he used a cURL script to generate an invoice using the Postman request to create an invoice. In this post, he wraps up all these requests into a continuous PowerShell script and discusses the nuances of working with the PowerShell Invoke-RestMethod Cmdlet.

Pulling the Postman Requests into a PowerShell Script

The exercise we did in Postman now identifies all of the requests needed to automate the generation of an invoice up to capturing a payment. Now we want to get these requests into a continuous end-to-end script using PowerShell This can be done using PowerShell or one of many other options that will allow you to engage REST services. Postman gives you several super easy options. The one we will explore is PowerShell. Let’s start by converting the Login request into a PowersShell script. Simply click the code button on the request as shown below.

When the Generate Code Snippets window opens select “PowerShell – RestMethod” as the output. Note that the same objective can be done with PowerShell or any of the other languages you are familiar with in this list.

Grab the snippets for the Get Invoice request as well as the LogOut request. The goal of this stage is to get a Script where we 1) login, 2) get and display the result of an invoice in JSON form, then 3) immediately log out. Working in PowerShell is a little less forgiving than when working in Postman where Postman shares the session across all of your requests. If we run our scripts without logging out within the same script, we will quickly consume all the available logins that are allowed (which is only 2 for the SalesDemo data instance). We also need to add a method of passing a Web Session object across the Invoke-RestMethod calls we use. We do this by creating an instance of a WebRequestSession object in the Microsoft.PowerShell.Commands namespace. Any variable in PowerShell will start with a dollar sign character ($). We will load it into a variable we will name $WebSession. On each of the Invoke-RestMethod calls we will pass this into the -WebSession parameter. When I copied the Get Invoice script from the Postman generate code snippit tool, I also removed the -Body parameter to get this script to work. Remove any unneeded and redundant code from merging the scripts together to get something like this:

GIST: https://gist.github.com/RobertWaiteREPAY/060e88e28fc5c349ce2a515ec248b872

This script includes Magic strings that need to be extracted and set into variables. We will use $AcumaticaUser, $AcumaticaPassword, $AcumaticaTenant, and $AcumaticaEndPoint. Then we will use a temporary variable of $ReferenceNbr for the invoice number we will look up. Make sure this is set to something that is currently in your system. I have also pulled out the URI data into its own $Uri variable. We have also added String formatting. All we need to do is drop in the variable into a string. When we used Postman to generate a snippet function, it returned a few strings with `n within the content. The ` grave character is PowerShell’s escape character. When the grave is used, the character after it is escaped giving it additional meaning. For our Get Invoice request, we have a $ char that is needed, however the $ char is reserved for variables. If we intend to use it within a string, we can precede the $ with a grave as we have in the below script or use a script that is declared with single chars as is the case with the version above. However, you will not be able to format the string as we need to with the version below. Hence the change to double quotes. Another item we can escape to get a double quote into a string is to use the `”. Using “” also works. 

Here is the changed code:

GIST: https://gist.github.com/RobertWaiteREPAY/a94504240e05795a5c2b2571bb5ca976

Next, we need to create an invoice and immediately release it. To do this, we will call our Create Invoice request and receive the entity as a JSON string. Then we will extract the Entity ID and the ReferenceNbr out of that JSON string for future steps. To do that, we will pipe the result to the ConvertTo-Json cmdlet and store it to a variable. Piping means that we will take the results of the previous cmdlet and pass it to the next cmdlet. Piping is done with the vertical bar character |.  From there, getting at the values is rather easy as you can use PowerShell intelli-sence or tab completion to explore the convenient object returned from the ConvertTo-Json cmdlet. Within this section, we added the @” and “@ tags to declare what’s called a “here string” – a string to be declared across multiple lines, which is handy when dealing with larger JSON strings. Keep in mind you need the closing tag anchored to the far left when using a here string.  There are other ways to prepare strings as JSON, this is one of them. 

Another thing we introduce in this section is splatting. With splatting, you load your parameters into a hash table and pass that hash table as a single parameter to a cmdlet, using a @ char instead of $ to indicate we are splatting the parameters. I prefer doing it this way as it improves code readability by eliminating vertical scrolling, preventing too much information being on a single line of code. When you have a single idea on a line of code, you can add inline commentary to help future readers (which may be yourself) understand your code.  Now we run the Invoke-RestMethod then pipe the response and get a clean PSObject we can query into. We pipe the response to the convert to JSON cmdlet first to convert it to a clean JSON string. Then we immediately pipe the value to the ConvertFrom-Json to create a convenient PSOject to get our values. 

GIST: https://gist.github.com/RobertWaiteREPAY/56675a6739db1e854f602a22a3d84734

Next, we release the invoice and poll for the Open Status to confirm the invoice is released. The release action item is invoked within a long-running process as indicative of a timer icon on the invoice as observed in the Acumatica UI. Therefore, this action is asynchronous, which means you will receive a response from the Invoke-RestMethod call before this release is finished. We can determine that the invoice is indeed finished with the release process by polling the invoice and confirming the Status changes to Open. Another option is to find the location element in the response, which will give you a string that you can parse into a URI. This comes in handy if an error is occurring. Doing a GET on that link will yield further information on the error. 

I find it easier to do this type of investigation using the location URI suffix within Postman as opposed to PowerShell. So we will poll the status flag instead. We will do so using the code we have for getting the invoice detail. We will establish a Do While loop and query the invoice within its code block. We will only loop again if the value of the status is not equal to “Open” using the -ne (not equal) flag which is equivalent to != or <> in C#. If you know the codes, you can use within the OData $Filter parameter. This will be familiar to you as they use the same codes. In order to prevent an infinite loop, we implement a counter mechanism that will throw an error if too many loops have transpired. Note the comparison operator used to determine if too many loops uses -eq (equal). This is equivalent to == in C# and is very similar to OData $Filter syntax you might be used to using. To raise an error, you simply use the Throw cmdlet and pass in a string for the message body of the error. One caveat to consider is that as it is now the error will not allow for the script to log out. The best way to handle this is to wrap the log out request into a function. Then you simply call that function just prior to throwing the error. You could just copy the request and paste it, but that would violate a best practice known as the “DRY Principle” or Do Not Repeat yourself. We will circle back to this later.

GIST: https://gist.github.com/RobertWaiteREPAY/74512db68be8c0658862f2ae7c202c22

Next, we will finish off the script and have an end-to-end script that accomplishes what we need it to do.

We are not introducing any new topics at this point, so I will just present the code need. When we run the script, we now invoke an end-to-end process that creates an invoice, then immediately collects payment for that invoice by creating a payment and application button and executes the Capture CC action for that new payment record.

GIST: https://gist.github.com/RobertWaiteREPAY/43d2be53154032a90d206dd27dbb74d8

In the next step, we are going to clean up a little by encapsulating the parts of this script into distinct functions. You can create functions with the Function keyword with open and closing curly braces surrounding your function script. Once we have that in place, we will create the final Foreach loop that will iterate through records of a CSV file and automate payment for each of the records. PowerShell function names follow a Verb-Noun nomenclature using the verb to connote the action and the noun to describe what object the action will act on. We will create the following functions LogIn-Acumatica, LogOut-Acumatica, Create-Invoice, Check-Invoice, and Pay-Invoice. We will also introduce the concept of switch parameters, where we will set up a flag that will auto release the invoice being created. Once we have our Logout-Acumatica function we will also call this prior to throwing the counter error when we check for the invoice being released. To set parameters you use a param() block. For the WebSession variable we want to make sure it is passed, so we decorate that parameter with the [Parameter(Mandatory=$true)] attribute. Note that the Mandatory argument is set to the global $true constant. 

This is our new Login-Acumatica function

GIST: https://gist.github.com/RobertWaiteREPAY/dd6e1382b9c2908ea40ed0e9d528924f

This is our new Logout-Acumatica function 

GIST: https://gist.github.com/RobertWaiteREPAY/34fae3707b3bb841b6991e1573bbb839

We have a few places where we need to query the Invoice information so we will create this function to do that job. Once we get and convert the result to JSON then a PSObject we use the Return keyword to pass the result back to the caller. You don’t have to specify “return” in PowerShell as it is optional.  Simply expressing the value as if you were printing to the output window will achieve the same result.

GIST: https://gist.github.com/RobertWaiteREPAY/5085f1b815a8ff07170d85f56e312f6c

This function will create the invoice and we will have a flag to automatically release the invoice. We can do this with the [Switch] attribute. When you are using a switch your calling code sets this to true by setting the flag with a dash then parameter name. if this flag is not specified then the variable value will be false. We will also make the body parameter mandatory as we will need to have variable body data for each of this invoice being created. 

GIST: https://gist.github.com/RobertWaiteREPAY/58835830b6ef68b803f929d10363ed46

The last function we will create is the Pay-Invoice function. We will query for the invoice detail and then use that detail to build the payment detail once the payment is made, and return it as a Generic PowerShell Object (or PSObject). The function is as follows. 

GIST: https://gist.github.com/RobertWaiteREPAY/307e0a6d309e6622355abf553ae80d54

Now the final part of getting this script to where we need it, is to open a CSV file and iterate through it as to create an invoice and payment for each of the records. We will also add a preventative measure as well that will lookup and check if an invoice has a description that has a DocDesc of a certain value. If one is detected, we will assume the payment was already captured preventing a duplicate payment. For this, we will create a Check-Invoice function that will return a bool value if a matching invoice is found. 

GIST: https://gist.github.com/RobertWaiteREPAY/d0d2bc5697328a1c7da063797482e325

Now our main script can be simplified, making it more readable. We will load the CSV records into PS Objects using the Import-CSV cmdlet. From there, we will iterate through each of the CSV records, load the info into variables, and run each of our corresponding payments. We load our Check-Invoice and invert the result with the -not operator. If no information is found that indicates an invoice already exists, we will only then create the invoice and payment with our new functions.

GIST: https://gist.github.com/RobertWaiteREPAY/2bb88b95b527f8a8b87bd6db0b66f5ba


There are plenty of things we can do to make this script even better, but at this point it has served as an introduction to automating a process within Acumatica using PowerShell. I do intent to follow up with more PowerShell content with future blog posts – so stayed tuned. 

If you are interested in learning more about the use-case I outlined for payments, you can access the APS Payments Acumatica user guide. If you have further questions about integrated payment processing for Acumatica, please contact the APS Payments team

Happy coding!

Robert Waite

Robert is the Lead Acumatica Developer at APS Payments, a REPAY company, a leading provider of omni-channel B2B integrated payment solutions. Robert’s passion for programming started in grade school doing projects on the Apple IIe, and he checked out all the library books he could on the topic to appease his voracious appetite for learning how to code. Robert started working with ERP platform software in 2003, where he automated distribution tasks on a green screen apps called PICK, which he later replaced with Sage 100. Since then, he has expanded his expertise to many other ERP systems, including Acumatica. Prior to joining APS Payments, Robert worked for Accounting Systems, Inc. (ASI Focus), where he was an ERP Software Developer. Outside of programming, Robert is passionate about dancing, where you will find him spending a lot of time looking for any opportunity to West Coast Swing, Hustle, Salsa, as well as many other styles of dance. In his spare time, he enjoys soldering together racing drones and IoT home automation projects. Robert volunteers his time at a local high school maker space and has taught classes on programming Arduinos and other IoT development boards like the Raspberry Pi.

Categories: Developers

Subscribe to our bi-weekly newsletter