Master-Detail relationship in Acumatica

Hello everybody,

today I want to make a post about Master-Detail relationship between DAC classes in Acumatica. Maybe you already familiar with C# and SQL and know this theme.  But how acomplish this in  Acumatica? Lets consider. 

Suppose that we have table in our database with students. In another table we store detail data about them. First table has name "Student", second - "StudentDetail".

First of all, let we set up master-detail relationship between the Student and StudentDetail data access classes. For this we need to add PXDBDefault attribute to the StudentDetail.FirstName and for StudentDetail.LastName fields:

#region FirstName
       public abstract class firstName : IBqlField { }
       [PXDBString(20, IsKey = true)]
       [PXUIField(DisplayName = "First Name", Visibility = PXUIVisibility.Visible, Visible = false)]
       [PXDefault(typeof(Students.firstName))]
       public virtual string FirstName { getset; }
       #endregion
 
       #region LastName
       public abstract class lastName : IBqlField { }
       [PXDBString(20, IsKey = true)]
       [PXUIField(DisplayName = "Last Name", Visibility = PXUIVisibility.Visible, Visible = true)]
       [PXDefault(typeof(Students.lastName))]
       public virtual string LastName { getset; }
       #endregion

We use here the PXDBDefault attribute for inserting value into the key field of the detail class, which is the key to the master record.
In our exemple two key fields, and for both of them we set PXDBDefault.

Then important add the PXParent attribute to our code:
#region FirstName
       public abstract class firstName : IBqlField { }
       [PXDBString(20, IsKey = true)]
       [PXUIField(DisplayName = "First Name", Visibility = PXUIVisibility.Visible, Visible = false)]
       [PXDefault(typeof(Students.firstName))]
       [PXParent(
           typeof(Select<Students,
               Where<Students.firstName,
                   Equal<Current<StudentDetails.firstName>>>>))]
       public virtual string FirstName { getset; }
       #endregion
 
       #region LastName
       public abstract class lastName : IBqlField { }
       [PXDBString(20, IsKey = true)]
       [PXUIField(DisplayName = "Last Name", Visibility = PXUIVisibility.Visible, Visible = true)]
       [PXDefault(typeof(Students.lastName))]
       [PXParent(
           typeof(Select<Students,
               Where<Students.lastName,
                   Equal<Current<StudentDetails.lastName>>>>))]
       public virtual string LastName { getset; }
       #endregion 

We need here PXParent attribute for cascade deletion of detail records when the master record is deleted. If you dont add it, and will delete some student,
his records will be removed from "Students" table of database, but all his data will stay in "StudentDetails".

Next, we should define the master and detail data views in the graph:
        public PXSelect<Students> StudentPerson;
        public PXSelect<StudentDetails> StudentDetails;
StudentsPerson (master) records the student's first and last name to the database. StudentDetails (details) saving his details.
Next, we need to declare this data views in our UI, so in our .aspx page set PrimaryView to StudentsPerson as follow:
<asp:Content ID="cont1" ContentPlaceHolderID="phDS" Runat="Server">
    <px:PXDataSource ID="ds" runat="server" Visible="True" Width="100%"
                     TypeName="StudentsDemo.StudentsEntry"
                     PrimaryView="StudentsPerson"

DataMember property of form view also to StudentsPerson:

<asp:Content ID="cont2" ContentPlaceHolderID="phF" Runat="Server">
    <px:PXFormView ID="form" runat="server" DataSourceID="ds" DataMember="StudentsPerson" Width="100%" Height="100px" AllowAutoHide="false">
        <Template>
            <px:PXTextEdit CommitChanges="True" ID="edFirstName" runat="server" DataField="FirstName" AutoRefresh="True"
                           Width="183px" AllowAddNew="True" DataSourceID="ds" />
            <px:PXTextEdit CommitChanges="True" ID="edLastName" runat="server" DataField="LastName" AutoRefresh="True"
                           Width="183px" AllowAddNew="True" DataSourceID="ds" />
        </Template>
    </px:PXFormView>
</asp:Content>

And finally DataMember of grid area, set to StudentDetails:

<asp:Content ID="cont3" ContentPlaceHolderID="phG" Runat="Server">
    <px:PXGrid ID="grid" runat="server" DataSourceID="ds" Width="100%" Height="150px" SkinID="Details" AllowAutoHide="false">
        <Levels>
            <px:PXGridLevel DataMember="SingleStudentDetails">

Now our example is ready, let check it in browser:

As you see, our code worked properly and data was saved to database. We ready to elevate it to the next level. Point is that when we add more students with their details, in browser we'll see next picture:

This is because data member for our grid (StudentDetails) not only save records to database, but also retrieve all of them to UI. So let define in graph another data view for filtering records and retriving only thouse data, that match the current student.

