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.

No Comments

Add a Comment