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