Solved

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


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

Nicely done!

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

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 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

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.

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