Lightweight persist to database

Hello everybody,

today I want to describe following use case. Quite often it is needed to persist to database one or another DAC class, which is filled by some data. 

As usually I see people do this via hard coding of DAC class inside of the Graph. But today I want to share with you a way of persisting DAC class without hardcoding it as a view. 

In order to accomplish this, you can use following graph:

public class ImportEntitiesInsertion : PXGraph<ImportEntitiesInsertion>
    public string AddView(Type dacType)
        var viewName = "_DYNAMIC_" + dacType.GetLongName();
        if (!this.Views.ContainsKey(viewName))
            var command = BqlCommand.CreateInstance(typeof(Select<>), dacType);
            var newView = new PXView(thistrue, command);
            Views.Add(viewName, newView);
        return viewName;


After that, in some other place of the code, you can use this graph like this:


var graphForInsertion = PXGraph.CreateInstance<ImportEntitiesInsertion>();
var dacType = typeof(SOOrder); 
var viewName = graphForInsertion.AddView(dacType);
for (int i = 0; i < 10; i++)
    var newOrd = new SOOrder();


 What I especially like about this approach, is that records will be persisted initially in the cache, and only after you'll call Persist, all bunch of records will be persisted to database.



How to override properly CreateMatrixItems

Hello everybody,

today I want to leave a short snippet on how to override methods in CreateMatrixItemsImpl graph extension. Below goes code snippet you can use for this purpose:


public class CreateMatrixItemsImplExt : PXGraphExtension<CreateMatrixItems.CreateMatrixItemsImpl, CreateMatrixItems>
    public override void Initialize()

With help of this code fragment you can override and customize a bit more Matrix management of Acumatica.

Execution Timeout Expired error message in Acumatica

Hello everybody,

today I want to leave a short note on Execution Timeout Expired. In case if you face it on your local dev environment, then consider adding following key in your web.config:

<add name="PXSqlDatabaseProvider" type="PX.Data.PXSqlDatabaseProvider, 
PX.Data" ... queryTimeout="100" />

With that addition, you'll be able to execute long running queries without timeout. 


But keep in mind, that such addition can affect performance of Acumatica for some cases. So use that freely for your dev instance, and be careful with your production instance.

Error message Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' while importing MySQL dump

Hello everybody,

today I want to leave another note on how to import MySQL database, when you import from MySQL for Myob and Acumaitca.

Error message looks like this:

In order to fix it, I suggest to open dump file in any text editor, and remove ,NO_AUTO_CREATE_USER, line:

After deletion just run Data import in MySQL workbench one more time.


If you have error message Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'  when you run export for MySQL db for Acumatica or Myob, then just use find and replace all for removal of on empty string, and execute import one more time.



FUNCTION pp_conv2smallInt does not exist while importing database for MYOB and Acumatica

Hello everybody,

today I want to share with you one rake, which stolen from me few days of my life. 

Recently I imported SQL backup of MySQL database, and got error like this:

17:20:58 Restoring D:\Backups\Wire\rev.sql
Running: mysql.exe --defaults-file="c:\users\zalju\appdata\local\temp\tmp5dndjm.cnf" --protocol=tcp --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments --database=rev < "D:\\Backups\\Wire\\rev.sql"
ERROR 1305 (42000) at line 70858: FUNCTION rev.pp_conv2smallInt does not exist

Operation failed with exitcode 1
17:28:28 Import of D:\Backups\\Wire\rev.sql has finished with 1 errors

Error message looked similar to what you can see below:

After plenty of googling and applying different advice I was disappointed as nothing worked for me. 

Then I've decided to take a look on clean database of installed Acumatica and discovered the following:

Then I've decided to create such a function manually and tried to execute import one more time. 

Execution lasted for a bit longer period of time, but now I got another error message, but now related to function pp_conv2smallInt, which you should be aware of how to fix.


If you make import of MySQL database, then prior to it create functions pp_conf2int, binaryMaskTest and other standard Acumatica functions, otherwise you'll get error messages similar to mine, and anyway will need to create them properly. Not very much convenient but working approach. In other words, cheap comes with it's price.





How to make selector for CSAnswers

Hello everybody,

recently one of the colleagues asked me how to make selector from Attributes values. Also that request seem trivial, but still took some time, especially with usage of FBQL query to build. 

Below goes template you may use if you'll need some kind of selector for attributes by some predefined value:


public class SOOrderExt : PXCacheExtension<SOOrder>
        public class Codes
            public const string MediaCode = "MEDIACODE";

            public class mEdiaCode : BqlType<IBqlStringstring>.Constant<mEdiaCode>
                public mEdiaCode() : base("mEdiaCode")

            public const string OrdOrigin = "ORDORIGIN";

            public class ordOrigin : BqlType<IBqlStringstring>.Constant<ordOrigin>
                public ordOrigin() : base("ORDORIGIN")

        public string SomeValue1 { getset; }

        public string SomeValue { getset; }


 For me it was also interesting to note, that in the past it was common to use for Selector combination of PXSelector with Search, but in FBQL you'll need PXSelector with SearchFor.

How to connect Acumatica with MySql at api level

Hello everybody,

today I want to leave a short note on how to connect Acumatica with MySql. Recently for me was needed to organize connection between API project which send requests to Acumatica and MySql database. In codebase I've seen following line:

 using MySqlX.XDevAPI.Relational;

 But when I've tried to find nuget package MySqlX, I've find nothing useful. After a bit of googling I've discovered that it is needed to have referenced MySql.Data package. After I've added MySql.Data package on my project, all MySqlX staff become active, and I was able to build and execute my project


Create index with Include section

Hello everybody.

Today I want to leave a short note on include section of SQL queries. Imagine that you see SQL Query similar to this:

 CREATE NONCLUSTERED INDEX [tt_CSAttributeGroup] ON [dbo].[CSAttributeGroup] ([CompanyID], [EntityClassID], [EntityType], [IsActive])  INCLUDE ([AttributeCategory], [CreatedByID], [CreatedByScreenID], [CreatedDateTime], [DefaultValue], [LastModifiedByID]) WITH (ONLINE = ON)

 And while part in the first brackets is clear, then second part Include is not very clear from the first point of view. 

What is difference between index which has INCLUDE section and index which doesn't have INCLUDE section?

Include section means, that values will be stored with the key itself, but will not be part of the key. 

In case of presented SQL query, which is used by Acumatica, key values will be created based on CompanyID, EntityClassID, EntityType, IsActive columns. They will be used for building nodes of the tree. And also each node of the tree besides having Key calculated will also have those columns: AttributeCategory, CreatedByID, CreatedByScreenID, CreatedDateTime, DefaultValue, LastModifiedByID .

How this fact may be used later?

Once SQL server will search for some field, based on CompanyID, EntityClassID, EntityType, IsActive columns in it's query, and will find them, SQL will not need to go to the data itself for columns AttributeCategory, CreatedByID, CreatedByScreenID, CreatedDateTime, DefaultValue, LastModifiedByID. But will read values of those columns from the tree itself. 

If to go further with illustration, then at storage level B+tree structure without include section will look like this:

but with include section it will look like this:


If to compare indexes with INCLUDE and indexes without INCLUDE section, you can make following conclusion: indexes with include section will make faster reading from database. But they will cause longer insert/update operations, as each change will cause copy/paste overhead. And if you have a lot of columns included, then each insert/update will have double loading on your system.







How to clean duplicated elements from Acumatica database

Hello everybody,

Today I want to leave a short snapshot which addresses following issue. 

One of my friends got table in Acumatica created, but for some reason at DB level he decided not to set there any field as part of Primary key. He had IsKey attributes set only at DAC class of Acumatica, but not at Database level. As outcome he got duplicated items at database level, and it was needed somehow to delete duplicated items. How to achieve it? 

After a bit of research, I've come with T-SQL code like this:

DELETE  Record
FROM    (
			SELECT  rowno = ROW_NUMBER() 
				OVER (
				PARTITION BY ACGLiquidFamilyPrice.LiquidFamilyCD, ACGLiquidFamilyPrice.PriceEntryDateTime, ACGLiquidFamilyPrice.CompanyID
				ORDER BY ACGLiquidFamilyPrice.LiquidFamilyCD, ACGLiquidFamilyPrice.PriceEntryDateTime, ACGLiquidFamilyPrice.CompanyID )
			FROM  ACGLiquidFamilyPrice
        ) AS Record
WHERE   Record.rowno > 1

Columns LiquidFamilyCD, PriceEntryDateTime, CompanyID - are key fields.

ROW_NUMBER in connection with Partition by allows to delete only records, which are more then one. 


COVID-19 dealing

Hi everybody,

It happened. I've got COVID-19 myself. As well as my wife. Very unpleasant feelings honestly speaking. 

On the day 1 of symptoms I've got those:

a. Temperature ( ~38 )

b. Headache

c. Weakness

Now I have one more concern about my parents. I'm worried if they got or not got COVID-19. And in case if yes, I'll have one more reason to worry.

Update on 2020-07-16

As of now by being in hospital I've got some improvements in my health. Temperature become lower. But now  I got another symptom: caught with blood. So my advice for anyone who says that COVID-19 is fake is this: it is not fake, but very real disease. Don't look on it lightly.

Update on 2020-07-24

I was sent to continue dealing with Covid-19 to home. Luckily for me, treatment went fine, and I'll continue on way of recovering. 

Update on 2020-08-11

I got two negative PCR tests on Covid-19. Besides that I have also regained my working memory back. During Covid-19 my working memory was affected. In the middle of the sentence I've used to forget what I supposed to say. But for now I don't have this issue which makes me very happy as of now.