Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

To display get the sum of all purchase order lines on purchase order header

(1) ShareShare
ReportReport
Posted on by 1,479
hi ,
i want to display the sum of the purchline. lineamount on purchline header and how can i get this done ,and also need to get the sum value updated if the lines are added or subtracted, can any one help me on this .
 
thanks,
regards ,
dinesh
  • Martin Dráb Profile Picture
    Martin Dráb 230,962 Most Valuable Professional on at
    To display get the sum of all purchase order lines on purchase order header
    First of all, try recompiling the model.
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,479 on at
    To display get the sum of all purchase order lines on purchase order header
    hi 
    martin ,
    i debug the init method of form on forther debugging i got this exception thrown on execute query method of form on super ,i was not coming before as  i added the display method i is keeps appearing on front end ,execute query code is below .
      public void executeQuery()
            {
                QueryBuildDataSource purchTableQueryRunQbds = purchTable_ds.queryRunQueryBuildDataSource();
                QueryBuildRange purchIdRange = purchTableQueryRunQbds != null ? purchTableQueryRunQbds.findRange(fieldNum(PurchTable, PurchId)) : null;
                
                // If there is specific purchase order id filter on the query run, we don't need the exist join with ProjTmpPurchListTable
                if (purchIdRange != null)
                {
                    QueryRun queryRun = purchTable_ds.queryRun();
                    if (queryRun != null && queryRun.query() != null)
                    {
                        QueryBuildDataSource tmpQbds = queryRun.query().dataSourceTable(tableNum(ProjTmpPurchListTable));
                        if (tmpQbds != null)
                        {
                            tmpQbds.enabled(false);
                        }
                    }
                }
    
                logisticsPostalAddressHeader_ds.validTimeStateUpdate(ValidTimeStateUpdate::Correction);
                logisticsPostalAddressHeader_ds.query().validTimeStateDateTimeRange(DateTimeUtil::minValue(), DateTimeUtil::maxValue());
                
                super();
            }
     
  • Martin Dráb Profile Picture
    Martin Dráb 230,962 Most Valuable Professional on at
    To display get the sum of all purchase order lines on purchase order header
    Please use the debug to find more information about the problem.
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,479 on at
    To display get the sum of all purchase order lines on purchase order header
    hi  martin , 
     
    i am getting the value on the front end , but when i open the form i am getting this error where should i debug for this can you please tell me .
     
     
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,962 Most Valuable Professional on at
    To display get the sum of all purchase order lines on purchase order header
    It's very strange that you see a value in debugger, because your code is obviously wrong.
     
    You're filtering the query by purchTable.PurchId, but you've never assigned any value to purchTable variable, therefore the ID will always be empty. Therefore you're trying to sum lines that has no PurchId, which makes no sense. You should use this.PurchId instead - PurchId of the current record.
     
    Also, throw away group by PurchId. There is point in grouping lines by purchase ID if our filter guarantees that we'll get lines for a single order.
     
    Therefore your code could look like this:
    [ExtensionOf(tableStr(PurchTable))]
    final class PurchTablePOtotalValue_Extension
    {
        [SysClientCacheDataMethod]
        display PurchLineAmount getPoTotalValue()
        {
            PurchLine purchLine;
    
            select sum(LineAmount) from purchLine
                where purchLine.PurchId == this.PurchId;
    
            return purchLine.LineAmount; 
        }
    }
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,479 on at
    To display get the sum of all purchase order lines on purchase order header
    hi girish ,
     martin ,
     currency is not issue all the lines will be of same currency , i want to display the sum of line amount on before currency on list page of all purchase order on this form , i have written code on purchtable extension my code is below but i am not getting any value the debugger is showing value  there . 
    [ExtensionOf(tableStr(PurchTable))]
    final class PurchTablePOtotalValue_Extension
    {
         display PurchLineAmount GetPOtotalValue()
        {
            PurchTable         purchTable;
            PurchLine          purchLine;
            PurchLineAmount    POtotalValue;
    
    
            select sum(LineAmount) from purchline group by PurchId
                where  purchLine.PurchId == purchTable.PurchId;
    
            POtotalValue = purchLine.LineAmount;
    
            return   POtotalValue; 
        }
    
    }
    
  • Martin Dráb Profile Picture
    Martin Dráb 230,962 Most Valuable Professional on at
    To display get the sum of all purchase order lines on purchase order header
    Don't forget that the amounts may be in different currencies. If you add, say, 100 USD and 50 CNY, you get 150, but 150 of what? This number is useless, unless you can guarantee that the only one currency will ever be used.
  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    To display get the sum of all purchase order lines on purchase order header
    Hi Dinesh,
     
    Instead of adding the Line Amount total to the header, I would tell them to use D365 FO personalization options like Right click the required column and select Total this column >> Sum.
    If you still need that field in a header, you can create a display method as Martin suggested which will be an easier way.
     
     
    Thanks,
    Girish S.
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,479 on at
    To display get the sum of all purchase order lines on purchase order header
    hi martin ,
              thanks for reply 
           i will try to do it with display method as of now  if it not works i will try for second option .
     
     
    thanks ,
     regards,
    Dinesh
     
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,962 Most Valuable Professional on at
    To display get the sum of all purchase order lines on purchase order header
    You have two main options:
    1. Use a display method. Then you don't have a problem with added or removed lines, because the method will always calculate the current value. The drawback is performance, especially if you show many orders in a grid, and inability to filter or sort by this field (which may be irrelevant for you. Don't forget display method caching.
    2. Create a field to store the value. Then there is no performance penalty associated with showing the value and you can filter and sort the field. But... you need extra code to keep the value consistent, which has its performance implications and might even lead to update conflicts. You'd need to update the field every time when a line is created, deleted or the line amount changes.
    By the way, don't forget that different lines may have LineAmount in different currencies and simply adding them together may give you a meaningless number. You'll likely need to use lineAmountMST() method instead of LineAmount field, which will again have negative performance implications.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,160 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,962 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans