Saturday, 30 December 2017

Access Rights Database Reference

Hi All,

Want to share with you reference about architecture of Access rights in Acumatica.
This knowledge you can use for
  • Sharing data between companies
  • Copy data between companies for faster implementations
  • Mass update of access rights with database
  • Just for fun :)
All acumatica access rights are located in the 6 different tables: Users, Roles, UsersInRoles, RolesInGraph, RolesInCache, RolesInMember. Lets check all of them.

Friday, 29 December 2017

Change Azure VMSize for Acumatica Package

Hi All,

Past several years Microsoft has added multiple possible VM sizes (that you can check here) and they actually will not stop at this point.

So we need a good way to easily change some properties, if we want to have custom Azure configurations. Today i want to share with you the way how can you repack Acumatica Azure package.

To complete there steps you need to have installed:
  • Microsoft .Net 4.0+ for running PowerShell script.
  • Azure SDK 2.9+ You can download it here. Actually you just need a one single tool that will do packing - MicrosoftAzureAuthoringTools-x64.msi. Than you will be able to find a CSPack.exe tool by this path "C:\Program Files\Microsoft SDKs\Azure\.NET SDK\v2.9\bin" CSPack can generate *.cspkg files, that are actually should be uploaded to Azure
  • 7zip.exe for extracting files
The idea about this approach, that Azure package is just a Zip file, that can be extracted. So we can extract it, change required configuration and pack it back.

Here you can see folders structure that I have prepared for this task:
folders structure acumatica
  • In the "In" folder you should put your Acumatica Service.cspkg right there.
  • "Out" folder will contains repacked file in the end of process.
  • "Package" folder contains some tools and temporary extracted files required for the process.
     folder contains acumatica
    • "7za.exe" - 7zip console tool
    • "template.txt" list of system files required for packing
    • "properties.txt" list of additional required properties
  • "1-extract.bat" will extract data from standard package.
  • "2-pack.bat" will pack data back after modification of properties.
  • "ServiceDefinition.csdef" is Azure csdef file that contains some properties and actually includes vmsize that we need to change. 

So the entire scenario:
  1. Download Acumatica Azure Package from Acumatica web site.
  2. Put Package to "In" folder
  3. Run Extract.bat
  4. Change vmsize in the "ServiceDefinition.csdef" file to required size for your company
  5. Run Pack.bak 
  6. Get Result package from "Out" folder.

Code files:

Have a nice Installation.

Wednesday, 27 December 2017

Link Between GL And AP/AR

Hi There,

In case you have not seen that before, Acumatica has links between GL Batch lines and AP/AR Invoices lines - Following conditions should be applied:

  • GLTrain,TranType = ARTran.TranType;
  • GLTrain,RefNbr = ARTran.RefNbr;
  • GLTrain,LineNbr= ARTran.TranLineNbr;
TranLineNbr is populated on release of a document in another module with the number of the corresponding line in that document.

Please note that TranLineNbr can be empty in some cases. For example if you have consolidation posting to GL, than multiple AP/AR trans will be combined in one GL line, in that case TranLineNbr cannot be evaluated. Specially this field is not populated when PX.Objects.GL.GLTran.SummPost is ON.

Using that knowledge you can update something in GL from AP/AR transactions. For Example:

protected void GLTran_TranLineNbr_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e)
       GLTran row = (GLTran)e.Row;

       if (row != null
&& row.RefNbr != null && row.TranType != null && row.TranLineNbr != null)
              ARTran tran = PXSelect<ARTran,
                      Where<ARTran.tranType, Equal<Required<ARTran.tranType>>,
                             And<ARTran.refNbr, Equal<Required<ARTran.refNbr>>,
                             And<ARTran.lineNbr, Equal<Required<ARTran.lineNbr>>>>>>.
Select(Base, row.TranType, row.RefNbr, row.TranLineNbr);
              if (tran != null && tran.Date != null)
                      row.TranDate = tran.Date;

Have a nice Customization.

Calling Base Actions by example of Generating Time Cards

Hi All,

Just a short article where I can share with you customization that can generate time cards from uploaded time activities.
 time cards
Customization doing following:
  • Selects the first and the last unreported (where no time-cards assigned) activity from currently selected employee.
  • Goes week by week between first and last activities
  • Create a new time card for every week using TimeCardMaint graph
  • Code also automatically submit time card for approval
  • In case there is unreported activity for already submitted or released time card, code can automatically generate time card correction and submit it again.

Tuesday, 26 December 2017

Dashboards and Reports on Mobile

Hi All,

Do you know that Acumatica Mobile native app has supported dashboards and reports since release of version 2017 R2.
dashboards and reports acumatica
Here you can see YouTube video with full presentation of Acumatica Mobile capabilities - Acumatica Mobile App - Functionality and Customization

But in this article I want to show you how to configure it by you own. Welcome under the cut.

Monday, 25 December 2017

Restore Large Snapshot

Hi Everyone,

Have you seen such error before when you try to upload snapshot of the size more than a gigabyte?
The Error message: "Snapshot file size exceed the maximum allowed size".
Acumatica Snapshot Size Limits
There are multiple reasons why it is not allowed - connection stability, IIS limitations, Memory size, on the fly processing limitations and so on.

But what you still need to restore a snapshot and you need to in any possible way? Lucky you there is a stable way for that using Acumatica Configuration Wizard.

Monday, 18 December 2017

