AcumatiCares
Introduction
Our product, SPS Commerce EDI brings EDI data into staging tables in Acumatica via Web Service for later import into the ERP. Specifically, JSON data must be deserialized and then added to Acumatica data objects. Since we circumvent the UI when loading data into the product, it is possible to load string data into the cache that will exceed SQL specifications when inserted by the ORM. In previous iterations, the Acumatica ORM system would truncate this data automatically to fit the SQL field. As of a few years ago, however, the ORM was changed so that it attempts to insert the data without a check and a SQL error is thrown by the database if a field spec is exceeded. I needed a way to reinstate this check and possible truncation. In addition, due to the size of our EDI product I have around 20 different incoming data streams to check with more to come. Therefore, the challenge was to validate the incoming string data for multiple DACs in one easy to manage code base without hard coding field sizes.
Solution
Check_Field_Lengths_InsertUpdate<T> is a generic method which means that the parameter data types are deferred until the method is called. After I’ve deserialized my JSON data and translated it into DAC objects, I call the method with the cache of the appropriate data view and the list of DAC objects as parameters. The method then loops through each DAC object accessing the DAC definition via the cache and checking each string field in the object for length. If necessary, the method truncates the incoming data and then inserts or updates the object (myCache.Update() does both!) via myCache.
GIST: https://gist.github.com/patrick711/9ff5b6e9da90bcc52e14803269be5dd7
In the production version of this method, a warning is generated for the user when a truncation has occurred. Since this is staging data, the user then has the option to fix the data and reprocess it. In this way, our workflow is protected from fatal errors that might have dropped the entire transaction.
Summary
Hopefully you’ve found this interesting and even if you have no need to validate string data, you’ll be interested in implementing generic methods to process multiple DAC types and keep your code easy to maintain.
Happy Developing!
