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:

Monday, 28 November 2016

PXUIEnabled and PXUIRequired Attributes

Hi All,

If you developing something on Acumatica xRP platform you know that changing of the UI fields visibility should be done through PXUIFieldAttribute on the RowSelected event.

This works perfect on small screens with limited number of controls. But most of ERP screens are not really small. So most probably you already saw such types of code on the RowSelected in SOOrderEntry graph and many others.

Not nice and really hard to support.
Luckily now we have a different way to do it:

  • PXUIEnabled - based on provided BQL conditions can automatically change UIFieldAttribute Enabled property.
  • PXUIRequired - based on provided BQL condition can automatically change PXDefaultAttribute PersistingCheck property.
  • PXUIVerify - based on provided BQL condition can automatically change validate that new value meets provided conditions. Also can trigger validation on update of dependent fields,

Usage of PXUIEnabled:

#region isActive
public abstract class isActive : PX.Data.IBqlField
[PXUIEnabled(typeof(Where<isFinancial, NotEqual<True>>))]
[PXUIField(DisplayName = "Active", Visibility = PXUIVisibility.SelectorVisible)]
public virtual Boolean? IsActive { get; set; }

Usage of PXUIVerify:
#region TimeBillable
public abstract class timeBillable : IBqlField { }
[PXDefault(0, PersistingCheck = PXPersistingCheck.Nothing)]
       Switch<Case<Where<isBillable, Equal<True>>, timeSpent,
              Case<Where<isBillable, Equal<False>>, int0>>,
[PXUIField(DisplayName = "Billable Time", FieldClass = "BILLABLE")]
[PXUIVerify(typeof(Where<timeSpent, IsNull,
       Or<timeBillable, IsNull,
              Or<timeSpent, GreaterEqual<timeBillable>>>>),
PXErrorLevel.Error, Messages.BillableTimeCannotBeGreaterThanTimeSpent)]
[PXUIVerify(typeof(Where<isBillable, NotEqual<True>,
       Or<timeBillable, NotEqual<int0>>>),
PXErrorLevel.Error, Messages.BillableTimeMustBeOtherThanZero,
       CheckOnInserted = false, CheckOnVerify = false)]
public virtual int? TimeBillable { get; set; }


Have a nice development!

Wednesday, 23 November 2016

Getting started with Acumatica xRP Platform

Hi All,

Sometimes I get a questions like this:
  • How to start development with Acumatica xRP platform? 
  • How complex is it? 
  • Where we can get some guidance?
So here I want to summarize all thoughts that I have about it.
development with Acumatica xRP platform

I would select these steps that i would recommend you to do:
  1. Ensure Pre-Requisites
  2. Get Acumatica 
  3. Prepare Development Environment
  4. Learn Acumatica xRP Platform
  5. Do Development
  6. Communicate & Support
Lets go though each of these steps in details under the cut.

Monday, 21 November 2016

100 articles on the Blog!

Hi All!

Today we have a small anniversary - 100 articles:

acumatica blog

Thank you for constant support and attention! I really hope that this information useful for you and will do my best to provide you more interesting articles in the future.

Meanwhile, your opinion is very important for me, and I would be really happy to hear your feedback, requests and topic suggestions that may help you to know Acumatica better.

Have a nice day ahead! Thank you.

Site Configuration using Customization

Hi All,

Acumatica as most of web applications has some configurations that are stored on the file system. For example it can be a web.config or any other file that is stored in Site folder itself.
configurations acumatica
During upgrade procedure Acumatica overrides all standard files to ensure that system will work correctly with all latest platform changes correctly. This is correct, as any automatic merge of configurations can cause to compilation errors. This is especially harmful if you site is in public cloud and you have no access to the instance.
So that means if you do custom configuration (like redirection rules, filters or custom controls) your changes most probably will be lost right after update. There are several exceptions when configurations will not be overridden:
  • You keep configurations in separate (not standard) file. Acumatica will not touch any file that is included into standard Acumatica.
  • Some specific changes can be preserved by Acumatica Configuration wizard. Connection String, Active Directory, SSO, timeouts are the example of preserved configurations.
But what to do if you really want to reconfigure something?

Monday, 14 November 2016

Acumatica Test Companies

Hi All,

As you may know Acumatica does not limit the number of users that can connect to the system but it limits the number of cores that may be used by the system and number of tenants (companies) that can be created and used in the database.
However these roles applies only for properly licensed instance. If you are using Acumatica in the trial mode (read more there), you have a limitation on 2 concurrently working users and maximum 10 tenants.

Here I want to discuss with you some points number allowed tenants.
When you apply license to Acumatica instance, you limit number of production tenants to allowed by the license. Usually it can be something like 3, 10, 20, ..., 50, .....
If you add more companies than is allowed, Acumatica will automatically restrict list of visible companies to maximum allowed number.
acumatica limits
All other companies will be still safe in the database but you cannot access it.

But what to do if you want to have any additional tenant to test something or do an experiment, but install a demo data. Luckily this is possible with Test Companies feature.

The "Test company" is an additional tenant in the databases that is not included in the list of companies allowed by license. But in the same time Acumatica applies on the test company the same restrictions as on the trial mode:

  • can be used even if you have maximum number of production tenants
  • no limitations on number of test companies
  • for all test companies in the system you cannot have more than 2 concurrently working users.
  • any production company can be flagged as test at any time
  • if you make a company as test, it cannot be reverted.
  • all demo companies will be used as test.
If you want to make you company as test, it is very easy - just click appropriate button on manage companies screen.
acumatica test
As this operation is not revertible, you should confirm your operation:
Please confirm that you want to make 'Company8 (9)' a test company. Test companies are intended for training and demonstrating Acumatica, and are not for intended for production use. This operation cannot be reverted.

Now you can see that your company is marked as test:
test company acumatica

Have a nice testing!

Wednesday, 9 November 2016

Reuse Session during API Calls

Hi All,

If you using Acumatica Web Services you most probably saw the examples where you need to call Login and GetSchema methods before each valuable operation.

This is OK in general, but if you building some complex and high loaded integration as the result you may get something like this:
integration in acumatica

Not very nice as it creates additional traffic network traffic and unnecessary load to server.

Much better way is to cache Cookies and Schema on the client side and refresh it only if it was not in use for some period of time.

Here I want to share with you an example where I create a new class inherited from Screen and call Login operation automatically before each Submit operation if service was idle for more than 10 minutes.
This static class is more for your idea, i'm pretty sure you may have a better way to do it on the production environment.

Also note that you can encapsulate some additional logic in your class and make your integration code simpler.

Code Snippet:

Have a nice Integration!

Monday, 7 November 2016

Most Popular Acumatica DAC Attributes

Hi All,

Today I want to share with you some statistics on Acumatica xRP Attributes usage across Acumatica ERP Business modules. The goal of this article is to show you a full list of attributes that you may use for customization and also give you a chance to learn some new useful attributes, that you have not seen before.

This data was collected from PX.Objects.dll of Acumatica 6 and may be different from version to version.
All attributes are sorted by the usage frequency, highlighted by value and grouped by colours:
  • Fields attributes
  • Selectors and aggregate attributes
  • Business logic
  • Others
In the brackets you can find approximate value of how many time this attribute is used. Here you can find that was used more than 5 times on all DACs.
Also some attributes (like list attributes) were hidden from the list as they do not bring a value to the reader.

After the brackets you can find brief explanation of attribute meaning. Please note that most of the attributes have been described in the context help
attribute meaning acumatica

and Acumatica Framework reference guide as well.
Acumatica Framework reference guide

Welcome under the cut for more details:

Wednesday, 26 October 2016

Alter GL Transaction during Inventory Release

Hi All

Today I want to share with you one of possible ways how you can catch the process how Inventory release created a GL Batch and GL Transactions.

Lets assume that we want to change description or Sub-Account during inventory receipt release, than i need to do following things:

  1. Create extension of INReleaseProcess, that is responsible for release operation.
  2. Override ReleaseDocProc, that is doing main release job.
  3. In this method we have links to JournalEntry and INRegister, where we can check what is going on and add some additional logic
  4. Add RowInserting event to JournalEntry that will handle creation of batch or tran
  5. Change description or sub-account where it is required

In the end you have exactly required batch.

Code snippet:

Have a nice Development!

Thursday, 20 October 2016

Encrypted batch payment file (ACH, GIRO)

Hi All,

In Acumatica you can easily generate electronic payment files (such as ACH, GIRO and others). By using this process you will have a file that will have payment amounts and other required information.
But what about data protection? Bu default payment file will have clear text amounts.
Today i want to share with you how you can easily encrypt this file.
electronic payment files acumatica

Payment file will be generated by Acumatica Integration Services Export Scenario. The main class that is responsible for generation file is Integration Services Data Provider. In Acumatica out of the box we have only 2 providers : ACH and GIRO.

In my Example i will show you standard .NET encryption using Triple DES encryption.

  1. Using customization module create a new provider and inherit it from from base provider (ACHProvider or GIROPaymentProvider).
  2. Override SetFile method.
  3. Put your encryption logic there.
  4. Publish customization.
  5. create a new provider in  acumatica
  6. Use new provider type in the export provider. Do not forget to update provider object and fields when you changing type. Also will be good to save previous object name, as Export Scenario is linked to provider object by name.
  7. export a new provider in  acumatica

That is all you need to do. Now you can generate a file and it will be encrypted.
create a new provider in  acumatica

Full code snippet:

Have a nice decryption!

Monday, 17 October 2016

Acumatica Test Framework

Hi All,

Acumatica has a strong focus on the Platform and Development tools, that can help all our clients and partners to provide better product faster and with lower cost.

One of the nice and free tool that Acumatica provides as part of platform is Acumatica Test Framework. Acumatica test Framework is a set of tools and libraries that can be used for unattended black-box testing of any product or customization that is based on Acumatica Platform right in your favorite browser. For interacting with UI controls and components Acumatica Test Framework uses Selenium Web Driver. You can read more about Selenium here.

The diagram of Test Framework components is described here.
Test Framework diagram acumatica

The usage flow is like this:
  1. You have Acumatica ERP instance with required module or customization that you want to test.
  2. Using Page Wrapper Generation Tool you create a set of classes that will represents Acumatica page structure including controls, buttons, grids, columns, panels and so on. This tool is related on Acumatica platform and can dynamically analyse all standard and custom pages including customization. You need to create wrappers for each page that you want to test.
  3. Test Framework contains all required classes and methods that you can use to work with browser pages and controls. For example you can open selectors, search values, type or past values, click buttons, wait for long run operations and so on.
  4. Based on Wrappers and Framework classes you write your own Tests (that is classes) that will contains test logic.
  5. Using Test Runner you can lunch required tests. Test runner will load all test libraries and using interaction with Selenium Web Driver will open browser and execute all required steps.
  6. Selenium Web Driver is intermediate layer between test and browser. This tool knows browser API and can simulate user clicks with mouse and keyboards on each control separately. Also it can get values back to validate result. 
  7. Browser is embedded in test SDK, as it requires specific version of browser to run smoothly. When you run test you will see browser window and can check how your test is running.

Wednesday, 12 October 2016

Replacing Cache with custom class

Hi All,

Today i want to share with you a good trick how you can override original PXCache object.
Why you may need it?

  • To replace base cache logic, that should not be executed in some cases.
  • Forbid cache to read data for database in case of virtual DAC
  • Inject required logic to the standard workflow
  • Do not clear cache data on graph clearing
  • Access protected method
And many other things.

As you may know, that when you (or system) create a new instance of a graph, it will automatically instantiate all caches for each data views that are declared. Unfortunately, because this is unattended process it is almost impossible to adjust it for our own purposes.

But luckily Acumatica is very flexible system and there are several easy ways to do it lately after base initialization. So the reccomended solution will be:

  1. Create an graph extension for required Graph. Or also you may use constructor of the graph, if you develop new functionality.
  2. Override Initialize method to add your logic
  3. Create a new class for cache that should be inherited form PXCache<DAC> type.
  4. Instantiate a new cache object a new cache of the same DAC type.
  5. Replace original cache in the PXGraph.Caches collection with a new one.
On now it is done. You may use your new cache type and put logic there.

Code example:
public class InventoryItemFilterCache : PXCache<InventoryItemFilter>
       public InventoryItemFilterCache(PXGraph graph)
              : base(graph)

       public override void Clear()

       public override int Persist(PXDBOperation operation)
              return 0;

public class NonStockItemMaintExt : PXGraphExtension<NonStockItemMaint>
       public override void Initialize()
Base.Caches[typeof(InventoryItemFilter)] = new InventoryItemFilterCache(Base);


Have a nice Development!

Thursday, 6 October 2016

Provide Default Value for Report Parameter from Branch

Hi All,

Really often you may want to have some default values for report parameters, that may depend on environment. For example different default warehouse depend on branch where are you working now. Or different warehouse depend on current employee.
But unfortunately it is not out of the box functionality in Acumatica.

Luckily, using powerful Acumatica Customization engine it is quite easy to do. Let me share with you a way to archive it.

Scenario: lets assume that we want to have different default warehouse on Inventory Valuation report based on different branch that is selected as current.

We will do this task in 3 steps:
  1. Adding custom field to link branch and warehouse
  2. Create a class that can evaluate required warehouse by currently selected branch
  3. Provide default parameter in report 

Monday, 3 October 2016

Generate Grid Columns Dynamically

Hi All,

Today I want to show you an example how you can generate grid columns dynamically.
Sometime it is really important for dynamic inquiry report, where you want to show data that may be changed due to some other configurations.
generate grid columns dynamically in acumatica

To add columns dynamically you should follow this plan:
  1. Create a separate method that will generate columns.
  2. Call method create above from the graph constructor. Please not that if you put method into any event you need to make sure that you generation columns only once. Also you logic assume that columns may be changes if user select some other values on the same screen you need to make sure that you delete previously generated columns.
  3. On the columns go throught all new columns and do following:
    1. Add new Field to the PXCached
    2. Subscribe 2 events for FieldSeleting and FieldUpdating. These are most important events that are interacting with user interface. On the FieldSeleting you need to provide value and the configuration how this value should be displayed. On FieldUpdating you need to check, how system updated the value back to cache.
Here you can see a code snippet for columns generation:
protected void GenerateColumns(PXCache sender, List<String> columns)
       foreach (String column in columns)
              String key = column;
              String name = column;

              if (!sender.Fields.Contains(key))
                      this.FieldSelecting.AddHandler("Partners", key,
(PXCache cache, PXFieldSelectingEventArgs args) =>
                      this.FieldUpdating.AddHandler("Partners", key,
                             (PXCache cache, PXFieldUpdatingEventArgs args) =>


Friday, 23 September 2016

Custom Selector Attribute

Hi All,

Today I want to discuss with you a ways to define selectors and data for them.
define selectors and data for them in acumatica
Usually you define selectors like this:

In this case system will automatically select value from database Carrier table and show list of Carrier IDs to user.
In this case you have 2 potential problems:

  • you should have a database table, otherwise you will see an error.
  • There is no way to dynamically change or adjust data that is returned to user
  • You can limited configurations to control selector behavior. 

To solve these problem Acumatica has a Custom Selector attributes:
public class CustomerPriceClassAttribute : PXCustomSelectorAttribute
    public CustomerPriceClassAttribute()
        : base(typeof(ARPriceClass.priceClassID))
            this.DescriptionField = typeof(ARPriceClass.description);
    protected virtual IEnumerable GetRecords()
        foreach (ARPriceClass pc in PXSelect<ARPriceClass>.Select(this._Graph))
            yield return pc;


In this case you see that you have simple attribute, that is really similar to the attribute for first example. Using GetRecords method you can create a custom BQL querry, select any data you want, apply any filter and return it as a IEnumberble collection.
In the user interface you will see exactly data provided by your custom query.

Monday, 19 September 2016

Make compilation of extension library faster

Hi All,

When you develop customization using Microsoft Visual Studio you may notice that compilation process (if you click F6 or Ctrl+Shift+B) is really long - you may wait for 5-10 minutes.

Usually this happens because system compiles full solution that contains DLL and also Web Site. Site has multiple pages that have aspx markup and must be transformed before compilation. But actually this process is required only for validation purpose and does not require for Acumatica functionality, as ASP.NET will compile page any way one more time when you open it in browser.
You may read more about it here.

So as it is optional compilation you may guess that it can be disabled. Just go to web site property pages and deactivate 2 configurations:

  • Build Web site as part of the solution
  • Before run startup page - no build.

After this your compilation process will take just few seconds instead of minutes. And it is absolutely save for your customization development.

But we still highly recommend you to do one full compilation and validation before releasing of final production version of customization to ensure that there is not errors on final pages.

Have a nice development!