Blog

Here I document some issues for searching of which I spent more then 15 minutes

  • How to add User defined fields to any entity in Acumatica

    Hello everybody,

    today I want to describe how at code level you can add User defined fields in Acumatica to any entity. Sequence will be this:

    In PXDataSource add attribute EnableAttributes. It may look like this:

    <px:PXDataSource EnableAttributes="true" ID="ds" 

    2. For target entity create table with same name, but with suffix KvExt. Query for creation of such a table may look like this:

    CREATE TABLE [[TargetTable]KvExt](

    [CompanyID] [int] NOT NULL,

    [RecordID] [uniqueidentifier] NOT NULL,

    [FieldName] [varchar](50) NOT NULL,

    [ValueNumeric] [decimal](28, 8) NULL,

    [ValueDate] [datetime] NULL,

    [ValueString] [nvarchar](256) NULL,

    [ValueText] [nvarchar](max) NULL,

      … more

  • How to unit test SOOrderEntry extension in Acumatica

    Hello everybody,

    today I want to show to unit test, and I mean really unit test SOOrderEntry graph extnesion in Acumatica with XUnit.

    In order to achieve it, you'll need following steps:

    Create .Net Class library

    Reference xUnit

    Create public class that inherits from TestBase class

    Add override ResisterServices 

    Create something like PrepareGraph with usage of TestBase.Setup class

    Write your mehtods.

    I will not describe how steps 1 - 4 may look, as it is pretty obvious, but step 5 and 6 at C# level may look like this:

    public class SOOrderEntrySDExtTests : TestBase

    {

        protected IPXCurrencyService CurrencyService;

        protected IFinPeriodRepository FinPeriodService; … more

  • 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:

    Install Neor Profile SQL.

    In your Acumatica web.config make following change:

      < … more

  • How to point MySQL to another port then 3306

    Hello everybody,

    today I want to describe how to point to non standard port in My SQL for Acumatica.

    Below goes fragment from my Web.config

        <remove name="ProjectX" />

        <add name="ProjectX" providerName="System.Data.SqlClient" connectionString="Server=localhost;Port=4040;Database=PXProjectionMySql2;Uid

    you can use this knowledge for having multiple MySQL instances on the same machine and for catching generated SQL queries. more

  • How to install MySQL proxy

    Hello everybody,

    quick post on how to install MySQL proxy. You may be puzzled why it is needed? Because MySQL proxy allows you to track all generated SQL by MySQL. As of now, you can catch generated SQL for SQL server with SQL Server Request Profiler. But in order to achieve it for MySQL, you'll need to use MySQL proxy.

    So, first step will be download MySQL proxy archive and unzip it to some folder on your drive. 

    Then in command prompt ( not in powershell ) execute something like this:

    sc create "Proxy" DisplayName="MySQL Proxy" start="auto" binPath="d:\Install\mysql-proxy-0.8.5-windows-x86-32bit\bin\mysql-proxy-svc.exe"

    And then this:

    net start proxy

    Those two commands will allow … more

  • How to use FBQL in PXProjection

    Hello everybody,

    Recnetly I had a need to create PXProjection and wanted to use in declaration of it not SelectJoin, but FBQL SelectFrom with combination of InnerJoin. Finally I've got something like this:

    [PXProjection(typeof(SelectFrom<SOOrder>.InnerJoin<SOLine>.On<SOOrder.orderType.

        IsEqual<SOLine.orderType>.And<SOOrder.orderNbr.IsEqual<SOLine.orderNbr>>>))]

    public class SalesOrderLines

    {

    }

    Nothing fancy, but during initial coding error messages were a bit confusing

    more

  • How to use PXDBScalar with PXProjection

    Hello everybody,

    today I want to write a few words about how to use PXDBscalar in connection with PXProjection and is it possible at all.

    First of all want to say that it's defientely a possiblity. Take a look on this sample of implementation:

    [Serializable]

    [PXProjection(typeof(Select2<FABookBalance,

    LeftJoin<FABookHistory, On<FABookHistory.assetID, Equal<FABookBalance.assetID>,

    And<FABookHistory.bookID, Equal<FABookBalance.bookID>,

    And<FABookHistory.finPeriodID, Equal<IsNull<FABookBalance.currDeprPeriod, FABookBalance.lastPeriod>>>>>,

    InnerJoin<FABook, On<FABook.bookID, Equal<FABookBalance.bookID>>>>>), new Type[] { typeof(FABookBalance) })]

    [PXCacheName(Messages. … more

  • how to make redirects in Acumatica and popup warning

    Hello everybody,

    today I want to write a newer post about how to give an opportunity to user go to some link from Acumatica. As I've mentioned in https://blog.zaletskyy.com/types-of-redirects-in-acumatica, redirections are implemented as exception. Let consider next simple code, by which you can implement it:

    In your page (in our example this is SO301000) a button will appear:

    If user will click to this button, then he'll immediately receive requested data right in main window:

    This approach has the disadvantage - the user will lose all unsaved data. So let's change our code to warn the user about it:

      Now, after the user clicks on the "Learn More" button, a popup window … more

  • When to use PXConnectionScope, and when to avoid it

    Hello everybody,

    today I want to leave a very short note-reminder. Mainly for myself, but maybe someone else may benefit from it also. 

    In Acumatica there is wonderful scope: PXConnectionScope. It is needed for the following situation: if you need to raw data from database eliminating cache. For this purposes is also fine PXSelectReadOnly, but if you want to open a new connection, and read from db raw data, then it's better to read them through PXConnectionScope. 

    Also want to mention few rakes, which stolen from me enormous amount of time.

    If you want to read some data during RowSelecting or FieldSelecting, potentially it may be a good idea to use PXConnectionScope

    If you want to … more

  • Getting Combo-box values set for REST API

    Hello everybody,

    today I want to share idea on how to get getting Combo-box values set for REST API.

    As usually values of comboboxes values are just hardcoded in web api calls, but sometimes it may be necessary to load them from Rest API, for example for cases if you want to target multiplve versions of Acumatica. For such a purpose I'd suggest to create Graph, which via reflection will read values from dlls. Quite similar to what Acumatica team does. 

    Below goes source code of graph, which via reflection loads data:

    using PX.Data;

    using System;

    using System.Collections;

    using System.Reflection;

    namespace LA

    {

        public class ListAttributesInq : PXGraph<ListAttributesInq>

         … more

General information

Greetings! Here I publish everything I found useful or beneficial for me and maybe others. For some sql samples I'm using wiseowl samples. 

Contact information

If you want to contact me more on any subject printed at this web stie, you can email me: zaljur1989(at)gmail.com

Job information

If to speak in general, I'm developer. For now I work as Solutions Architect in company Kensium.