Guest Post: Tackling an Integration Challenge via Push Notifications in Acumatica

Mark Franks | October 9, 2018

Written by Diane Cawley | Founder/Chief Software Architect | Savant Software, Inc. | @Savant_WMS

Introduction¹

This article’s purpose is to show you how to solve one of the many integration challenges developers face by taking advantage of the the Push Notifications capability that the Acumatica platform provides. Push Notifications, which was released in version 2017 R2, has multiple configuration options. This article explores one of those options to achieve the goal.

For an excellent overview of the topic, view Vladimir Perov’s presentation at this past year’s 2018 Virtual Developers Conference.

Challenge

We have an Warehouse Management Solution (WMS) which manages and tracks the movement of inventory from receiving through the shipping process.  It is an external application that integrates tightly with Acumatica. In our application, we need to know when a new Purchase Order has been created,  modified, or deleted, so that our application can use that Purchase Order and it’s line item details to perform purchase receipts against it.

We also need to know when Sales Orders, Shipments, and Stock Items are created, modified, and/or deleted.   This scope of this article will focus on Purchase Orders.

Current Approach

Using Acumatica’s Contract-based API model, we have implemented a Windows service that runs on our application server, and wakes up every few minutes to look for new Purchase Orders which were created and/or existing Purchase Orders that were modified.  This is the standard approach – poll the Acumatica instance at certain desired intervals and fetch the data that has been added or changed since the last time it was polled. We use the LastModifiedDateTime property on the PurchaseOrder entity to get this information.

This process works beautifully for getting new and modified purchase orders.   It is fast and can handle a large number of orders using the GetList() method within the Acumatica API model.

But there is a problem with this approach, which is:  “What happens if a user deletes a purchase order completely in Acumatica?”  When the above mentioned service runs, there is nothing returned in the GetList() because the data is no longer in the database.   So this opens up a problem whereby our application still thinks that there is a Purchase Order to be received, but in reality, that Purchase Order does not exist.

The current API solution to this is to select all Purchase Orders in our database and do a series of Get() commands to see if each Purchase Order still exists in Acumatica.  If it does not exist, we can then also delete it from our database. Obviously, this is VERY inefficient.

The example below illustrates this point:

Notice that we have to perform the Get() on the PurchaseOrder entity each time through this loop, which is not good for server performance.  Imagine do this for 500+ open purchase orders every 5 minutes throughout the day and 99% of the time the result is that the Purchase Order does exist and we don’t have to do anything on the WMS.

 

//POTable is populated with a DataTable of all Open Purchase Orders in the WMS

   foreach (DataRow POR in POTable.Rows)

   {

       PONumber = POR.Field<string>(“PONumber”);

       //now check to see if Acumatica has this PO in its database

       PurchaseOrder POtoCheck = new PurchaseOrder

       {

           ReturnBehavior = ReturnBehavior.OnlySpecified,

           OrderNbr = new StringSearch { Value = PONumber }

       };

       PurchaseOrder poFound = (PurchaseOrder)soapClient.Get(POtoCheck);

       if (poFound == null)

       {

           //Ah ha! this one was deleted in Acumatica

           //

           //Update the Savant DB here to CANCEL or DELETE the PO

           //

       }

   }

 

The Solution

If only there existed a way that Acumatica could notify us when the document changed and/or was deleted. Push Notifications to the rescue!

Push Notifications act similarly to SQL Triggers. They are activated when data in a given data object is Inserted, Updated or Deleted. The associated data can then be sent somewhere externally to be processed.

As outlined in the Virtual Developers Conference session and in Acumatica’s Developer’s documentation, there are 2 possible configurations to set up the source for Push Notifications:

  • Results of a Generic Inquiry (GI)
  • Create a Built-In Notification Definition

I have chosen to create a GI and use that definition to create the Push Notifications.

A GI called “WMSPurchaseOrders” is created which selects 3 columns from the POOrder table.

 

 

There are a few things to note when creating a GI for the purpose of using it in Push Notifications.  

  1. Use as simple a data query as possible.
  2. Do not use aggregation and grouping in the query.
  3. Do not use joins of multiple detail tables (like Sales Order – Shipments – Shipment lines, i.e. several many-to-many relationships)
  4. Inner joins in queries may work a bit slower than left joins
  5. For a query defined by using a generic inquiry, do not use a formula on the Results Grid tab of the Generic Inquiry (SM.20.80.00) form.

Finally, you also need to make sure that the “Expose via OData” option is selected.

Once the GI is saved, you will need to initialize the GI via OData by going to its link:

http(s)://<AcumaticaInstanceURL>/OData/<YourGIname>

Enter the login credentials as prompted and you will see that the OData feed is running.

The next step is to create the Push notification definition.  This is located on the Integration menu.

 

 

Note that there are 3 possible destination types that can be used for the notification as shown in the above screen capture.  These options are detailed in the Integration Developer Guide.

For this article, I have chosen to use Webhook option.

 

In the Address field, I filled in the address as the local web API project that was created to accept the incoming notifications. Then, I added the name of my GI to the details in the bottom of the screen.  You can set up more than one GI in the list to notify the same destination if you’d like.

Make sure that the check-box for Active is set.  You do have the ability to inactivate a notification by de-selecting this box.

Testing the Configuration

Now that the notification is setup properly, we can test it out.

The easiest way to test a Webhook destination is to go to www.webhookinbox.com and create a new inbox.  This page will give you a URL to use.  Put this URL into the Push Notifications Screen / Address field as is noted in the screenshot below.

 

Now, we go into the Purchase Order screen and Delete an existing PO.

Once that is deleted, we can check our WebhookInbox session and see the successful notification:

 

The POST API was executed by Acumatica and the data is showing in the screen.  Here’s a more friendly view of the JSON:

{“Inserted”:[],

“Deleted”:[

{“OrderNbr”:”PO000699″,”Status”:”On Hold”,”LastModifiedOn”:”2018-10-07T23:31:06.887″}

          ],

“Query”:”WMSPurchaseOrders”,

“CompanyId”:”Company”,

“Id”:”eec58e4b-db63-4cde-af2a-51cac3c4488f”,

“TimeStamp”:636745570492722724,

“AdditionalInfo”:{“PXPerformanceInfoStartTime”:”10/08/2018 00:57:28″}

}

 

Here is some Information about the fields in the POST:

  • Inserted – the new rows in the query. This is for if a Purchase Order was inserted, we will see it here.
  • Deleted – the rows that were present in query but were removed. If a Purchase Order is deleted we will see it here. Comparing deleted and inserted sets will show updated fields.  Only the fields defined in the GI will be listed in the Inserted and Deleted sections. Therefore, if the Status of the Purchase Order is changed, we will see data in both the Inserted and Updated fields.
  • Query – the name of the source definition (class name for Built-In definitions or Generic Inquiry name for GI definition).
  • CompanyId – Acumatica company
  • Id – transaction identifier generated on DB level. Acumatica guarantees at least one delivery, thus ‘Id’ and ‘Query’ fields can be used as key fields to detect duplicate notifications.
  • TimeStamp – a value that is guaranteed to increase with every transaction. Can be used to define a sequence of events when processing multiple notifications.

Any failed notifications will be shown in Acumatica in the Integration menu, under the Process Push Notifications screen.   Below is an example.  You can click on SHOW NOTIFICATION and it will show the JSON here, and you can also see the Error.

 

 

From this screen, you can select rows in the grid and resend them if you believe you have corrected the issue, or you can delete the from the grid listing.

Important to note that the Notifications which fail are left in this list for 2 days, after which time they are automatically deleted by Acumatica.

Receiving the Notification

Now that we know how the notifications are sent from Acumatica, we can implement a Web API from within our WMS application that will accept the POST transaction from Acumatica.

When we receive the notification, we need to look at the JSON data, confirm that the Insert section is empty, and the Delete section is populated.  This is our indicator that a deletion has taken place for the OrderNbr listed.

Then, in our database, we can delete the appropriate rows from our tables, and make a transaction log entry to show up in history, if needed.

When our external WMS Web API code is up and running, we can go back to the Push Notification definition screen and change the Address field to the URL of our new API and save it. Now we are ready to receive notifications.

The Last Piece of the Puzzle

As an ISV product, we have certain assets that need to get implemented in Acumatica via a customization package.   There is now a Push Notifications option on the Customization Package screen.  We just need to add our Push Notification identifier to the package, along with our GI and other necessary elements for installation on our customers’ sites.

 

Conclusion

When it comes to synchronizing the data between two disparate systems, such as an ERP system and WMS application, there are often several way to accomplish the task.  But as developers we like our solutions to efficient, scalable/performant, and easy to maintain.

Push notifications are useful for many functions.  It is a possibility to use Push Notifications for notifying the WMS that there are new Purchase Orders available and to use that to populate the WMS database.  But getting multiple single transactions is not efficient when you think that you may have bursts of new orders being created. Selecting new and modified Purchase Orders using the Contract-based API’s is much better from a performance perspective. This is even more the case for Sales Orders and/or Shipments.

However, for certain operations that are not handled with Get() or GetList() from the contract API model, Push Notifications fills an important gap.  Even more important with respect to our needs, we will be looking for deletions of purchase order lines, sales order lines, and shipment lines, as this is also a gap in the Contract-based API model.  Push notifications will allow us to have a more tightly-integrated / loosely-coupled interface between Acumatica and Savant WMS.

One item I would like to see addressed for the future is that when defining a push notification, I would like to be able to choose INSERT / UPDATE / DELETE as options.  This could possibly reduce the number of notifications being sent by a very busy Acumatica customer instance.

Acknowledgements

I would like to take a moment to thank Acumatica’s Vladimir Perov for his presentation in June 2018 and also to Ruslan Devyatko for reviewing the pros and cons of Push Notifications with me.


¹The work for this article was performed on version 2018 R1.

Mark Franks

As a Platform Evangelist, Mark is responsible for showing people the specifics about what makes Acumatica’s Cloud Development Plaform wonderfully attractive to ISV & Partners. He's also passionate about Running, Latin, and his family. | E-mail: mfranks@acumatica.com | Skype: mfranks |

Subscribe to our bi-weekly newsletter

Subscribe