Visual Studio Code Snippets

Hi All,

If you search on ways to optimize your development experience you can try to look at the code snippets for Acumatica events and DAC fields.

Here it comprehensive instruction on how to develop own code snippets.
I want to share with you snippets I use in my own local environment. Please follow the installation Instructions:

  • Below you will see the XML code from GitHub Gist that you need save as a file: Acumatica.snippet
  • Place this file to snippets location : C:\Users\<username>\Documents\Visual Studio 2015\Code Snippets\Visual C#\My Code Snippets
    • Code Snippets
  • Launch your Visual Studio and try to use it.

    • Code Snippets

Code snippet is under the cut.

Wednesday, 13 December 2017

Synchronize User Passwords between Companies with SQL Trigger

Hi Everybody,

Here want to share with you one easy way how to easily sync passwords between users in all companies in Acumatica - we can use SQL triggers.
A trigger is a special kind of code that automatically executes when an event occurs in the database server. Database Engine execute trigger when a user tries to modify data through a data using Update/Delete/Insert commands.

In our case we can catch the event when password is changed in specific company and distribute it to all other companies we need.


Have a nice configurations.

Thursday, 7 December 2017

Copy Dashboard

Hi All,

Previously I shared with you how to copy dashboards using SQL script.

Starting from Acumatica 2017R2 you can also copy dashboards with 2 more ways:
  • Using customization project
  • Export/Import from/to XML buttons

Steps using Customization Project:
  1. Go to Customization Projects and create a new one.
  2. Find Dashboards section In the Customization Browser.
  3. Find and Select there a dashboard that you want to copy.
    1. Steps copy dashboard acumatica
  4. Go and publish this customization to other companies
    1. Steps copy dashboard acumatica
Done, you can use it now.
Please note that you can export customization project and copy dashboards to other Acumatica Instance.

Steps using Export/Import from/to XML
Another way you have Export to Xml/Import from Xml buttons on Dashboards. These buttons will allow you get copy and upload dashboards definitions between databases and servers.
copy dashboard acumatica

Have a nice Configurations!

Monday, 4 December 2017

Multi-Company Drop-Down

Hi All,

Today want to show you how to have different values of drop-down in different companies:
  • Company One
    • Values 1, 2, 3
    • Labels "1", "2", "3"
  • Company Two
    • Values 3, 4, 5
    • Labels "1", "2", "3"
To do this we can use following nice features in Acumatica Platform:
  1. PX.Data.Update.PXInstanceHelper.CurrentCompany - to get ID of current company
  2. FieldSelecting - Event where you can change/adjust value just before it will be included in response to browser.
  3. e.ReturnState = PXStringState.CreateInstance(...) - State contains full description of control that should be generated in the user interface with app UI properties and configurations. By adjusting state on the fly you can change list of allowed labels and values
  4. Custom Attributes - to reuse one logic in many places
Please see full code example under the cut.

Monday, 27 November 2017

Restrict Data Accessibility using Generic Inquries

Hi Everyone,

In this article I want share with you one more idea of how can you use Acumatica Generic Inquires.
Acumatica Generic Inquires
Generic Inquires in Acumatica is key part of reporting and have plenty of usage examples:
And many others. Today I would like to add here scenario with restricting access right to data based on custom filtering conditions in the Generic Inquiry.

Lets assume we have two departments who is using opportunities for Products and Services. But we would like to disallow them see opportunities of each other.
Acumatica Generic Inquires

Friday, 24 November 2017

Mirror Mobile Screen on Computer

Hi Everyone,

Today want to share with you some tips on how to share your mobile screen with Acumatica to PC. That is really useful when you want to share great Acumatica mobile application with your customers.
Acumatica mobile application
Type: Free
Platform: Android
Internet connection is required.
Free application that uses internet connection to share Android screen to your PC. Internet conenction is required.

TeamViewer QuickSupport 
Free for non commercial use
Platform: Android
Internet connection is required
TeamViewer Quick Support allows you to connect from your PC to mobile and control it from using fingers and mouse. From PC you can connect using standard TeamViewer app.

Type: Paid, 15 USD
Platform: iOS and Android
Internet connection is not required
Nice app that can work and with iOS and with Android devices using screen-cast feature of mobile app. However unfortunately I had issues with reflector 2 and android devices. For some reason receiving device was not visible in some cases.

Handy Andy
Type: Free
Platform: Android Emulator for Windows 
Internet connection is not required
Good and free android emulator that might be useful if you do not have any interned or Wi-Fi connections during presentation.

Type: Free
Platform: iOS
Internet connection is not required.
Alternative app to Reflector.

Screen Cast
Type: Free
Platform: Android
Internet connection is not required.
Interesting app that works as web service on the mobile. You can use browser to connect to this services right from mobile. 

Screen Cast

Have a nice Demos.

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,
In<Required<InventoryItem.itemStatus>>>>.Select(Base, values);

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.

Acumatica Framework API

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.

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

Example 1:
public Extension : PXCacheExtension<DAC>
    public String Field { get; set; }

Example 2:
public Extension : PXCacheExtension<DAC>
    [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.
SQL statement

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.
Acumatica Customization Browser
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.
 Acumatica Framework 6.1

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.
 Acumatica Framework 6.1
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.
DACs inheritance in Acumatica

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.
DACs inheritance in Acumatica
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" />

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.
Database Timeouts

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: