Solved

Trying to add a column to a selector that will use a BQL query to get a sum from another table

  • 15 May 2023
  • 2 replies
  • 111 views

Userlevel 6
Badge +3

On the Subcontracts screen I want to add a custom column to the Project ID selector.

The value will come from the Details tab of the Projects screen (sum of Revised Budget Total)

I’ve tried numerous ways to do this.  This is what I am trying at the moment.  I am simply trying to put a dummy string into the custom column as a proof of concept before doing any BQL selects at this point.

namespace PX.Objects.PO
{
    [PXNonInstantiatedExtension]
    public sealed class PO_POLine_ExistingColumn : PXCacheExtension<PX.Objects.PO.POLine>
    {
        public static bool IsActive() => true;

        #region ProjectID    
        [PXMergeAttributes(Method = MergeMethod.Append)]
        [PXCustomizeSelectorColumns(
            typeof(POLine.alternateID),
            typeof(USRBudgetTotalExt.usrBudgetTotal))]
        public int? ProjectID { get; set; }
        #endregion
    }

    public sealed class USRBudgetTotalExt : PXCacheExtension<PX.Objects.PO.POLine>
    {
        public static bool IsActive() => true;

        #region UsrBudgetTotal
        public abstract class usrBudgetTotal : PX.Data.BQL.BqlString.Field<usrBudgetTotal> { }
        protected string _UsrBudgetTotal;
        [PXString(20)]
        [PXUnboundDefault(PersistingCheck = PXPersistingCheck.Nothing)]
        [PXUIField(DisplayName = "Budget Total2", Visibility = PXUIVisibility.SelectorVisible, Visible = true)]
        public string UsrBudgetTotal
        {
            get
            {
                try
                {
                    return "Budget Total";
                }
                catch
                {
                    return "JoeError";
                }
            }
            set
            {
                this._UsrBudgetTotal = value;
            }
        }
        #endregion


        #region Event Handlers

        #endregion
    }

}

 

This code has some effect on the selector.  It does not show either of the two columns I am adding, and it actually removes some of the default columns like Customer and Status.  It also changes the order of the fields.

 

My next thought was to completely override the selector for the Project ID field.  But I cannot find the actual select statement for that field.  

  1. is this even possible to add a custom column that is an aggregate of another table based on the current line of the selector?
  2. If it is possible, any suggestions on the approach I should take
  3. Can anyone tell me how to get the select statement if I need to override it?  In the code repository, the selector is based on an attribute which I cannot seem to find the source.

 

 

 

icon

Best answer by Joe Schmucker 16 May 2023, 18:30

View original

2 replies

Userlevel 6
Badge +3

Update.  I’m able to add my column to the selector.  I’ve got one other issue but I am going to submit that issue in another topic.

Here is the code I ended up with to get my custom field to show up in the selector.

Note that my USRBudgetTotalExt2 is hardcoded to return 100, but that is another issue.

namespace PX.Objects.PO
{
    //[PXNonInstantiatedExtension]
    public class PO_POLine_ExistingColumn : PXCacheExtension<PX.Objects.PO.POLine>
    {
        public static bool IsActive() => true;

        #region ProjectID  
        [PXMergeAttributes(Method = MergeMethod.Append)]
        [PXCustomizeSelectorColumns(
            typeof(PX.Objects.PM.PMProject.contractCD),
            typeof(PX.Objects.PM.PMProject.description),
            typeof(USRBudgetTotalExt2.usrBudgetTotal),
            typeof(PX.Objects.PM.PMProject.status),
            typeof(PX.Objects.PM.PMProject.customerID),
            typeof(PX.Objects.AR.Customer.acctName),
            typeof(PX.Objects.PM.PMProject.curyID))]
        public int? ProjectID { get; set; }
        #endregion

    }

    public sealed class USRBudgetTotalExt2 : PXCacheExtension<PX.Objects.CT.Contract>
    {
        public static bool IsActive() => true;

        #region UsrBudgetTotal
        public abstract class usrBudgetTotal : PX.Data.BQL.BqlDecimal.Field<usrBudgetTotal> { }
        protected decimal? _UsrBudgetTotal;
        [PXDecimal]
        [PXUnboundDefault(PersistingCheck = PXPersistingCheck.Nothing)]
        [PXUIField(DisplayName = "Budget Total", Visibility = PXUIVisibility.SelectorVisible, Visible = true)]
        public decimal? UsrBudgetTotal
        {
            get
            {
                try
                {
                    //PMBudget budget = SelectFrom<PMBudget>
                    //        .Where<PMBudget.projectID.IsEqual<PMProject.contractID.FromCurrent>>
                    //        .AggregateTo<GroupBy<PMBudget.projectID>, Sum<PMBudget.curyRevisedAmount>>.View.Select(this);

                    return 100;
                }
                catch
                {
                    return 0;
                }
            }
            set
            {
                this._UsrBudgetTotal = value;
            }
        }
        #endregion


        #region Event Handlers

        #endregion
    }
 

Userlevel 7
Badge

Thank you for sharing your solution with the community @Joe Schmucker !

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved