Hello everybody.

Imagine that you need to have implementation of several tables with possibility to update them all. 

Also you want to implement by the Acumatica side rather than by the database.

• You know that Joined tables in Acumatica are read-only and you cannot update fields there
• Can we create/use SQL-like views in Acumatica?
• How can I join grouped (statistical) view to the DAC?

So, yes we can do it. For this purpose Acumatica gives you PXprojection attribute.

PXProjection attribute  binds the DAC to an arbitrary data set. The attribute thus defines a named view, but is implemented by the server side rather than by the database.

Lets start)

Steps:

• Think whats tables you need to implement and whats of fields from this table;
• Create new DAC that will represent view. This DAC may have less columns than you have in DACs you will select later. So just define fields you need. In the end it will help a bit with select performance;
• Create new graph;
• Create page - view of implementatoin;

For examle, I want to see implementatioin of 2 tables(CRactivity and BAccount) and fields like baccountID, baccountName from BAccount table and contactID, subject from CRactivity table.

In my solution I create new DAC class and define PXProjectionAttribute on this DAC.

[Serializable]
    [PXProjection(typeof(
        Select2<BAccount,
            InnerJoin<CRActivity,
                On<CRActivity.bAccountID, Equal<BAccount.bAccountID>>>>),
       Persistent = true
        )]
    public partial class PXprojectionJoinDacClass : IBqlTable
    {
        public abstract class baccountID : PX.Data.IBqlField
        {
        }
        // The field mapped to the BAccount field (through setting of BqlField)
        [PXDBInt(IsKey = true, BqlField = typeof(BAccount.bAccountID))]
        [PXExtraKey]
        [PXUIField(DisplayName = "BAccount ID")]
        public virtual int? BAccountID { get; set; }
 
        public abstract class contactID : PX.Data.IBqlField
        {
        }
        // The field mapped to the CRactivity field
        // (through setting of BqlField)
        [PXDBInt(IsKey = true, BqlField = typeof(CRActivity.contactID))]
        [PXUIField(DisplayName = "Contact ID")]
        public virtual int? ContactID { get; set; }
 
        public abstract class subject : PX.Data.IBqlField
        {
        }
        // The field mapped to the CRactivity field
        // (through setting of BqlField)
        [PXDBString(IsKey = false, BqlField = typeof(CRActivity.subject))]
        [PXUIField(DisplayName = "Subject")]
        public virtual string Subject { get; set; }
 
        public abstract class acctName : PX.Data.IBqlField
        {
        }
        // The field mapped to the Baccount field
        // (through setting of BqlField)
        [PXDBString(IsKey = false, BqlField = typeof(BAccount.acctName))]
        [PXUIField(DisplayName = "Account Name")]
        public virtual string AcctName { get; set; }
 
    }

As you see I use Select2 attribute for select from 2 tables and use BQL command. In my way it is InnerJoin.

For PXProjection constructor you should provide BQL command that will define what tables you want to select. You may use all possible commands of BQL (Join, Where, GroupBy, OrderBy).

Also I define "Persistent = true" for update both tables, without it my implementatiioin will be only for read. 

Go next and create new graph.

public class ProjectionMaint : PXGraph<ProjectionMaint, PXprojectionJoinDacClass>
   {
       public PXSelect<PXprojectionJoinDacClass> Projections;
   }

Easy, we only use PXSelect from PXprojectionJoinDacClass.

Go next and create new page.

<%@ Page Language="C#" MasterPageFile="~/MasterPages/FormDetail.master" 
                          AutoEventWireup="true" ValidateRequest="false" 
	CodeFile="PR101000.aspx.cs" Inherits="Page_PR101000" Title="Untitled Page" %>
<%@ MasterType VirtualPath="~/MasterPages/FormDetail.master" %>
 
<asp:Content ID="Content1" ContentPlaceHolderID="phDS" runat="Server">
    <px:PXDataSource ID="ds" runat="server" Visible="True" SuspendUnloading="False" 
                     TypeName="ClassLibrary1.ProjectionMaint" PrimaryView="Projections">
    </px:PXDataSource>
</asp:Content>
 
<asp:Content ID="cont2" ContentPlaceHolderID="phG" runat="Server">
    <px:PXGrid ID="grid" runat="server" Height="400px" Width="100%" Style="z-index: 100"
               AllowPaging="True" AllowSearch="True" AdjustPageSize="Auto"
                                       DataSourceID="ds" SkinID="Primary" 
               TabIndex="800" TemporaryFilterCaption="Filter Applied">
        <Levels>
            <px:PXGridLevel DataKeyNames="BAccountID" DataMember="Projections">
                <Columns>
                    <px:PXGridColumn DataField="BAccountID" Width="90px">
                        <ValueItems MultiSelect="False">
                        </ValueItems>
                    </px:PXGridColumn>
                    <px:PXGridColumn DataField="ContactID" Width="200px">
                        <ValueItems MultiSelect="False">
                        </ValueItems>
                    </px:PXGridColumn>
                    <px:PXGridColumn DataField="Subject" Width="200px">
                        <ValueItems MultiSelect="False">
                        </ValueItems>
                    </px:PXGridColumn>
                    <px:PXGridColumn DataField="AcctName" Width="200px">
                        <ValueItems MultiSelect="False">
                        </ValueItems>
                    </px:PXGridColumn>
                </Columns>
            </px:PXGridLevel>
        </Levels>
        <AutoSize Container="Window" Enabled="True" MinHeight="200" />
    </px:PXGrid>
</asp:Content>

So here we use only PXGrid where we define fields.

After add this page to site map and Go to page PR101000 to test.

All work fine. Make sure that fields updateble in both tables.

Also do not forget that you can use 2 and more tables in one implementation. 

Thank you for reading. Have you question? Please leave comments here.