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:

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 will appear on the screen with a warning about the need to save the data:

It mach better. Warning is needed when using "Redirect0:" -"Redirect1:" and the link opens directly in the work window. But it is not necessary when using "Redirect3:" - "Redirect8:", because in this cases unsaved data is not lost, since the link is opened on another page (when to use "Redirect3:" - "Redirect5:") or in a popup (when to use "Redirect7:" and "Redirect8:"):

Summary

As we see, in Acumatica it is easy to direct the user to the link he wants.

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.

  1. If you want to read some data during RowSelecting or FieldSelecting, potentially it may be a good idea to use PXConnectionScope
  2. If you want to read some data during overriding action Persist, it's better to avoid PXConnectionScope, as you can get Connection time out error message because of dead locking.

And one more point, sometime it may be needed to increase timeout. web.config sample shows how to achieve it:

      <httpRuntime executionTimeout="1500" requestValidationMode="2.0" maxRequestLength="1048576" />

Summary

If you need raw data on RowSelecting, go ahead with PXConnectionScope. If you need raw data on Persist, consider PXSelectReadOnly.

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>
    {
        public PXCancel<ClassFilter> Cancel;
 
        public PXFilter<ClassFilter> Filter;
 
        public override bool IsDirty
        {
            get
            {
                return false;
            }
        }
 
        public ListAttributesInq()
        {
            Records.Cache.AllowInsert = false;
            Records.Cache.AllowDelete = false;
            Records.Cache.AllowUpdate = false;
 
            Actions["Process"].SetVisible(false);
            Actions["ProcessAll"].SetVisible(false);
            Actions["Schedule"].SetVisible(false);
        }
 
        [PXFilterable]
        [PXVirtualDAC]
        public PXFilteredProcessing<KeyValueRecordClassFilter> Records;
 
        protected IEnumerable records()
        {
            //var row = new ClassFilter { ClassName = "PX.Objects.CR.CRMSourcesAttribute" };
            var row = Filter.Current;
            if (row != null && !string.IsNullOrWhiteSpace(row.ClassName))
            {
                var type = Type.GetType(row.ClassName) ??
                            Type.GetType(row.ClassName + ", PX.Objects");
 
                if (type != null)
                    switch (type.BaseType.Name)
                    {
                        case "PXIntListAttribute":
                            {
                                int[] values;
                                string[] labels;
                                GetRecords(typeout valuesout labels);
 
                                for (int i = 0; i < values.Length; i++)
                                    yield return new KeyValueRecord { Key = values[i].ToString(), UiValue = labels[i] };
                                break;
                            }
                        case "PXStringListAttribute":
                            {
                                string[] valueslabels;
                                GetRecords(typeout valuesout labels);
 
                                for (int i = 0; i < values.Length; i++)
                                    yield return new KeyValueRecord { Key = values[i], UiValue = labels[i] };
                                break;
                            }
                    }
            }
        }
 
        private void GetRecords<T>(Type typeout T[] valuesout string[] labels)
        {
            var obj = Activator.CreateInstance(type);
            var flags = BindingFlags.NonPublic | BindingFlags.Instance;
 
            values = type.GetField("_AllowedValues"flags).GetValue(objas T[];
            labels = type.GetField("_AllowedLabels"flags).GetValue(objas string[];
        }
    }
}

 

It has few features:

  1. Method get records, which creates instance, and fills values
  2. Method records, which is delegate overload, and which distinguishes between PXIntListAttribute and PXStringListAttribute.
  3. yield return purpose of it is to be pageble ( for page opening it doesn't have big value ) and getting all records at once
  4. As entry, it is needed to enter class with it's namespace. Like PX.Objects.CR.CRMSourcesAttribute, and not like CRMSources

Aspx source code looks like this:

<%@ Page Language="C#" MasterPageFile="~/MasterPages/FormDetail.master" AutoEventWireup="true" ValidateRequest="false" CodeFile="LA401000.aspx.cs" Inherits="Page_LA401000" Title="Untitled Page" %>
<%@ MasterType VirtualPath="~/MasterPages/FormDetail.master" %>
 
<asp:Content ID="cont1" ContentPlaceHolderID="phDS" Runat="Server">
    <px:PXDataSource ID="ds" runat="server" Visible="True" Width="100%"
        TypeName="LA.ListAttributesInq"
        PrimaryView="Filter">
		<CallbackCommands></CallbackCommands>
	</px:PXDataSource>
</asp:Content>
<asp:Content ID="cont2" ContentPlaceHolderID="phF" Runat="Server">
    <px:PXFormView SkinID="" ID="form" runat="server" DataSourceID="ds" DataMember="Filter" Width="100%" Height="" AllowAutoHide="false">
		<Template>
			<px:PXLayoutRule ID="PXLayoutRule1" runat="server" StartRow="True"></px:PXLayoutRule>
			<px:PXTextEdit CommitChanges="True" runat="server" ID="CstPXTextEdit1" DataField="ClassName" ></px:PXTextEdit></Template>
	</px:PXFormView>
</asp:Content>
<asp:Content ID="cont3" ContentPlaceHolderID="phG" Runat="Server">
    <px:PXGrid AllowFilter="True" AllowPaging="True" AllowSearch="True" SyncPosition="True" ID="grid" runat="server" DataSourceID="ds" Width="100%" Height="150px" SkinID="Inquire" AllowAutoHide="false">
		<Levels>
			<px:PXGridLevel DataMember="Records">
			    <Columns>
				<px:PXGridColumn DataField="Key" Width="120" ></px:PXGridColumn>
				<px:PXGridColumn DataField="UiValue" Width="180" ></px:PXGridColumn></Columns>
			
				<RowTemplate>
					<px:PXTextEdit runat="server" ID="CstPXTextEdit2" DataField="Key" ></px:PXTextEdit>
					<px:PXTextEdit runat="server" ID="CstPXTextEdit3" DataField="UiValue" ></px:PXTextEdit></RowTemplate></px:PXGridLevel>
		</Levels>
		<AutoSize Container="Window" Enabled="True" MinHeight="150" ></AutoSize>
		<ActionBar >
		</ActionBar>
	</px:PXGrid>
</asp:Content>

One more necessary DAC Class ClassFilter looks like this:

using PX.Data;
using System;
 
namespace LA
{
    [Serializable]
    public class ClassFilter : IBqlTable
    {
        #region ClassName
        public abstract class className : PX.Data.IBqlField
        {
        }
        [PXString(50, IsUnicode = true, InputMask = "")]
        [PXUIField(DisplayName = "Class Name")]
        public string ClassName { getset; }
        #endregion
    }
}

 

DAC class KeyValueRecord looks very trivial in this case:

using PX.Data;
using System;
 
namespace LA
{
    [Serializable]
    public class KeyValueRecord : IBqlTable
    {
        #region Key
        public abstract class key : PX.Data.IBqlField
        {
        }
        [PXString(10, IsUnicode = true, InputMask = "")]
        [PXUIField(DisplayName = "Key")]
        public string Key { getset; }
        #endregion
        #region Value
        public abstract class uiValue : PX.Data.IBqlField
        {
        }
        [PXString(50, IsUnicode = true, InputMask = "")]
        [PXUIField(DisplayName = "Value")]
        public string UiValue { getset; }
        #endregion
    }
}

And the end result looks like this:

Summary

If you need to read something from Attributes, just use reflection.

Create payment proc

Hello everybody,

today I want to write a few words about method CreatePaymentProc in graph SOOrderEntry.

Among different features of this method, want to describe that it have for some reason out parameter! Take a look on it's declaration:

public virtual void CreatePaymentProc(SOOrder orderout PXGraph targetstring paymentType = ARPaymentType.Payment)
{

 

as you see, it accepts as a parameter PXGraph. Inside of open part of Acumatica source code there is only one more file, which has similar staff: ServiceOrderCore. Take a look on it:

public static void CreatePrepayment(FSServiceOrder fsServiceOrderRowFSAppointment fsAppointmentRowout PXGraph targetstring paymentType = ARPaymentType.Payment)
{

But similarities not finished. Take notice how those methods utilize PXGraph target. First goes ServiceOrderCore:

ARPaymentEntry graphARPaymentEntry = PXGraph.CreateInstance<ARPaymentEntry>();
target = graphARPaymentEntry;

And second one goes SOOrderEntry:

public virtual void CreatePaymentProc(SOOrder orderout PXGraph targetstring paymentType = ARPaymentType.Payment)
{
	ARPaymentEntry docgraph = PXGraph.CreateInstance<ARPaymentEntry>();
	target = docgraph;

And now you'd probably ask, and so what? How can I use it? One of the very useful ways would be getting graph of ARPaymentEntry after calling of the method. 

In one of my projects it was needed to call method CreatePaymentProc, and persisting of payment but without showing dialog window of creation of Payment. Below goes code how I've achieved it:

 
PXLongOperation.StartOperation(Base, delegate()
{
    PXGraph target;
    Base.CreatePaymentProc(Base.Document.Current, out target);
    var arPaymentEntry = target as ARPaymentEntry;
    //here some additional manipulations may be added to in memory created payment
    arPaymentEntry.Persist();
});

Summary

If you have a need to call method CreatePaymentProc, and then massage data after execution of that method, jast ignore showing pop up window and with help of type casting and additional changes make it. Afterwards call Persist.