Friday, 22 September 2017

Database Timeouts

Hi Everyone,

As you may know all databases queries (like, Insert, Update, Delete, Select) should have timeout to prevent deadlock and prevent over-usage of hardware resources.

In Acumatica we have 2 timeout for Select query:
  1. Report Timeout - is used only for reports, as it may need some time to retrieve data from database for big report. To configure it you can use reportQueryTimeout="100" in PXSqlDatabaseProvider section of Web.config file. Parameter is defined in seconds and has default value as 30 seconds. You can read more here.
  2. Querry Timeout - is used only for inquires and other selects, as some inquiries also might take more time than allowed by default. To configure it you can use queryTimeout="100" in PXSqlDatabaseProvider section of Web.config file. Parameter is defined in seconds and has default value as 30 seconds. You can read more here.
For performance and deadlock-protection reasons Acumatica does not have general timeout configurations for Insert/Update/Delete, however you can change it from code using PXCommandScope (read more here). If you create that scope and change timeout, than next SQL command will be executed with your timeout.

Have a nice queries!

Tuesday, 19 September 2017

Consolidation Reports using Pivot Tables

Hi Everyone,

You most probably know that if you need to prepare consolidated reports in Acumatica you should check ARM Reports (Analytical Reports Management)

However there is one more option available with last release of Acumatica
  1. Create a Generic Inquiry that extracts Account/Subaccount balances for specific period from GL. Here I have a separate article that describes way to extract GL data.
  2. Dynamic pivot table that will split data by ledgers as columns and Accounts/Subaccounts as rows.
The difference here from my previous post is that here we should change GI to return much more data - for all accounts/subaccounts, multiple ledgers and periods. We need provide more data specially for pivot table that would aggregate selected it based on our rules.
Do not forget that you need to specify list of ledgers for consolidation explicitly, I have done it with conditions on GI, but you can do that using reusable filters as well.

Here is the GI result. See that it returns balances for every account, ledger period:

Here you can find result a pivot table:

Thursday, 14 September 2017

Some Samples of Existing Integrations

Hi All,

In the modern Multi-Cloud world single software almost cannot survive alone. And for ERP it became even more complicated as ERP is a core of the company and need to get data from all the parties.

Just recently I tried to check  how many Integrations with already have in Acumatica out-of-the-box or as open source customization and I was really surprised!
Hope you also will find it interesting for you.

Microsoft Exchange Server Integration - Acumatica can dynamically sync Contacts, Tasks, Emails and Events. That is available as additional feature

Microsoft Outlook Plugin - Using plugin you can easily tag your emails with Opportunities and Cases in Acumatica CRM. Also plugin allows you to dynamically create contacts in your CRM. Feature available with CRM module.

Sales Force Integration - Acumatica offers online and batch synchronization with Sales Force out of the standard Integration Services module. This is additional feature.

Microsoft Excel Integration - You can export and import data from/to excel using multiple ways: from grid, using integration services, using OData protocol. Availabe with the platform.

Microsoft Power BI Integration - You can integrate Microsoft Power BI with Acumatica using OData protocol. Available with the platform. More details here.

Avalara Tax Calculation Integration - Acumatica offers integration with 3rd party tax calculation services - Avalara. This is additional feature in Acumatica. More details here.

Hub Spot Integration - Using Acumatica Integration services Acumatica offers integration with great marketing tool - Hub Spot. Available with Integration Services.

DocuSign Integration - To allow you dynamical signature of the documents you can use open source integration with DocuSign. Available as open source. More details here.

Twilio Integration - In case you need SMS notifications, you can use integration with Twilio. This is open source customization. More details here.

Authorize .Net Integration - for online payment processing of credit cards Acumatica has out of the box integration with Authorize .Net. Available with financial module. More details here.

Open Exchange Rates Integration - For daily sync of excange rates Acumatica offers out-of-the-box integration with global exchange rates service. Available with financial module. More details here. Integration - to store and share you files you can use integration. Available as open source. More details here.

Azure Blog Storage and AWS S3 Storage - you also can use build-in ingratiation with Microsoft or Amazon storage services to manage your files better.

UPS/USPS/FedEx Integration - if you are providing any delivery services you may be happy to use integration with various carries. Available with Distribution module. More details here.

SurveyMonkey Integration - In case you are working with clients and want to hear back from them about your services you can use integration with Survey Monkey. Available as open source. More details here.

FTP Server Integration - Acumatica uses it to sync files from external FTP server to database. This feature is a part of files integration process in Acumatica. Feature is available together with platform.

Much more integrations are provided by our partners - check here for details,
And even mere that all integrations here you can create using strong Acumatica Framework!

Have a nice integration!

Monday, 11 September 2017

Database Mapped vs Virtual Fields

Hi All,

Today want to share with you how Database vs Virtual fields are working in Acumatica.

You may notice that some of the fields are linked to DB, some of them not.The difference is in the Attributes are used on it - PXDB<Type>Attribute is mapped on database field, PX<Type>Attribute is not. So from user standpoint it is just prefix "DB" or "".

In Acumatica there are many fields are mapped to database and most of them have a clone, that is not mapped. Here you can see list of most common attributes.
Database Mapped Field
Virtual Field
String Value
Byte Value
Short Value
Uniqueidentifier Value
Bool Value
Integer Value
Long (Int64) Value
Double Value
Decimal Value
Date Value
Timestamp Value
Integer Identity Value
Long (Int64) Identity Value
Binary Field

How does that work?

Monday, 4 September 2017

One-Time Action Executing in the Future Date

Hi Everyone,

Sometimes if you need to run heavy operation but afraid that it might affect performance day of the system, you can use following trick with scheduler.

In Acumatica, Automation Scheduler can be scheduler to run only one time at the specific date and time. Please note that exact day is specified on Dates tab and exact time on Hours tab.

As an example you can schedule import scenario for importing/updating of customers on the weekend:

Just note, if there is any error, you'll need to check that manually. As for now Acumatica does not sends any notification on failed scheduled action. To solve that issue you can create a dashboard that will shot you status of scheduled tasks.

Also note, that task may not be executed if Acumatica is not stand by at that time. Make sure that your application server is not configured to suspend W3WP process (that is IIS process that hosts Acumatica) in case of inactivity. You can check that article to find how to do that.

Hope it helps and have a nice weekend!

Monday, 28 August 2017

Disaster Recovery vs Failover

Hi All,

Today want to speak with you about protecting your ERP from accidents.

There are 2 possible way to protect you application from disaster, which are different by approach and recovery time:
  • Disaster Recovery (DR) - involves a set of policies, procedures and tools to enable the recovery infrastructure and systems following a natural or human-induced disaster. So basically disaster recovery assumes that disaster can happen and we just need to recover it as soon as possible.
  • Failover Protection - is automatic switching to a redundant computer server, system, hardware component or network upon the failure. Failover basically assume that stability of the system should not be affected by any disaster. 
Lets check how can we implement it in Acumatica.
In the explanations below i'm keeping away from network disaster, as it is a separate topic not related to Acumatica.

Friday, 18 August 2017

Adding new Report Button on Invoices and Memos screen

Hi All,

Today want to share with you the case how to add a new button to print alternative Invoice Form right from Invoices and Memos screen.
Lets check how does that work and check how original button "Print Invoice/Memo" is configured.

Automation Steps
Fist of all if you check the code, there is no handler for "Print Invoice/Memo". That is some sort of virtual button defined through Acumatica Automation Workflow.
If you check Automation Steps for "Invoices and Memos" screen you can find that menu "Print Invoice/Memo" is added to action "Report".  That is exactly action that will be executed in the code.

If you click "Fill With Values" button you also can see that this action passing some parameters to code.
Fields with normal name (like "Printed") are real DAC fields. Acumatica will put there your value when you click on button.
Fields those started with "@" is not a read field, they are parameter of functions in the code.

Monday, 14 August 2017

Number to Words Localization

Good Day.

Today want to give you an idea on how can you customize amount to words transcription in Acumatica.That might be required for any language localization.
Here you can find simple example, but real complexity might be related to your language rules.

All the main work around that feature is done by [PX.Objects.AP.ToWordsAttribute]
It can be defined on virtual field, and upon accessing by UI or Report will do automatic transcription from number to text.
e.ReturnValue = LangEN.ToWords((decimal)((PXDecimalState)DecimalVal).Value, ((PXDecimalState)DecimalVal).Precision);

If you need you can apply that attribute to other amounts and reuse transcription for other reports.
You can read more about it on StackOverflow.

Wednesday, 9 August 2017

Printing Two Reports at Once

Hi All,

Want to share with you one trick that I just discovered with help of one of our great partners in Africa region.

If you need to print 3 copies of the invoice (Customer Copy; Finance Copy; and Distribution Copy) at one click, you can use this trick:
All you need is just create 2 reports and include copy into the main one as sub-report.

See steps here:

1) Have 2 reports. I'm using here 2 invoice formats form Sales Demo data.

2) Open main (of first) report in report designer and add new sub-report in the last (report footer) section.

Do not forget to do following there:
  • Specify Second report name in the sub-report configuration.
  • Pass parameters from main report to sub-report
  • Add a page break before printing a copy
3) Now you can save report to Acumatica and test it.

Have a nice reporting!

Monday, 7 August 2017

Redirecting to External Page from Button

Hi There,

Want to share with you my experience with working with redirects from Acumatica.

You may know that due to security issues and stability of software, redirect to external web site in the iFrame is forbidden in Acumatica.
So every time when you use "PXRedirectToUrlException" for url like "" system will open url in separate window/tab. That is OK in mane cases, but sometimes you really need that functionality.

One way to solve that issue is well described in a separate article - Redirect on Page Load 
But that one is applicable only if you want to have a menu item in Acumatica SiteMap.

If you want to redirect use based on internal logic/code, that this way may not be the best.
To understand how to solve that issue, I want to explain how redirects works in Acumatica first

You know there there are multiple redirect exceptions available:
  • PXRedirectRequiredException
  • PXRedirectToUrlException
  • PXRedirectWithReportException
  • PXRedirectToGIException
  • And many others.
But basically all of them are different only in how system search for required URL and with cache of items (if you redirect to another screen). So for example "PXRedirectRequiredException" will find URL from sitemap based on graph type to what you do redirect.

After url is determined, system cancel current round trip and returns a special message back to JavaScript in the page. This message is "eRedirect<CODE>:<URL>" keyword with url and no other data.
<CODE> - is a number from 1 to 8 that defines how system should open new page
  • Redirect0 - open page in the same iFrame with no keeping session
  • Redirect1 - same as above but with keeping session
  • Redirect5, Redirect6 - same as above but with new popup
  • Redirect3 - will open new frame-set
  • Redirect7, Redirect8 - will open a new window

So as soon as you know how does it work, there is simple solution - throw your own exception with specific code instead of standard one:

    public PXAction<PX.Objects.PO.POReceiptRedirect;
    [PXButton(CommitChanges true)]
    [PXUIField(DisplayName "Redirect")]
    protected void redirect()
       throw new Exception("Redirect0:" "");

That code will open Acumatica page in the same frameset, just as an inner page.

Have a nice redirection!