How can I create create a view that shows the most recent order date per customer?

Hi, @ppowell  I just worked on this requirement, and working as expected to me (Navigated to the multiple and observed all good with the GI). please find the details below.

I have created an SQLView and for that SQLView created GI with the below modifications.

• While creating SQLView, I have removed all the underscores for that fields.
• Changed BranchID, Company ID to fetch the data from my local machine.
• While creating the DAC file, I have provided the IsKey= true for the KEY fields.

Here is the GI.

SQL View:

DROP VIEW IF EXISTS VWGetCustomerSOData

GO 
Create view VWGetCustomerSOData
as


SELECT B.[BAccountID] AS [CustomerBAccountID], MAX( B.[AcctCD]) AS [CustomerAcctCD], MAX( B.[AcctName]) AS [CustomerAcctName], MAX( B.[NoteID]) AS [CustomerNoteID], 
MAX( [SOOrder].[OrderType]) AS [SOOrderOrderType], MAX( [SOOrder].[OrderNbr]) AS [SOOrderOrderNbr], 
MAX( [SOOrder].[OrderDate]) AS [SOOrderOrderDate], MAX( [SOOrder].[NoteID]) AS [SOOrderNoteID], MAX( [SOOrder].[CuryID]) AS [SOOrderCuryID], 
SUM( [SOOrder].[CuryInfoID]) AS [SOOrderCuryInfoID] 
FROM ( 
[Customer] C 
INNER JOIN [BAccount] B ON ( B.[CompanyID] = 2) AND B.[DeletedDatabaseRecord] = 0 AND C.[BAccountID] = B.[BAccountID] 
) 
INNER JOIN [SOOrder] [SOOrder] ON ( [SOOrder].[CompanyID] = 2) AND  ( [SOOrder].[BranchID] IS NULL  OR [SOOrder].[BranchID] = 16) 
AND ( B.[BAccountID] = [SOOrder].[CustomerID]) 
WHERE ( ( [SOOrder].[OrderType] = 'SO' OR [SOOrder].[OrderType] = 'WO') AND [SOOrder].[Status] = 'C') AND 
( C.[CompanyID] = 2) AND C.[DeletedDatabaseRecord] = 0 
GROUP BY B.[BAccountID] 

DAC File:

using System;
using PX.Data;

namespace GetCustomerSOData
{
  [Serializable]
  [PXCacheName("VWGetCustomerSOData")]
  public class VWGetCustomerSOData : IBqlTable
  {
    #region CustomerBAccountID
    [PXDBInt(IsKey = true)]
    [PXUIField(DisplayName = "Customer BAccount ID")]
    public virtual int? CustomerBAccountID { get; set; }
    public abstract class customerBAccountID : PX.Data.BQL.BqlInt.Field<customerBAccountID> { }
    #endregion

    #region CustomerAcctCD
    [PXDBString(30, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Customer Acct CD")]
    public virtual string CustomerAcctCD { get; set; }
    public abstract class customerAcctCD : PX.Data.BQL.BqlString.Field<customerAcctCD> { }
    #endregion

    #region CustomerAcctName
    [PXDBString(255, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "Customer Acct Name")]
    public virtual string CustomerAcctName { get; set; }
    public abstract class customerAcctName : PX.Data.BQL.BqlString.Field<customerAcctName> { }
    #endregion

    #region CustomerNoteID
    [PXDBGuid()]
    [PXUIField(DisplayName = "Customer Note ID")]
    public virtual Guid? CustomerNoteID { get; set; }
    public abstract class customerNoteID : PX.Data.BQL.BqlGuid.Field<customerNoteID> { }
    #endregion

    #region SOOrderOrderType
    [PXDBString(2,IsKey = true, IsFixed = true, InputMask = "")]
    [PXUIField(DisplayName = "SOOrder Order Type")]
    public virtual string SOOrderOrderType { get; set; }
    public abstract class sOOrderOrderType : PX.Data.BQL.BqlString.Field<sOOrderOrderType> { }
    #endregion

    #region SOOrderOrderNbr
    [PXDBString(15,IsKey = true, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "SOOrder Order Nbr")]
    public virtual string SOOrderOrderNbr { get; set; }
    public abstract class sOOrderOrderNbr : PX.Data.BQL.BqlString.Field<sOOrderOrderNbr> { }
    #endregion

    #region SOOrderOrderDate
    [PXDBDate()]
    [PXUIField(DisplayName = "SOOrder Order Date")]
    public virtual DateTime? SOOrderOrderDate { get; set; }
    public abstract class sOOrderOrderDate : PX.Data.BQL.BqlDateTime.Field<sOOrderOrderDate> { }
    #endregion

    #region SOOrderNoteID
    [PXDBGuid()]
    [PXUIField(DisplayName = "SOOrder Note ID")]
    public virtual Guid? SOOrderNoteID { get; set; }
    public abstract class sOOrderNoteID : PX.Data.BQL.BqlGuid.Field<sOOrderNoteID> { }
    #endregion

    #region SOOrderCuryID
    [PXDBString(5, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "SOOrder Cury ID")]
    public virtual string SOOrderCuryID { get; set; }
    public abstract class sOOrderCuryID : PX.Data.BQL.BqlString.Field<sOOrderCuryID> { }
    #endregion

    #region SOOrderCuryInfoID
    [PXDBLong()]
    [PXUIField(DisplayName = "SOOrder Cury Info ID")]
    public virtual long? SOOrderCuryInfoID { get; set; }
    public abstract class sOOrderCuryInfoID : PX.Data.BQL.BqlLong.Field<sOOrderCuryInfoID> { }
    #endregion
  }
}