Use of PXProjection: To Display Multiple DAC Data on a Tab

The following activity will walk you through the process of deriving a set of data from multiple DACs by using the PXProjection attribute, and displaying that data on a single tab.

Story

Suppose that in the PhoneRepairShop customization project, you want to display information about the invoice related to a repair work order and the most recent payment that was made for it. You need to add a tab to the Repair Work Orders (RS301000) form that will display this information. This tab will be displayed on the form only if the repair work order has been paid.

The tab will have the following elements:

• Invoice Nbr.: The number of the invoice that has been created for the repair work order
• Due Date: The due date for the invoice
• Latest Payment: The number of the most recent payment applied to the invoice
• Latest Amount Paid: The amount paid in the payment that was applied to the invoice most recently

This set of data is derived from different DACs. To display the UI elements on a single tab, you will use the PXProjection attribute.

Process Overview

System Preparation

Step 1: Learning the DAC Names for the Fluent BQL Query

To retrieve the needed set of data, you will find out which DACs you need to use in a fluent BQL query of the PXProjection attribute. To learn the names of the required DACs, do the following:

1. On the Invoices (SO303000) form, apply the Element Inspector to the Summary area of the form to learn the DAC name for the invoice, and to the Applications tab to learn the DAC name for payments that have been applied to the invoice. Notice that these are the ARInvoice and ARAdjust2 DACs, respectively.
2. Learn the key fields of the ARInvoice DAC, which you will need to know to select records in a fluent BQL query. The key fields you need to select an invoice are ARInvoice.refNbr and ARInvoice.docType.
3. Analyze the code of the ARAdjust2 DAC. It is an alias of the ARAdjust DAC, so you can use the ARAdjust DAC.
4. Analyze the code of the ARInvoice and ARAdjust DACs and the fields that are defined in them.
   You will need the following fields:

   • For the invoice number, ARInvoice.refNbr
   • For the invoice due date, ARINvoice.dueDate
   • For the payment number, ARAdjust.adjgRefNbr
   • For the payment amount, ARAdjust.curyAdjdAmt

Step 2: Defining the DAC for the Tab

To define the DAC for the tab, do the following:

1. In the Helper/Messages.cs file, add the RSSVWorkOrderPayment string to the Messages class as shown in the following code. This message will me used in the PXCacheName attribute for the new DAC.

           public const string RSSVWorkOrderPayment =
               "Invoice and Payment of the Repair Work Order";

2. In the DAC folder of the PhoneRepairShop_Code project, create the RSSVWorkOrderPayment.cs file.
3. Add the following using directives.

   using PX.Data;
   using PX.Data.BQL.Fluent;
   using PX.Objects.AR;

4. Add the RSSVWorkOrderPayment DAC, as shown in the following code.

   namespace PhoneRepairShop
   {
       [PXCacheName(Messages.RSSVWorkOrderPayment)]
       [PXProjection(typeof(
         SelectFrom<ARInvoice>.
           InnerJoin<ARAdjust>.On<
             ARAdjust.adjdRefNbr.IsEqual<ARInvoice.refNbr>.
             And<ARAdjust.adjdDocType.IsEqual<ARInvoice.docType>>>.
           AggregateTo<
             Max<ARAdjust.adjgDocDate>,
             GroupBy<ARAdjust.adjdRefNbr>,
             GroupBy<ARAdjust.adjdDocType>>))]
       public class RSSVWorkOrderPayment : PXBqlTable, IBqlTable
       {    }
   }

   In the query of the PXProjection attribute, you select an invoice and all payments applied to the invoice. To sort the payments by the date, you use the AggregateTo clause. Inside the clause, you group all payments by their invoice number and document type (which are the same because all payments selected are applied to the same invoice) and select the payment with the latest document date.

5. Add to the RSSVWorkOrderPayment DAC the fields you learned in Instruction 1, as the following code shows.

           #region InvoiceNbr
           [PXDBString(15, IsUnicode = true, IsKey = true, InputMask = "",
             BqlField = typeof(ARInvoice.refNbr))]
           [PXUIField(DisplayName = "Invoice Nbr.", Enabled = false)]
           public virtual String InvoiceNbr { get; set; }
           public abstract class invoiceNbr :
               PX.Data.BQL.BqlString.Field<invoiceNbr> { }
           #endregion
   
           #region DueDate
           [PXDBDate(BqlField = typeof(PX.Objects.AR.ARInvoice.dueDate))]
           [PXUIField(DisplayName = "Due Date", Enabled = false)]
           public virtual DateTime? DueDate { get; set; }
           public abstract class dueDate :
               PX.Data.BQL.BqlDateTime.Field<dueDate> { }
           #endregion
   
           #region AdjgRefNbr
           [PXDBString(BqlField = typeof(ARAdjust.adjgRefNbr))]
           [PXUIField(DisplayName = "Latest Payment", Enabled = false)]
           public virtual String AdjgRefNbr { get; set; }
           public abstract class adjgRefNbr :
               PX.Data.BQL.BqlString.Field<adjgRefNbr> { }
           #endregion
   
           #region CuryAdjdAmt
           [PXDBDecimal(BqlField = typeof(ARAdjust.curyAdjdAmt))]
           [PXUIField(DisplayName = "Latest Amount Paid", Enabled = false)]
           public virtual Decimal? CuryAdjdAmt { get; set; }
           public abstract class curyAdjdAmt :
               PX.Data.BQL.BqlDecimal.Field<curyAdjdAmt> { }
           #endregion

   Note that each field has the PXDB<type> attribute with the BqlField parameter specified to set up the projection.

   Although the RSSVWorkOrderPayment DAC has a master-detail relationship with the RSSVWorkOrder DAC, you do not need to add any PXDBDefault and PXParent attributes to the fields because all field values are determined by the query in the PXProjection attribute.

6. Build the project.

Step 3: Defining the Data View for the Tab

To define the data view for the tab, do the following:

1. In the RSSVWorkOrderEntry class, add the following member to the Views region of the class.

           public SelectFrom<RSSVWorkOrderPayment>.
               Where<RSSVWorkOrderPayment.invoiceNbr.IsEqual<
                   RSSVWorkOrder.invoiceNbr.FromCurrent>>.
               View Payments;

   In the view, you select data from the RSSVWorkOrderPayment DAC with same invoice number (stored in the RSSVWorkOrder DAC) as in the Summary area of the form.

2. Build the project.

Step 4: Adding the New Tab Item

To create the new tab item and configure it, do the following:

1. Use the Screen Editor or define the tab manually in the RS301000.aspx file. The tab should contain the PXFormView container.
2. In the PXFormView container, define the UI elements for the fields you added in the RSSVWorkOrderPayment DAC.
3. Organize the elements in a single column.
4. Bind the PXFormView container to the Payments data view which you created in Step 3.
5. Publish the customization project.

<px:PXTab ID="tab" runat="server" Width="100%" Height="150px" DataSourceID="ds" AllowAutoHide="false">
  <Items>
   ...
   <px:PXTabItem Text="Payment Info">
     <Template>
	   <px:PXFormView runat="server" ID="CstFormView20" DataMember="Payments">
	     <Template>
		  <px:PXTextEdit runat="server" ID="CstPXTextEdit24" DataField="InvoiceNbr">
		  </px:PXTextEdit>
		  <px:PXDateTimeEdit runat="server" ID="CstPXDateTimeEdit23" DataField="DueDate">
		  </px:PXDateTimeEdit>
		  <px:PXTextEdit runat="server" ID="CstPXTextEdit21" DataField="AdjgRefNbr">
		  </px:PXTextEdit>
		  <px:PXNumberEdit runat="server" ID="CstPXNumberEdit22" DataField="CuryAdjdAmt">
		  </px:PXNumberEdit>
	     </Template>
	   </px:PXFormView>
    </Template> 
   </px:PXTabItem> 
  </Items>
</px:PXTab>

The Payment Info tab should look as shown on the following screenshot.

Step 5: Testing the Implemented Tab

To test the Payment Info tab of the Repair Work Orders (RS301000) form, do the following:

1. Open any repair work order with Paid or Completed status.
2. Open the Payment Info tab, which looks as follows.