Purpose of RowPersisting event

Hello everybody,

today I want to leave a note on usage of RowPersisting event.

Quite often I see situations, when RowPersisting is used for making additional insertions to database. Also quite often I see cases when some additional inserts being performed to database. 

I want to warn against such an approach. Reason for that is that during RowPersisting event, Acumatica opens transaction scope. Because of that, additional readings from db, or additional persists to db in scope of RowPersisting may lead to performance degradation and even deadlocks. 

Purpose of RowPersisting event is kind of latest resort, in which you can modify your record before putting it to database. And it shouldn't be used for some other purposes. Other purposes of RowPersisting event is validate record before it was putted to database, or cancel commit operation through throwing of an exception.






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 get user friendly error message out of WebException

Hello everybody,

today I want to leave a short notice on how to get user friendly text of WebException. Imagine that you need to know which part of data in your json is missing. How can you quickly figure out which part? For one of my projects I wanted to get easy way of saying user which field is potentially missing. In order to achieve that I've used following fragment:

catch (WebException ex)
  string errorMessage = string.Empty;
  if (ex.Response != null)
      using (var errorResponse = (HttpWebResponseex.Response)
          using (var reader = new StreamReader(errorResponse.GetResponseStream()))
              errorMessage = reader.ReadToEnd();

After that I've just used to throw exception with adding additional details over Data property of Exception class.



How to catch all MySQL queries generated by Acumatica

Hello everybody,

finally I found out how to catch all queries to MySQL server, generated by Acumatica. Well, in context of My SQL as usually people work more with MYOB, but under the hood MYOB is Acumatica.

Typical schema of Acumatica <-> MySQL connection looks like this:

In order to get generated MySQL queries, you may need some proxy service, which will intercept queries. You can use MySQL proxy, but instead of MySQL proxy I suggest to use Neor Profile SQL as it has much more convenient UI:


In order to achieve such catching of all My SQL queries, you'll need following steps:

  1. Install Neor Profile SQL.
  2. In your Acumatica web.config make following change:
    <remove name="ProjectX_MySql" />
    <remove name="ProjectX" />
    <add name="ProjectX" providerName="System.Data.SqlClient" connectionString="Server=localhost;Port=4040;Database=PXProjecti

pay especial attention to this part: Port=4040

3. Next goes configuration of Neor Profile SQL. Create connection to MySQL server in a way similar to what you see on screenshot:

4. You are all set. Now Acumatica will send SQL queries to Neor Profile SQL, while Neor Profile SQL will re-translate them to My SQL:


If you need to catch generated My SQL queries, you can go with My SQL query proxy and logging all files to file. Or with help of Neor Profile SQL you may get nice tool for tracking all generated queries. 

And also with such steps you can track everything that MYOB generated!

Create customization to MYOBAdvanced(Acumatica), part 3 сreate customization and publish it

So, continue the work:) 

Part 3 сreate customization and publish it

  1.  Create customization

To create customization for import to MYOBAdvanced (acumatica) I need go to Customization - Customization Project and add new customization (Screenshot 10):

In this example I use design of acumatica ( add screen, add page,  files and scripts) see Screenshot 11.

Also I add sql script that will add new column "UsrBodyWithoutHtml" to table "CRActivity" (Screenshot 12"):

After this click on "Publish"; I see whether validation has passed and there are no errors. If all ok I can close window and click export customization. ActivityCustomization.zip folder will be downloaded.


          2. Import and test customization

To test the customization I need launch another  the same build version of MYOBAdvanced (acumatica) web site, go to Customization - Customization Project and click "Import" and select my dowloaded ActivityCustomization.zip folder.

After this click Publish. Open the page AC601000 and make sure everything is working properly and displayed. The same on mobile device. It is done!

Thank you for reading. 



Create customization to MYOBAdvanced(Acumatica), part 2 сreate page, graph, DAC class extension and xml page for mobile

So, continue the work:). Part 1 is here.

Part 2 сreate page, graph, DAC class extension and xml page for mobile

First of all, In project I create folder "DAC" and add two clasess:

  1. ActivityTypes.cs

This class need to filter types rows from table CRActivity(column name - Type). Client asked display all events for that Business Account of type "Phone Call", "Client Visit" and "Futile Visit" So for this I do next:

This constant types I will use in graph when will select all from table CRActivity.

     2. CRActivityExtension.cs

This class need for correct show "Body"(Comments) from table CRActivity. Some fields include html tags. To show clear text I do next:

public class CRActivityExt : PXCacheExtension<CRActivity>
        public abstract class bodyWithoutHtml : IBqlFieldIBqlOperand
        [PXUIField(DisplayName = "Activity Details")]
        [PXString(IsUnicode = true)]
        public virtual string BodyWithoutHtml { getset; }
        public abstract class usrBodyWithoutHtml : IBqlFieldIBqlOperand
        [PXUIField(DisplayName = "Activity Details Without Html")]
        [PXDBString(IsUnicode = true)]
        public virtual string UsrBodyWithoutHtml { getset; }

Please pay attention that for string - UsrBodyWithoutHtml I use PXDBString atrribute, it`s mean that field will be in database CRActivity. This field is necessary for mobile application. I don't know why, but mobile application weren't able to display field that was created dynamically.

Next step - create graph , .aspx page and mobile site map:

     1. ActivitiesMaint.cs

       #region Selector
       public PXSelect<BAccount,
       public PXSelect<CRActivity,            Where2<Where<CRActivity.bAccountIDEqual<Current<BAccount.bAccountID>>>,                And<Where<CRActivity.typeEqual<ActivityTypes.fType>,                    Or<CRActivity.typeEqual<ActivityTypes.mType>,                       Or<CRActivity.typeEqual<ActivityTypes.pType>>>>>>,        OrderBy<Desc<CRActivity.startDate>>> Activities;        protected IEnumerable activities()        {            var result = new PXSelect<CRActivity,                Where2<Where<CRActivity.bAccountIDEqual<Current<BAccount.bAccountID>>>,                    And<Where<CRActivity.typeEqual<ActivityTypes.fType>,                        Or<CRActivity.typeEqual<ActivityTypes.mType>,                            Or<CRActivity.typeEqual<ActivityTypes.pType>>>>>>,                OrderBy<Desc<CRActivity.startDate>>>(this).Select();                        foreach (PXResult<CRActivity> boxedActivity in result)            {                var activity = boxedActivity.GetItem<CRActivity>();                string cutted = StripTagsCharArray(activity.Body);                PXDatabase.Update<CRActivity>(                    new PXDataFieldRestrict<CRActivity.noteID>(activity.NoteID),                    new PXDataFieldAssign<CRActivityExt.usrBodyWithoutHtml>(cutted)                );            }            var result2 = new PXSelectReadonly<CRActivity,                Where2<Where<CRActivity.bAccountIDEqual<Current<BAccount.bAccountID>>>,                    And<Where<CRActivity.typeEqual<ActivityTypes.fType>,                        Or<CRActivity.typeEqual<ActivityTypes.mType>,                            Or<CRActivity.typeEqual<ActivityTypes.pType>>>>>>,                OrderBy<Desc<CRActivity.startDate>>>(this).Select();            return result2;        } #endregion

#region ClearHtml        public static string StripTagsCharArray(string source)        {            string result2 = "";            if (source == null)            {                source = "";            }            HtmlDocument htmlDoc = new HtmlDocument();            htmlDoc.LoadHtml(source);            if (htmlDoc.ParseErrors != null && htmlDoc.ParseErrors.Count() > 0)            {                // Handle any parse errors as required                result2 = "Text not correct";            }            else            {                if (htmlDoc.DocumentNode != null)                {                    HtmlAgilityPack.HtmlNode bodyNode = htmlDoc.DocumentNode.SelectSingleNode("//body");                    if (bodyNode != null)                    {                        result2 = htmlDoc.DocumentNode.SelectSingleNode("//body").InnerText;                    }                    else                    {                        result2 = source;                    }                }            }            return result2;        }    }    #endregion

Here I use 2 standart PXSelect from tables BAcount and CRActivity, and my IEnumerable method where I change text - strip html tags (StripTagsCharArray), and also use PXDatabase.Update for write text to column "UsrBodyWithoutHtml". 

About this(PXDatabase.Update, PXDataFieldRestrict<>, Where2) and how it work read in my blog.

One more interesting part is Method StripTagsCharArray. That method uses external library HtmlAgilityPack in order to strip html tags from text.

      2. AC601000.aspx page

After I go to web site and in folder Page create new folder "AC". In this folder I copy and paste structure of another .aspx page (becouse .aspx page include .aspx.cs file), rename to "AC601000" (Screenshot 6).