Monday, 19 December 2016

Optimizing Large Import

Hi All

Want to share with you some good approaches to Import a large amount of data.
Import a large amount of data acumatica
If you ever tried to import some large set of data from Excel you actually know that it is not as fast as importing data into the SQL table. There are plenty of reasons why it is like this:
  • Data validation
  • Defaulting empty field
  • Running of business logic
  • Data integrity 
  • Updating of referenced data
  • Security tracking
  • Audit 
Of course all these rules have different effects in different modules. It is obvious that GL is much faster than SO Orders. At the same time, AR is faster than SO, but slower than GL.
Also import depends on hardware and database performance.

If you import AR Invoices from Excel you can expect to save about 2 AR Invoices per second.
In this case ideally you can import about 150 000 invoices per day. Actual performance may be different because performance depends on number of invoices and other dictionaries in database. Also this is just saving and does not include release operation that also may take similar amount of time. In addition to the performance we may have problem with errors handling.

What you need to do if you have 1 000 000 invoices to import and little time before go live of a site? It's impractical to use Excel files and import scenarios to do that. It will take too long, and will be very difficult to manage errors and add more advanced data mapping logic.

In this article I want to touch several optimizations tips that can be grouped into few groups:
  • Database Optimizations
  • Import Process Optimization
  • Business Logic Optimization
Lets go through all of them:

Thursday, 15 December 2016

Acumatica and Microsoft Flow

Hi Everyone,

Recently Microsoft has added a new tool to their Office 365- Microsoft Flow
With help of my college - Tim Rodman, we have done some investigations on how it can be used with Acumatica.
Microsoft Flow acumatica

I think that in modern internet of services and things services like Microsoft Flow, Azuqua and Zappier might be very useful.

Microsoft flow is most modern here so it does not looks like very mature and stable now. But i belive it is quite promising as Microsoft has enough resources to integrate it with most of Office 365 services. Looking forward to see that progress.

However even now it has some nice things, like HTTP integration where you can call various of web services using REST API.
HTTP integration acumatica

And really luckily Acumatica already supports REST API.

Monday, 12 December 2016

Enabling Upload from Excel for the Grid

Hi All,

Today I want to share with you a way how to enable upload form excel for custom grid.

Actually this is quite simple -

  1. Step 1 - define PXImportAttribute on data the data view under the grid.

  2. public class CSCalendarMaint : PXGraph<CSCalendarMaint, CSCalendar>
           public PXSelect<CSCalendarExceptions> CSCalendarExceptions;

  3. Step 2 - enable appropriate Grid Mode (Allow Upload) on the grid properties.
    enable appropriate Grid Mode acumatica
This will enable upload button on the grid action bar with standard functionality.

In some cases, you may want to have more control on the import process. In this case you also may implement optional interface: IPXPrepareItems. This interface should be implemented on processing graph itself.

public interface IPXPrepareItems
       bool PrepareImportRow(string viewName, IDictionary keys, IDictionary values);
       void PrepareItems(string viewName, IEnumerable items);
       bool RowImported(string viewName, object row, object oldRow);
       bool RowImporting(string viewName, object row);
  • PrepareImportRow - This event will be triggered right before insert record to cache. You can review keys and values that will be assigned to new record.
  • PrepareItems - Used to review items before import, but right now does not execute for performance optimizations.
  • RowImporting - This and next events will be executed only if you decided bypass update of existing records. On that event you can decide what to do with existing record if it was in the database before, for example delete it.
  • RowImported -  Here you can control update operation after insert.
Unfortunately right now there is an issues with declaring PXImport attribute on extension, but this thing will be fixed soon and you will have a easy way to import everything instead of typing.

Have a nice development!

Wednesday, 7 December 2016

Using Colors in Acumatica

Hi All,

Today I want to share with one way how you can highlight some Acumatica rows or data with colors or other text styles.
highlight colors in acumatica

Monday, 5 December 2016

Custom Formula for ACH/GIRO Providers

Hi All,

In the previous article about custom payment providers i have mentioned that Schema file supports several build-in formulas, like:
  • Count - Count of details or nested groups in group
  • TotalCount -  Total lines in all nested groups
  • BlockCount - Count of internal blocks including adjustments for fixed structure
  • CountOneBased -  CountField  + 1. Required if you need calculate header or footer with details
  • TotalCountOneBased - TotalCountField +1. Required if you need calculate header or footer with details
You also can add more formulas there. For example if you want to have Count that will calculate header and footer together with details you may need TotalCountTwoBased formula.

Please check this code snippet for more details:

Have a nice development! 

Thursday, 1 December 2016

Client Events using JavaScript

Hi All,

Today I want to share with you one way how you can add some client validations/calculation using JavaScript right in the browser with no accessing server data.
javascrypt acumatica
In general i would suggest you to not do any complex calculations on client side and always use server for all data manipulations. However in some specific situations this approach may save a lot of processing time because client events may do calculations right in the browser.
Also note that client logic will be available only for browser users and will not be triggered from web services API and Mobile application.

The scenario that i want to do is quite simple - trigger the code after the value changes in some controls and validate/calculate its value. This example is designed for demonstrating purpose only so I would like just to show a notification box with new value when someone change GL Batch description.
As a much better example of client events may be a playing of sound on button/control click. Really good explanation and example example is provided on Stack Overflow.

To add required validation lets complete following steps: