Solved

Trying to get a sum from table and provide it to a selector as a field

  • 16 May 2023
  • 5 replies
  • 57 views

Userlevel 6
Badge +3

If this looks familiar, I asked a question the other day about this issue.  I resolved everything in that question except for this part, so I am re-asking based on the remaining open issue I am having.

I added a column to a selector “typeof(USRBudgetTotalExt2.usrBudgetTotal)”.  The column value is being derived from an extension.  In that extension, I want to execute a BQL query to get a sum of the curyRevisedAmount field from the PMBudget table.

My code currently returns a hardcoded amount of 100 just for proof of concept that my selector is working and returning the value for the column based on the extension.

Looking good so far!  :-)

 

 

This is the code at this point.

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
                {
                    //returns the correct contract ID
                    var joe = Base.ContractID;

                    //PMBudget budget = SelectFrom<PMBudget>
                    //        .Where<PMBudget.projectID.IsEqual<PMProject.contractID.FromCurrent>>
                    //        .AggregateTo<GroupBy<PMBudget.projectID>, Sum<PMBudget.curyRevisedAmount>>.View.Select(Base);

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

No matter what I try, I am getting errors that I cannot execute code from within a property.

This code works in the ProjectEntry_Extension I created so I know the query is correct.

                    PMBudget budget = SelectFrom<PMBudget>
                            .Where<PMBudget.projectID.IsEqual<PMProject.contractID.FromCurrent>>
                            .AggregateTo<GroupBy<PMBudget.projectID>,   Sum<PMBudget.curyRevisedAmount>>.View.Select(Base);

Is there some workaround I can do to be able to execute this statement and be able to return from my USRBudgetTotalExt2 extension?  If not, I think that my answer to my client is that this is not possible.  I hate to reply with that, but maybe that is just the way it is.

 

 

icon

Best answer by Django 16 May 2023, 21:10

View original

5 replies

Userlevel 7
Badge +5

I could be wrong but I don’t think that you want to be running any significant code in the getter/setter area.

I feel like what you want is to set up your field structure/logic like OpenQty field in SOOrder and SOLine.  SOOrder is just a decimal field. SOLine uses PXFormula (or PXUnboundFormula) to automatically update the SOOrder record.

Then you always have the value that you want in that field for your selector.

Userlevel 6
Badge +3

@Django I totally agree.  I’ve cautioned the client that this could add a bunch of overhead, but they really want this.

Just for kicks, I added an unbound custom field to the Projects screen which shows the budget total amount.  I thought about making it a DB field and putting logic on that page to update that field automatically from the Details grid.  Then, in my selector, I can join to that table and pull the value.  I haven’t tried that yet as I was hoping there might be a better approach but I think that might work.

Userlevel 7
Badge +5

Having the header updated when the detail field changes should add very little overhead. There is a lot of optimization around how those types of aggregate fields are handled - mostly around how they don’t re-sum the total value - they only record net changes to the field value as you add/modify/delete the source records.

Alternatively, you could add in code in Persist to query the database with a PXDatabase call but I think the first option will work just fine for you.

Userlevel 6
Badge +3

@Django Check it out!

My workaround was to add a custom field to the Contracts table that stores the sum of the curyRevisedAmount from the PMBudget table for the respective contract (project).  Then in my selector, I grab that custom field.

Thanks for the tip!

 

 

Userlevel 7
Badge +5

Nicely done!

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