Master details step by step

Hello everybody,

today I want to share how to implement master detail form step by step starting from small. So, recently I was in situation when I created form, generated DAC, created aspx page, and got some errors. When I asked support for help, they informed me that they will help me only one hour for free, and another hours for $$$$. I need to say that I appreciate communication with Acumatica support team, and I even ready to pay to them, but should admit it's not always easy to me. 

So I decided to make another option. 

1. Create very simple tables

2. Create very simple DAC

3. Add complication logic.

Few words what means very simple. In my terms very simple is Table with key and fields cratedbyid, lastmodifiedbyid, etc. The same structure of DAC. So let's go on. 

Here it goes first table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PRPayroll](
    [RefNbr] [nvarchar](8) NOT NULL,
    [CompanyID] [int] NOT NULL,
    [tstamp] [timestamp] NULL,
    [CreatedByID] [uniqueidentifier] NOT NULL,
    [CreatedByScreenID] [char](8) NOT NULL,
    [CreatedDateTime] [datetime] NOT NULL,
    [LastModifiedByID] [uniqueidentifier] NOT NULL,
    [LastModifiedByScreenID] [char](8) NOT NULL,
    [LastModifiedDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_PRPayroll_1] PRIMARY KEY CLUSTERED 
(
    [RefNbr] ASC,
    [CompanyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Second table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PRPayrollDetails](
    [CompanyID] [int] NOT NULL,
    [PRPayrollDetailID] [nvarchar](8) NOT NULL,
    [PRPayrollID] [nvarchar](8) NULL,
 CONSTRAINT [PK_PRPayrollDetails] PRIMARY KEY CLUSTERED 
(
    [CompanyID] ASC,
    [PRPayrollDetailID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Next step is of course to generate DAC classes. After generating DAC classes copy/paste from coderepository.xml fields [PXDBCreatedByID()], [PXDBCreatedByScreenID()], ...., [PXDBTimestamp()].

Classes which you'll see should be the following:

namespace DS
{
    using PX.Objects.AP;
    using PX.Objects.CM;
    using PX.Objects.CS;
    using PX.Objects.EP;
    using PX.Objects.GL;
    using System;
    using PX.Data;
    
    [System.SerializableAttribute()]
    public class PRPayroll : PX.Data.IBqlTable
    {
        #region RefNbr
        public abstract class refNbr : PX.Data.IBqlField
        {
        }
        protected string _RefNbr;
        [PXDBString(8, IsKey = true, IsUnicode = true)]
        [PXDefault()]
        [PXUIField(DisplayName = "Reference Nbr.")]
        [PXSelector(typeof(Search<PRPayroll.refNbr>), new Type[] { typeof(PRPayroll.refNbr) })]
        public virtual string RefNbr
        {
            get
            {
                return this._RefNbr;
            }
            set
            {
                this._RefNbr = value;
            }
        }
        #endregion
        #region CreatedByID
        public abstract class createdByID : PX.Data.IBqlField
        {
        }
        protected Guid? _CreatedByID;
        [PXDBCreatedByID()]
        public virtual Guid? CreatedByID
        {
            get
            {
                return this._CreatedByID;
            }
            set
            {
                this._CreatedByID = value;
            }
        }
        #endregion
        #region CreatedByScreenID
        public abstract class createdByScreenID : PX.Data.IBqlField
        {
        }
        protected string _CreatedByScreenID;
        [PXDBCreatedByScreenID()]
        public virtual string CreatedByScreenID
        {
            get
            {
                return this._CreatedByScreenID;
            }
            set
            {
                this._CreatedByScreenID = value;
            }
        }
        #endregion
        #region CreatedDateTime
        public abstract class createdDateTime : PX.Data.IBqlField
        {
        }
        protected DateTime? _CreatedDateTime;
        [PXDBCreatedDateTime()]
        public virtual DateTime? CreatedDateTime
        {
            get
            {
                return this._CreatedDateTime;
            }
            set
            {
                this._CreatedDateTime = value;
            }
        }
        #endregion
        #region LastModifiedByID
        public abstract class lastModifiedByID : PX.Data.IBqlField
        {
        }
        protected Guid? _LastModifiedByID;
        [PXDBLastModifiedByID()]
        public virtual Guid? LastModifiedByID
        {
            get
            {
                return this._LastModifiedByID;
            }
            set
            {
                this._LastModifiedByID = value;
            }
        }
        #endregion
        #region LastModifiedByScreenID
        public abstract class lastModifiedByScreenID : PX.Data.IBqlField
        {
        }
        protected string _LastModifiedByScreenID;
        [PXDBLastModifiedByScreenID()]
        public virtual string LastModifiedByScreenID
        {
            get
            {
                return this._LastModifiedByScreenID;
            }
            set
            {
                this._LastModifiedByScreenID = value;
            }
        }
        #endregion
        #region LastModifiedDateTime
        public abstract class lastModifiedDateTime : PX.Data.IBqlField
        {
        }
        protected DateTime? _LastModifiedDateTime;
        [PXDBLastModifiedDateTime()]
        public virtual DateTime? LastModifiedDateTime
        {
            get
            {
                return this._LastModifiedDateTime;
            }
            set
            {
                this._LastModifiedDateTime = value;
            }
        }
        #endregion
        #region tstamp
        public abstract class Tstamp : PX.Data.IBqlField
        {
        }
        protected byte[] _tstamp;
        [PXDBTimestamp()]
        public virtual byte[] tstamp
        {
            get
            {
                return this._tstamp;
            }
            set
            {
                this._tstamp = value;
            }
        }
        #endregion
    }
}

namespace DS
{
    using System;
    using PX.Data;
    
    [System.SerializableAttribute()]
    public class PRPayrollDetails : PX.Data.IBqlTable
    {
        #region PRPayrollDetailID
        public abstract class pRPayrollDetailID : PX.Data.IBqlField
        {
        }
        protected string _PRPayrollDetailID;
        [PXDBString(8, IsKey = true, IsUnicode = true)]
        [PXDefault()]
        [PXUIField(DisplayName = "PRPayrollDetailID")]
        public virtual string PRPayrollDetailID
        {
            get
            {
                return this._PRPayrollDetailID;
            }
            set
            {
                this._PRPayrollDetailID = value;
            }
        }
        #endregion
        #region PRPayrollID
        public abstract class pRPayrollID : PX.Data.IBqlField
        {
        }
        protected string _PRPayrollID;
        [PXDBString(8, IsUnicode = true)]
        [PXUIField(DisplayName = "PRPayrollID")]
        public virtual string PRPayrollID
        {
            get
            {
                return this._PRPayrollID;
            }
            set
            {
                this._PRPayrollID = value;
            }
        }
        #endregion
        #region CreatedByID
        public abstract class createdByID : PX.Data.IBqlField
        {
        }
        protected Guid? _CreatedByID;
        [PXDBCreatedByID()]
        public virtual Guid? CreatedByID
        {
            get
            {
                return this._CreatedByID;
            }
            set
            {
                this._CreatedByID = value;
            }
        }
        #endregion
        #region CreatedByScreenID
        public abstract class createdByScreenID : PX.Data.IBqlField
        {
        }
        protected string _CreatedByScreenID;
        [PXDBCreatedByScreenID()]
        public virtual string CreatedByScreenID
        {
            get
            {
                return this._CreatedByScreenID;
            }
            set
            {
                this._CreatedByScreenID = value;
            }
        }
        #endregion
        #region CreatedDateTime
        public abstract class createdDateTime : PX.Data.IBqlField
        {
        }
        protected DateTime? _CreatedDateTime;
        [PXDBCreatedDateTime()]
        public virtual DateTime? CreatedDateTime
        {
            get
            {
                return this._CreatedDateTime;
            }
            set
            {
                this._CreatedDateTime = value;
            }
        }
        #endregion
        #region LastModifiedByID
        public abstract class lastModifiedByID : PX.Data.IBqlField
        {
        }
        protected Guid? _LastModifiedByID;
        [PXDBLastModifiedByID()]
        public virtual Guid? LastModifiedByID
        {
            get
            {
                return this._LastModifiedByID;
            }
            set
            {
                this._LastModifiedByID = value;
            }
        }
        #endregion
        #region LastModifiedByScreenID
        public abstract class lastModifiedByScreenID : PX.Data.IBqlField
        {
        }
        protected string _LastModifiedByScreenID;
        [PXDBLastModifiedByScreenID()]
        public virtual string LastModifiedByScreenID
        {
            get
            {
                return this._LastModifiedByScreenID;
            }
            set
            {
                this._LastModifiedByScreenID = value;
            }
        }
        #endregion
        #region LastModifiedDateTime
        public abstract class lastModifiedDateTime : PX.Data.IBqlField
        {
        }
        protected DateTime? _LastModifiedDateTime;
        [PXDBLastModifiedDateTime()]
        public virtual DateTime? LastModifiedDateTime
        {
            get
            {
                return this._LastModifiedDateTime;
            }
            set
            {
                this._LastModifiedDateTime = value;
            }
        }
        #endregion
        #region tstamp
        public abstract class Tstamp : PX.Data.IBqlField
        {
        }
        protected byte[] _tstamp;
        [PXDBTimestamp()]
        public virtual byte[] tstamp
        {
            get
            {
                return this._tstamp;
            }
            set
            {
                this._tstamp = value;
            }
        }
        #endregion
    }
}

The next step will be writing joins between master and detail. 

Below goes what I wrote for class PRPayrollDetails

#region PRPayrollID
        public abstract class pRPayrollID : PX.Data.IBqlField
        {
        }
        protected string _PRPayrollID;
        
        [PXDBString(8, IsUnicode = true)]
        [PXDBDefault(typeof(PRPayroll.refNbr))]
        [PXParent(typeof(Select<PRPayroll, Where<PRPayroll.refNbr, Equal<Current<PRPayrollDetails.pRPayrollID>>>>))]
        public virtual string PRPayrollID
        {
            get
            {
                return this._PRPayrollID;
            }
            set
            {
                this._PRPayrollID = value;
            }
        }
        #endregion

Next step goes for Graph. Below goes implementation of graph:

public class PayRollManager : PXGraph<PayRollManager, PRPayroll>
    {
        public PXSelect<PRPayroll> PayRolls;
        public PXSelectJoin <PRPayrollDetails, InnerJoin<PRPayroll, On<PRPayrollDetails.pRPayrollDetailID, Equal<Current<PRPayroll.refNbr>>>>> PayRollsDetails;

    }

The next step is formatting page. Below goes my code:

<%@ Page Language="C#" MasterPageFile="~/MasterPages/FormDetail.master" AutoEventWireup="true" ValidateRequest="false" CodeFile="PR301000_.aspx.cs" Inherits="Page_PR301000" 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%" PrimaryView="PayRolls" SuspendUnloading="False" TypeName="DS.PayRollManager">
    </px:PXDataSource>
</asp:Content>
<asp:Content ID="cont2" ContentPlaceHolderID="phF" Runat="Server">
    <px:PXFormView ID="form" runat="server" DataSourceID="ds" Width="100%" Height="170px" DataMember="PayRolls" TabIndex="11400">
        <Template>
            <px:PXSelector ID="edRefNbr" runat="server" CommitChanges="True" DataField="RefNbr">
            </px:PXSelector>
        </Template>
    </px:PXFormView>
</asp:Content>
<asp:Content ID="cont3" ContentPlaceHolderID="phG" Runat="Server">
         <px:PXGrid ID="grid" runat="server" DataSourceID="ds" AdjustPageSize="Auto" AllowPaging="True" Width="100%" Height="250px" SkinID="Details" TabIndex="13000">
        <Levels>
            <px:PXGridLevel DataMember="PayRollsDetails">
                <RowTemplate>
                    <px:PXMaskEdit ID="edPRPayrollDetailID" runat="server" DataField="PRPayrollDetailID">
                    </px:PXMaskEdit>
                    <px:PXSelector ID="edPRPayroll__RefNbr" runat="server" DataField="PRPayroll__RefNbr">
                    </px:PXSelector>
                </RowTemplate>
                <Columns>
                    <px:PXGridColumn DataField="PRPayrollDetailID">
                        <ValueItems MultiSelect="False">
                        </ValueItems>
                    </px:PXGridColumn>
                    <px:PXGridColumn DataField="PRPayroll__RefNbr">
                        <ValueItems MultiSelect="False">
                        </ValueItems>
                    </px:PXGridColumn>
                </Columns>
            </px:PXGridLevel>
        </Levels>
        <AutoSize Container="Window" Enabled="True" MinHeight="220" />
        <ActionBar ActionsText="True">
            <CustomItems>
                <px:PXToolBarButton Text="Update Salary" Key="cmdUpdateSalaryGrid">
                    <AutoCallBack Command="UpdateSalaryGrid" Target="ds" />
                    <PopupCommand Command="Cancel" Target="ds" />
                </px:PXToolBarButton>
                <px:PXToolBarButton Text="Create Pay Slip" >
                    <AutoCallBack Command="CreatePaySlip" Target="ds" />
                </px:PXToolBarButton>
                <px:PXToolBarButton Text="Calculate" >
                    <AutoCallBack Command="Calculate" Target="ds" />
                </px:PXToolBarButton>
                <px:PXToolBarButton Text="Validate">
                    <AutoCallBack Command="Validate" Target="ds" />
                </px:PXToolBarButton>
                <px:PXToolBarButton Text="View Doucment" >
                    <AutoCallBack Command="ViewDocument" Target="ds" />
                </px:PXToolBarButton>
            </CustomItems>
        </ActionBar>
    </px:PXGrid>
</asp:Content>

I ommited explanation how to use "Edit content layout" screen as it's out of the topic. 

So, if to open page, then you'll be able to notice possibility to save. Try to enter some values there, monitor db, and you'll see that staf goes saved in db, but with one limitation. For some unknown reason staf is not loaded. After watching more carefull, I noticed what is the source of problem, after instead of 

        public PXSelectJoin <PRPayrollDetails, InnerJoin<PRPayroll, On<PRPayrollDetails.pRPayrollDetailID, Equal<Current<PRPayroll.refNbr>>>>> PayRollsDetails;

I wrote

        public PXSelectJoin <PRPayrollDetails, InnerJoin<PRPayroll, On<PRPayrollDetails.pRPayrollID, Equal<Current<PRPayroll.refNbr>>>>> PayRollsDetails;

and rebuilding I got working grid, with working switching.

Summary. 

If you need automatic binding between your master and details, you need

1. PXParent and PXDBDefault attributes in your Details class.

2. Master and detail DAC's should have IsKeyAttribute=true, 

3. Write correct PXSelectJoin

My next step will be adding nesessary columns to grid

Download

Saving changes are not permitted. Because the changes you have made require the listed tables to be dropped and re-created

Small hack.

If you modify table in Microsoft SQL Server Management Studio, it sometimes says something like 

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option Prevent saving changes that require the table to be re-created. 

Do you know where mentioned option is hidden?

Tools -> Options -> Designers -> Table and DAtabase Designers. Find there checkbox Prevent saving changes that require table re-creation, remove checkbox and enjoy results.

Lua for FXCM Trade Station

Hello everybody,

today I will share with you adventure of learning writing something for Trade Station from FXCM and some other trading companies.

I got one request from client to write for him expert advisor, and it should be for Trade Station in lua, and not in mql4.

I can't say that I was happy, but customer is sacred think, so I started.

Initially I read the manual and realized that lua is simple language with C like syntax. AFAIK C, I decided to look how to develop EA.

So I created file "matrade.lua" which was empty. 

Then in trade station I made the following:

Pressed at button import and pointed to my file:

and as result got error message

For me it meant following. In some way I need to add function Init to code of my strategy. I digged in the folder of fxcm trade station and found interesting folder which had name Strategies, and there I found file strategies.cab. When I looked inside of it, found interesting list of files, and among them was file macross.lua.

as you can see from screenshot there was function Init(), and I added it to my file and tried to open again. Imagine my surprise, when Trading Station notified me that my strategy was installed. Of course, with only function Init which is empty my strategy is useless, but now I can continue to look into existing code and develop my strategy.

Acumatica PXSplitContainer

Hello,

I want to share some discoveries about attributes of PXSplitContainer.

<px:PXSplitContainer runat="server" ID="sp1" SplitterPosition="300" Height="100px">

SplitterPosition="300" means that first item will have height 300 pixels

Height="100px" I didn't notice any changes at page, so I have no idea, why on Earth it is.

CreatedByID cannot be null in Acumatica

Hello everybody,

today I want to share how to cope with error message CreatedByID cannot be null and CreatedByScreenID cannot be null, and so on. In order to get rid of

those error messages use attributes [PXDBCreatedByID], [PXDBCreatedByScreenID],

 [PXDBCreatedDateTime], [PXDBLastModifiedByID], [PXDBLastModifiedByScreenID], 

 [PXDBLastModifiedDateTime], [PXDBTimestamp]. 

If somebody has a question, how I got such error message, the answer is simple. DAC generator generated those fields for me without those attributes, so only with the help of reflector I realized how to fix my issue.

CompnayID in tables and DAC in Acumatica

Hello everybody,

today just short notice of DAC and tables. If you create new functionality in Acumatica and look for creating tables, you should know, that all tables which you'll create should have column CompnayID, and that column shouldn't be reflected in DAC.

Cannot insert explicit value for identity column in table when identity_insert is set to off in Acumatica

Hello everybody,

today I received the following error message in Acumatica:

Cannot insert explicit value for identity column in table when identity_insert is set to off.

After executing SQL

SET IDENTITY_INSERT table off

problem wasn't solved, so I needed to search deeper. After attaching with SQL Profiler, I noticed that Acumatica generated SQL, which tried to insert null into key field. Then was time to look in manual. 

And then I noticed that for key fields statement IsKey = true is not used. Instead is used 

[PXDBIdentity]

So I replaced [PXDBInt(IsKey = true)] with [PXDBIdentity] and problem disappeared.

GoFirstRecord and Configuration screens in Acumatica

Hello everybody,

today I want to share with you one of my mistakes. 

Few days ago I started to develop Configuration screen, or screen which should end at xx101000. After I completed it and passed for testing I faced a challenge. The page should display only the first record. So I implemented some logic which selected the first record. After that I re-read one of the manuals and found that DataSet have property PageLoadBehavior which can be set to GoFirstRecord!!!!

After I removed my code which loaded first record, and added beforementioned property my configuration screen behaved as really configuration screeen. 

Saving complex objects in Acumatica

Hello everybody,

today I want to share one nesessary step which is needed for graph in order to save graph which has more then one DAC ( data access class ).

In my case I have DAC PRPayRoll, which is joined with classes PRPayrollDetails, PRPaySlip, PRTran.

In that case it is needed to mention in graph declaration primary dac.

So instead of writing 

public class PayRollManager : PXGraph<PayRollManager>
I wrote
public class PayRollManager : PXGraph<PayRollManager, PRPayroll>

Where PRPayroll is primary DAC. Or if to quote Acumatica manual PRPayroll is primary DAC for business logic container

Undefined: Cannot read property "length" of undefined

Hello everybody.

Today I want to share how to deal with Acumatica error message

undefined: Cannot read property "length" of undefined.

If you face it, you need to know that some members of your DAC need attribute either PXInt or PXDbInt or something similar. 

One more detail, DAC should have at least one IsKey = true property