public PXSelect<StudentDetailsWhere<StudentDetails.firstNameEqual<Current<Students.firstName>>,
       And<StudentDetails.lastNameEqual<Current<Students.lastName>>>>> SingleStudentDetails;

Then don't forget replace data member for grid to SingleStudentDetails - and that all.

Let shortly summarize:
1. For creating Master-Detail relationship, define main DAC class (Master) and one or more secondary classes (Details).
2. Assign common key fields, add PXDefault and PXParent attribute to them.
3. In the graph create matching data views for the Master and for Details.
4. Declare Master view in PrimaryView of PXDataSource and in DataMember of PXFormView; set Details view to DataMember of PXGridLevel.
5. Don't forget to add filtering of details with usage of Current BQL statement.

How to modify Stock Item screen ( IN202500 ) in Acumatica with adding master detail selectors

Hello everybody,

today I want to describe how to extend Stock Item screen IN202500 in Acumatica. Imagine that you need to add to tab General settings two selectors. Suppose that you need to have two selectors:

as you can see following need to be achieved:

  1. To tab General Settings it is needed to add selectors: "Clase articulo web" and "Subclase articulo web".
  2. In case if selector "Clase articulo web" changes, then "Subclase articulo web" should show some other values.

The first step should be start Acumatica developer project as described here.

For cases if we have dependency of one selector from another it is possible to program in two ways:

  1. Custom selector for dependent code.
  2. Describe dependency in DAC class or DAC class extension.

Option number 1 or custome selectors were already described at mine blog here

Let's take a look at second scenario. Before we continue let's create two tables: UsrArticul and UsrSubArticul. In order to make life simple, you can use SQL below in order to follow me:

SET ANSI_NULLS ON
 
 
SET QUOTED_IDENTIFIER ON
 
 
CREATE TABLE [dbo].[UsrArticul](
	[CompanyID] [INT] NOT NULL,
	[ArticulID] [INT] IDENTITY(1,1) NOT NULL,
	[ArticulCD] [NVARCHAR](50) NULL,
	[ArticulName] [NVARCHAR](50) NULL,
 CONSTRAINT [PK_Articul] PRIMARY KEY CLUSTERED 
(
	[CompanyID] ASC,
	[ArticulID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) 
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[UsrSubArticuls](
	[CompanyID] [int] NOT NULL,
	[ArticulID] [int] NOT NULL,
	[SubArticulID] [int] IDENTITY(1,1) NOT NULL,
	[SubArticulCD] [nvarchar](50) NULL,
	[SubArticulName] [nvarchar](50) NULL,
 CONSTRAINT [PK_UsrSubArticuls] PRIMARY KEY CLUSTERED 
(
	[CompanyID] ASC,
	[ArticulID] ASC,
	[SubArticulID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) 

I always use prefix Usr in Acumatica in order to notify updater of Acumatica that during upgrade of version those two tables shouldn't be deleted. 

Next let's insert some demo data:

INSERT INTO [dbo].[UsrArticul] ([CompanyID] ,[ArticulCD] ,[ArticulName]) VALUES (2, 'ART1', 'Articul 1' )
INSERT INTO [dbo].[UsrArticul] ([CompanyID],[ArticulCD],[ArticulName]) VALUES (2, 'ART2', 'Articul 2' )
INSERT INTO [dbo].[UsrArticul]([CompanyID],[ArticulCD],[ArticulName]) VALUES (2, 'ART3', 'Articul 3' )
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (,,'SUB1A' ,'SUB 1 A')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (,1, 'SUB1b' ,'SUB 1 b')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (,1, 'SUB1c' ,'SUB 1 c')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (,1,  'SUB1d','SUB 1 d')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 2,'SUB2A','SUB 2 A')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 2, 'SUB2b' ,'SUB 2 b')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 2, 'SUB2c' ,'SUB 2 c')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 2, 'SUB2d' ,'SUB 2 d')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 3, 'SUB3A', 'SUB 3 A')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 3, 'SUB3b', 'SUB 3 b')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 3, 'SUB3c', 'SUB 3 c')
INSERT INTO [dbo].[UsrSubArticuls] ([CompanyID] ,[ArticulID] ,[SubArticulCD] ,[SubArticulName]) VALUES (2, 3,'SUB3d', 'SUB 3d')

We have three root articuls and each of those root articuls has 4 child sub articuls. And task is the following, if User select Articul 1 at top selector, then bottom selector should show SUB 1 A, SUB 1 b, Sub 1 c and Sub 1 d. 

In order to have access to those two controls on page IN202500 we need:

  1. Create DAC classes for tables UsrArticule and UsrSubArticuls
  2. Exted DAC class InventoryItem with declaration of two fields as selectors
  3. Add two selectors on the page

In order to create two DAC classes, you can use either standard Acumatica DAC class generator, or download mine utility which does the same. If you decide to use utility then also keep in mind that you need delete from generated code following fields: CreatedByID, CreatedByScreenID, CreatedDatetime, LastModifiedByID, LastModifiedByScreenID, LastModifiedDateTime, Tstamp. I skipped those fields that Acumatica uses in order to make sample easier to understand. But in real life projects I definetely recommend to have those fields especially if you have multiuser environment where few users can modify the same entity. In that case those service fields is a must.

Take a look at two DAC classes about UsrArticul and UsrSubArticuls:

[Serializable]
    public class UsrArticul : IBqlTable 
    {
        #region ArticulID
        public abstract class articulID : IBqlField
        {
        }
        [PXDBInt(IsKey = true)]
        [PXUIField(DisplayName = "Articul ID", Visibility = PXUIVisibility.Visible, Visible = false, Enabled = false)]
        public virtual int? ArticulID { getset; }
 
        #endregion
 
        #region route
        public abstract class articulCD : IBqlField
        {
        }
        [PXDBString(50)]
        [PXUIField(DisplayName = "Articul CD", Visibility = PXUIVisibility.Visible)]
        public virtual string ArticulCD { getset; }
        #endregion
 
        #region route
        public abstract class articulName : IBqlField
        {
        }
        [PXDBString(50)]
        [PXUIField(DisplayName = "Articul Name", Visibility = PXUIVisibility.Visible)]
        public virtual string ArticulName { getset; }
        #endregion
       
    }

and another class:
    [Serializable]
    public class UsrSubArticuls : IBqlTable 
    {
        #region ArticulID
        public abstract class articulID : IBqlField
        {
        }
 
        [PXDBInt()]
        [PXUIField(DisplayName = "Articul ID", Visibility = PXUIVisibility.Visible, Visible = false, Enabled = false)]
        public virtual int? ArticulID { getset; }
 
        #endregion
 
        #region SubArticulID
        public abstract class subArticulID : IBqlField
        {
        }
 
        [PXDBInt(IsKey = true)]
        [PXUIField(DisplayName = "Sub Articul ID", Visibility = PXUIVisibility.Visible, Visible = false, Enabled = false)]
        public virtual int? SubArticulID { getset; }
 
        #endregion
 
        #region route
        public abstract class subArticulCD : IBqlField
        {
        }
        [PXDBString(50)]
        [PXUIField(DisplayName = "Sub Articul CD", Visibility = PXUIVisibility.Visible)]
        public virtual string SubArticulCD { getset; }
        #endregion
 
        #region route
        public abstract class subArticulName : IBqlField
        {
        }
        [PXDBString(50)]
        [PXUIField(DisplayName = "Sub Articul Name", Visibility = PXUIVisibility.Visible)]
        public virtual string SubArticulName { getset; }
        #endregion
        	
    }

 Next step - create extension class, what are you use. In this example it InventoryItem DAC class:

public class InventoryItemExt : PXCacheExtension<InventoryItem>
   {
       public abstract class usrArticul : IBqlField
       {
       }
 
       [PXDBInt()]
       [PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
       [PXUIField(DisplayName = "Articul CD")]
       [PXSelector(typeof(Search<UsrArticul.articulID>), SubstituteKey = typeof(UsrArticul.articulCD))]
       public virtual int? UsrArticul { getset; }
 
       public abstract class usrSubArticul : IBqlField
       {
       }
 
       [PXDBInt()]
       [PXSelector(typeof(Search<UsrSubArticuls.subArticulIDWhere<UsrSubArticuls.articulIDEqual<Current<InventoryItemExt.usrArticul>>>>), 
           SubstituteKey = typeof(UsrSubArticuls.subArticulCD))]
       [PXDefault(PersistingCheck = PXPersistingCheck.Nothing)]
       [PXUIField(DisplayName = "Sub Articul CD")]
       public virtual int? UsrSubArticul { getset; }
 
   }

Here I describe two selectors. Overhead and downhead. 

Second selector depend of first. Also pay attention that I used  Equal<Current<InventoryItemExt.usrArticul> (not UsrArticul.articulID), for correct filtering because in this moment when you select first field it is "Current "selector, and you use "YourDacExt.Field".

or in full picture:

[PXSelector(typeof(Search<UsrSubArticuls.subArticulID, Where<UsrSubArticuls.articulID, Equal<Current<InventoryItemExt.usrArticul>>>>), 
           SubstituteKey = typeof(UsrSubArticuls.subArticulCD))]

Staff in bold will allow you to select only sub group.

Do not forget build your project!

 

After that add controls(PXSelector) to .aspx  view page:

<px:PXSelector CommitChanges="True" ID="usrArticul1" runat="server" DataField="UsrArticul" AllowEdit="True" ></px:PXSelector>
<px:PXSelector CommitChanges="True" ID="usrSubArticul1" runat="server" DataField="UsrSubArticul" AutoRefresh="True" AllowEdit="True" ></px:PXSelector>

After that open this page, and you can see this controls: