Data View Delegates: To Add a Filtering Query Dynamically

The following activity will walk you through the process of dynamically adding a filtering query in code for an inquiry form.

Story

Suppose that you need to display both repair work orders and sales orders on the Open Payment Summary (RS401000) inquiry form. You cannot use a single BQL query of a data view to implement the displaying of two different types of entities on a single form. You need to compose a query for the form dynamically in code rather than specify the query in the definition of a data view.

Process Overview

In this activity, you will dynamically add a filtering query in code for the Open Payment Summary (RS401000) inquiry form by performing the following steps:

Adding a new field to the grid of the inquiry form to indicate whether the record is a repair work order or a sales order
Defining the data view delegate to dynamically add a filtering query on the inquiry form
Testing the dynamically added query on the inquiry form

System Preparation

Make sure that you have configured your instance by performing the Test Instance for Customization: To Deploy an Instance for Developing Inquiry Forms prerequisite activity. Also, be sure that you have completed the steps described in the Inquiry Forms: To Set Up an Inquiry Form and Filtering Parameters: To Add a Filter for an Inquiry Form prerequisite activities.

Step 1: Adding a New Column to the Filtered Results

To distinguish between the sales orders and repair work orders that are listed on the Open Payment Summary (RS401000) form, you need to add a new column to the grid. This column will contain the identifier of the order type: SO if the order in the row is a sales order; and WO if the order in the row is a repair work order. To add this new column, do the following:

In the Constants.cs file, add the class, as shown below.

    public static class OrderTypeConstants
    {
        public const string SalesOrder = "SO";
        public const string WorkOrder = "WO";
    }

In the Messages.cs file, add the following strings to the Messages class.

        // Order types
        public const string SalesOrder = "SO";
        public const string WorkOrder = "WO";

Add the following field to the RSSVWorkOrderToPay DAC.

        #region OrderType
        [PXString(IsKey = true)]
        [PXUIField(DisplayName = "Order Type")]
        [PXUnboundDefault(OrderTypeConstants.WorkOrder)]
        [PXStringList(
          new string[]
          {
              OrderTypeConstants.SalesOrder,
              OrderTypeConstants.WorkOrder
          },
          new string[]
          {
              Messages.SalesOrder,
              Messages.WorkOrder
          })]
        public virtual String OrderType { get; set; }
        public abstract class orderType :
            PX.Data.BQL.BqlDecimal.Field<orderType>
        { }
        #endregion

Build the project.
In the RS401000.aspx file, add the column indicated in the following code before the OrderNbr column.
```
              <px:PXGridColumn DataField="OrderType" />
```
Publish the customization project.

Step 2: Defining the Data View Delegate

To display both sales orders and repair work orders in one grid, you need to define a data view delegate in which you use two separate queries: a query to select sales orders, and a query to select repair work orders. After you select each query, you return the result with the yield keyword, which provides the next value for the returned collection. To define this data view delegate, do the following:

To compose a query that selects the sales orders and invoices created for these sales orders, learn the names of the DACs that you will use in the query.
In MYOB Acumatica, an invoice cannot be created directly for a sales order. A user first creates a shipment and then creates an invoice for the shipment. The invoice number is stored in the shipment record. Therefore, you need to use the DAC that contains information about shipments. To learn the DAC name, do the following:
1. In MYOB Acumatica, open the Sales Orders (SO301000) form.
2. Open the Shipments tab, which contains information about shipments and the corresponding invoices.
3. While pressing Ctrl + Alt, click the Invoice Nbr. column.
  In the Element Properties dialog box, which opens, note that the DAC name is SOOrderShipment and that the field name of the Invoice Nbr. column is InvoiceNbr.
Use the DAC Schema Browser or open the source code of the SOOrderShipment DAC to investigate its fields. You can see that the DAC contains both the sales order number (in the OrderNbr field) and the invoice number (in the InvoiceNbr field). You will use this information later to construct a fluent BQL statement.

In the RSSVPaymentPlanInq graph, define a method (as shown in the following code) that converts an object of the SOOrderShipment DAC to an object of the RSSVWorkOrderToPay DAC.

        public static RSSVWorkOrderToPay ToRSSVWorkOrderToPay
            (SOOrderShipment shipment) =>
        new RSSVWorkOrderToPay
        {
            OrderNbr = shipment.OrderNbr,
            InvoiceNbr = shipment.InvoiceNbr
        };

Add the following delegate method. The method has the same name as the data view, except that it uses a different case for the first letter.

        protected virtual IEnumerable detailsView()
        {
            var workOrdersQuery =
                SelectFrom<RSSVWorkOrderToPay>.InnerJoin<ARInvoice>.
                   On<ARInvoice.refNbr.IsEqual<RSSVWorkOrderToPay.invoiceNbr>>.
                Where<RSSVWorkOrderToPay.status.IsNotEqual<
                     RSSVWorkOrderWorkflow.States.paid>.
                   And<RSSVWorkOrderToPayFilter.customerID.FromCurrent.IsNull.
                   Or<RSSVWorkOrderToPay.customerID.IsEqual<
                       RSSVWorkOrderToPayFilter.customerID.FromCurrent>>>.
                   And<RSSVWorkOrderToPayFilter.serviceID.FromCurrent.IsNull.
                   Or<RSSVWorkOrderToPay.serviceID.IsEqual<
                        RSSVWorkOrderToPayFilter.serviceID.FromCurrent>>>>.
                View.ReadOnly.Select(this);

            foreach (PXResult<RSSVWorkOrderToPay, ARInvoice> order in workOrdersQuery)
            {
                yield return order;
            }

            var sorders =
                SelectFrom<SOOrderShipment>.InnerJoin<ARInvoice>.
                  On<ARInvoice.refNbr.IsEqual<SOOrderShipment.invoiceNbr>>.
                Where<RSSVWorkOrderToPayFilter.customerID.FromCurrent.IsNull.
                Or<SOOrderShipment.customerID.IsEqual<
                    RSSVWorkOrderToPayFilter.customerID.FromCurrent>>>.
                View.ReadOnly.Select(this);

            foreach (PXResult<SOOrderShipment, ARInvoice> order in sorders)
            {
                SOOrderShipment soshipment = order;
                ARInvoice invoice = order;
                RSSVWorkOrderToPay workOrder = ToRSSVWorkOrderToPay(soshipment);
                workOrder.OrderType = OrderTypeConstants.SalesOrder;
                var result = new PXResult<RSSVWorkOrderToPay, ARInvoice>(
                    workOrder, invoice);
                yield return result;
            }
        }

In the code above, first you have selected repair work orders according to the filter values and returned each repair work order. Then you have selected information about sales orders from shipments according to the filter values; for each object that represents a sales order, you have converted the object to the set of the RSSVWorkOrderToPay and ARInvoice objects, and you have returned the result.

Important: Although the data view delegation declaration in the preceding code works as expected, it is not an efficient approach to use when you have thousands of records in the database and only need to display a few of them in the grid. In these scenarios, we recommend that you use a view context that contains information about the number of records to be retrieved and the filter and sort order for the records. For details on the recommended approach to writing data view delegates, see Data View Delegates: General Information.

Add the required using directives, which are shown in the following code.
```
using PX.Objects.SO;
using System.Collections;
```
Build the project.

Step 3: Testing the Dynamically Added Filter

To test the dynamically added filter on the Open Payment Summary (RS401000) form, do the following:

In MYOB Acumatica, open the Open Payment Summary form.
The form should list both repair work orders and sales orders, as shown in the Order Type column of the following screenshot.
Figure 1. The form with repair work orders and sales orders
In the Customer ID box, select the C000000003 customer.
The results should look as shown below.
Figure 2. The Open Payment Summary form for a particular customer