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:

  1. 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,
 CONSTRAINT [[TargetTable]KvExt_PK] PRIMARY KEY CLUSTERED 
(
	[CompanyID] ASC,
	[RecordID] ASC,
	[FieldName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
ALTER TABLE [[TargetTable]KvExt] ADD  DEFAULT ((0)) FOR [CompanyID]
GO

just replace [TargetTable] with necessary DAC class. For example I wanted to create User defined fields for entity CovidQuizKvExt:

CREATE TABLE [CovidQuizKvExt](
	[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,
 CONSTRAINT [CovidQuizKvExt_PK] PRIMARY KEY CLUSTERED 
(
	[CompanyID] ASC,
	[RecordID] ASC,
	[FieldName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[CovidQuizKvExt] ADD  DEFAULT ((0)) FOR [CompanyID]
GO

As outcome I've got on my page something like this:

Summary

As you can see, it's very easy from development standpoint to add user defined fields to any Acumatica form

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:

  1. Create .Net Class library
  2. Reference xUnit
  3. Create public class that inherits from TestBase class
  4. Add override ResisterServices 
  5. Create something like PrepareGraph with usage of TestBase.Setup class
  6. 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;
 
    public SOOrderEntryExtTests()
    {
        FinPeriodService = new PX.Objects.Unit.FinPeriodServiceMock();
        CurrencyService = new PX.Objects.Unit.CurrencyServiceMock();
    }
 
    protected override void RegisterServices(ContainerBuilder builder)
    {
        base.RegisterServices(builder);
        builder
            .Register<Func<PXGraphIFinPeriodRepository>>(context
                =>
            {
                return (graph)
                    =>
                {
                    return FinPeriodService;
                };
            });
        builder
            .Register<Func<PXGraphIPXCurrencyService>>(context
                =>
            {
                return (graph)
                    =>
                {
                    return CurrencyService;
                };
            });
    }
 
    private SOOrderEntry PrepareGraph()
    {
        Setup<SOOrderEntry>(
           new SOSetup
           {
               DefaultOrderType = "SC",
               TransferOrderType = "TR",
               ShipmentNumberingID = "SOSHIPMENT",
               ProrateDiscounts = true,
               FreeItemShipping = "S",
               FreightAllocation = "A",
               CreditCheckError = false,
               MinGrossProfitValidation = "W"
           });
        
        
        var graph = PXGraph.CreateInstance<SOOrderEntry>();
        graph.CurrentDocument.Insert(
            new SOOrder()
            {
                OrderType = "SC",
                OrderDesc = "some test desc"
            }
            );
        var graphExtension = graph.GetExtension<SOOrderEntryExt>();
        
 
        return graph;
    }
 
    [Fact]
    public void CheckInsertion()
    {
        var graph = PrepareGraph();
        graph.CurrentDocument.Current.Approved = true;
        graph.CurrentDocument.Update(graph.CurrentDocument.Current);
 
        Assert.Equal(graph.CurrentDocument.Current.CuryID, "USD");
 
    }
}

Few more comments regarding code. 

  1. As of now, in order to give to SOOrderEntry setup classes, you'll need to go from one exception message to another. Not very convenient, but the only available way
  2. RegisterServices was copy/pasted from Github post of Dmitriy Naumov
  3. You may need to take a look on graphExtnesion
  4. Regarding extension fields I suggest to set up them in the cache

Summary

Acumatica team put big amount of efforts in order to add stability to their products. Also they put a lot of efforts for giving line for adding stability to Acumatica by other ISV. It will be a crime not to benefit from it. Crime against customers!

 

 

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

Summary

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!

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.

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 you to have MySQL proxy installed. In my future post I will mention how to track all generated SQL. I hope I will figure it out.

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

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<FABookHistoryOn<FABookHistory.assetIDEqual<FABookBalance.assetID>,
	And<FABookHistory.bookIDEqual<FABookBalance.bookID>,
	And<FABookHistory.finPeriodIDEqual<IsNull<FABookBalance.currDeprPeriodFABookBalance.lastPeriod>>>>>,
	InnerJoin<FABookOn<FABook.bookIDEqual<FABookBalance.bookID>>>>>), new Type[] { typeof(FABookBalance) })]
[PXCacheName(Messages.FABookBalance)]
public partial class FABookBalance : PX.Data.IBqlTable
{

as you see, above goes projection of FABookBalance, FABookHistory, FABook. Three DAC classes. And then take a look on how it can be used in PXDBScalar:

public abstract class classID : PX.Data.BQL.BqlInt.Field<classID> { }
protected Int32? _ClassID;
[PXInt()]
[PXDBScalar(typeof(Search<FixedAsset.classIDWhere<FixedAsset.assetIDEqual<FABookBalance.assetID>>>))]
[PXDefault(typeof(Search<FixedAsset.classIDWhere<FixedAsset.assetIDEqual<Current<FABookBalance.assetID>>>>))] [PXSelector(typeof(Search<FixedAsset.assetID>), SubstituteKey = typeof(FixedAsset.assetCD), CacheGlobal = true, DescriptionField = typeof(FixedAsset.description))] [PXUIField(DisplayName = "Asset Class", Enabled = false)] public virtual Int32? ClassID { get { return this._ClassID; } set { this._ClassID = value; } }

As you see from code sample, it has calculation from totaly another class even: FixedAsset. Pretty cool? 

Take a look on another example:

[Serializable]
    [PXBreakInheritance]
    [PXCacheName(TX.TableName.SODET_PART)]
    [PXProjection(typeof(Select<FSSODet,
                        Where<
                            FSSODet.lineTypeEqual<ListField_LineType_ALL.Inventory_Item>,
                            Or<FSSODet.lineTypeEqual<ListField_LineType_ALL.Comment_Part>,
                            Or<FSSODet.lineTypeEqual<ListField_LineType_ALL.Instruction_Part>>>>>), Persistent = true)]
    public class FSSODetPart : FSSODet
    {

and take notice of PXDBScalar usage inside of that class:

        public new abstract class planType : PX.Data.BQL.BqlString.Field<planType> { }


        [PXDBScalar(typeof(Search<INPlanType.planTypeWhere<INPlanType.inclQtyFSSrvOrdBookedEqual<True>>>))]
        [PXDefault(typeof(Search<INPlanType.planTypeWhere<INPlanType.inclQtyFSSrvOrdBookedEqual<True>>>), PersistingCheck = PXPersistingCheck.Nothing)]         [PXString(SOOrderTypeOperation.orderPlanType.Length, IsFixed = true)]         public override String PlanType         {             get             {                 return this._PlanType;             }             set             {                 this._PlanType = value;             }         }

As you can see from he code sample, PXProjection is built on FSSODet table only, but for calculation of it's value it uses INPlantype table. 

Summary

If to summarize, you can use PXProjection with PXDBScalar. The only word of warning I have is during web api calls, you may potentially face cases when Acumatica team decided to make optimization, and PXDBSCalar can be skipped. There are ways of dealing with skipping of it also with help of attribute PXDependsOnField.

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: