Friday, 3 November 2017

SQL In<> Operator in BQL

Hi There,

Today want to show you example of amazing and very new BQL Operator - In<>.
This operator was added just recently with Acumatica version 2017R2, so now you can pass there an array of values and Acumatica core will convert it to SQL IN ( ... ) statement.

Here is a code example:
Object[] values = new String[] { "AC""IN" };
InventoryItem item = PXSelect<InventoryItem,
       Where<InventoryItem.itemStatus,
In<Required<InventoryItem.itemStatus>>>>.Select(Base, values);
Console.WriteLine(item.Descr);

This will be converted to following SQL
Select * from InventoryItem InventoryItem
Where InventoryItem.Status In ('AC', 'IN')
Order by InventoryItem.InventoryCD

Please note that In<> operator is available only with Required<> parameter and you need to pass array of possible values manually to Select(...) method parameters.

Have a nice development!

Wednesday, 1 November 2017

Acumatica Framework API Reference Guide has been Published

Dear Developer,

I'm are happy to announce that Acumatica team has updated descriptions of all Acumatica Framework core classes and methods as part of new Acumatica Development Reference Library. All of them are located in one place now: Acumatica xRP Reference Guide

The content is collected directly from the Acumatica code and presented in a clear and concise way. You can easily find the needed information and code examples, which you can use in your customization and custom solutions.


Please let me know if you have any questions or other suggestions!

Tuesday, 31 October 2017

Remove base Attribute with Customization

Hi All

In my previous article "Append and Replace of DACs Attributes" I have described how can you replace append attributed in DAC with customization.

Today I would like to add one more useful attribute - PXRemoveBaseAttribute. This one can help you to just remove one specific attribute from base field and replace it with new one if needed.

[PXDBString(32)]
[PXDBDefault("Test")]
[PXUIField(DisplayName = "Test" )]
public String Field { get; set; }

Example 1:
public Extension : PXCacheExtension<DAC>
{
    [PXRemoveBaseAttribute(typeof(PXDefaulAttribute))]
    public String Field { get; set; }
}

Example 2:
public Extension : PXCacheExtension<DAC>
{
    [PXRemoveBaseAttribute(typeof(PXDefaulAttribute))]
    [PXDBDefault("New String")]
    public String Field { get; set; }
}

Have a nice development!

Wednesday, 18 October 2017

Rates per Day in the Report

Hi All,

Today I would like to share you my experience in building report that needs to translate currency rate on the fly.
The main challenge in this task as you may not have rates for every day. So that mean if your document is for 20th of Jan but there is no rate for that date, you need to find the latest available from list of the rates. Unfortunately this task is solvable just with standard Joins and standard DACs as we need to have little bit more tricky logic.

So to fix that I have created an SQL view that can find last available rate for the particular date.Than I have added custom DAC to Acumatica that later can be used in the Report Designer.  You can find full code of the view and the DAC in later in this article.

Here you can see join conditions
You also can use select following SQL statement where you can check how does it work.

Source code:

Have a nice reporting!

Monday, 16 October 2017

Move Customization project to Source Control/Another Server

Hi There,

When you are doing customization it is usually much faster and easier to start it using Acumatica Customization Browser and even create an extension library from there.
But later, you might need to move customization somewhere:
  • to source control folder 
  • to another production/testing/development server
  • to use Acumatica ERP instead of framework.
In this article I want to show you how to link all the things together if you move it.
Customization usually consists of 2 things:
  • Customization package - just a zip file that contains all the changes that should be applied during customization publication.
  • Sources of extension library - that is something that should be kept outside of customization package and require additional movement.

Monday, 9 October 2017

Difference between Acumatica ERP and Framework

Hi All,

Previously I had quite a lot of questions about how to integrate development done on Acumatica Framework with Acumatica ERP.
Usually them main reason of that question is misunderstanding of what is Acumatica Framework.

Acumatica Framework (what you can download as separate installer from portal) is designed to develop product that is completely separated from Acumatica ERP. That is why there are less functions and no code integration with ERP.

In case you are see benefits of using functions form Acumatica ERP (like segmented keys, numbering, accounts, subaccounts, customers/vendors, inventory items), it would be much better to start development on Acumatica ERP itself from the beginning, and do not use Acumatica Framework at all.
T300 - Acumatica Customization Platform training guide exactly explains you how to create new functionality with close connection to ERP and even how to use Visual Studio without installing Framework.

Difference between ERP and Framework
The main misunderstanding I faced before here is that based on the name you may expect that Framework should be installed prior to ERP (like .NET Framework), but that is not correct.
When you install Acumatica ERP, framework is already included there and does not require separate installations.
If to dig little bit in the details, Acumatica Framework consists on multiple system libraries: PX.Common.dll, PX.Data.dll, PX.Web.UI.dll, set of system screens as aspx file and so on. Comparing to that almost whole Acumatica ERP is included into one PX.Objects.dll and set of screens as aspx files.

So if to simplify it little bit - ERP contains all possible dlls, but Framework missing some of them with business logic. Other things are absolutely the same.
Based on description above you can understand that you can develop absolutely the same way on Acumatica ERP as you do in on Acumatica Framework. And even more ERP gives you much more benefits as it has a lot of nice business logic that you can reuse and save cost on development.

There still is one real difference of Framework comparing to ERP - Framework has function to deploy templates of forms that you can create from Visual Studio.
However that is not really needed as you can use Acumatica Customization Tools to generate forms and DACs. You can read more about it here and here.


That misunderstanding comes to T100 and T200 training guides where you learning how to develop new functionality using Framework. Later you think that it is the proper way of doing new development.
However it just shows you that you can use Acumatica Framework without ERP at all and does not limit you on such ways. In T300 training guide you also learn how you can use ERP directly do so same development including programming in Visual Studio.

As I know later Automatic's team will update training guides to new flow, so it should eliminate that misunderstanding, and it all will be fine.

Have a nice development!

Tuesday, 3 October 2017

DACs Inheritance and Caches

Hi All

Want to speak today about DACs inheritance in Acumatica and some issues that it cause to development.

As you know C#/.NET as OOP-oriented framework support inheritance of objects, but when in comes SQL and Database structure than there is no inheritance between tables.
As a bonus I will explain why we need BAccount and BAccount2 DACs and what is the difference.

Friday, 29 September 2017

Multiple DataViews on the same Form

Hi All,

If you didn't know previously, in Acumatica has introduced new syntax of defining DataViews/Fields.

Previously you have to create a new Form, assign DataView to form and than add there fields.
But now you can use following construction: "DataMember.DataField" right in the DataField property.

<px:PXSelector DataField="BranchID" />
<px:PXCheckBox DataField="DefLocation.IsRemitContactSameAsMain" />
<px:PXTextEdit DataField="RemitAddress.AddressLine1" />
<px:PXTextEdit DataField="RemitAddress.AddressLine2" />

Also, a DataMember can be specified as an attribute of a PXPanel, and it will be inherited by all internal controls.
<px:PXPanel RenderStyle="Simple" DataMember="RemitContact" >
    <px:PXMaskEdit ID="edFax" runat="server" DataField="Fax" />
    <px:PXMaskEdit ID="edPhone1" runat="server" DataField="Phone1" />
</px:PXPanel>

Have a nice development!









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.

Box.com Integration - to store and share you files you can use Box.com 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.
Description
Database Mapped Field
Virtual Field
String Value
PXDBStringAttribute
PXStringAttribute
Byte Value
PXDBByteAttribute
PXByteAttribute
Short Value
PXDBShortAttribute
PXShortAttribute
Uniqueidentifier Value
PXDBGuidAttribute
PXGuidAttribute
Bool Value
PXDBBoolAttribute
PXBoolAttribute
Integer Value
PXDBIntAttribute
PXIntAttribute
Long (Int64) Value
PXDBLongAttribute
PXLongAttribute
Double Value
PXDBDoubleAttribute
PXDoubleAttribute
Decimal Value
PXDBDecimalAttribute
PXDecimalAttribute
Date Value
PXDBDateAttribute
PXDateAttribute
Timestamp Value
PXDBTimestampAttribute
<NONE>
Integer Identity Value
PXDBIdentityAttribute
<NONE>
Long (Int64) Identity Value
PXDBLongIdentityAttribute
<NONE>
Binary Field
PXDBBinaryAttribute
<NONE>

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.