Loading ...

Master Details Step By Step

15 July 2014

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

 

6 Comments

  • Sam said 2 years ago

    Hi Yuri,

    It is really nice article for master child relationship between two separate table.

    I need your help for one of my technical requirement.

    I need to design entry screen with help of grid. First 3-4 columns will come for Master table which has fixed number of rows. Additionally I want to capture user inputs in multiple columns which I need to add in this grid which are defined in another table (Transaction Table).

    When we click on + button, it should load grid with master table. Upon click on Save button it should store values in Transaction table only with user inputed values.

    How I can implement this.

    Waiting for your reply with your expertise comments.

  • docotor said 2 years ago

    yes Sam, you can create it. I've sent you personal email

  • angie said 13 months ago

    Hi Yuri

    I can not save the head and grid records at the same time,

    I want to know why, because I use the master detail as his example and I can not save it, if I save the record at the same time, it is saved in different parts ...

    Could you help me? :(

  • angie said 13 months ago

    Your link is disabled, you can not download your example :(

    Noooo, that is very bad :(

  • angie said 13 months ago

    Hi, could you create articule with example sam's ?

    This is great! :)

    pleaseeee

  • docotor said 13 months ago

    what is sams's?

Tags : ,

Be the first to rate this post

  • Currently 0.0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5