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

(1) Share

Report

Posted on by Dineshkarlekar

1,836

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

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

Martin Dráb 238,828 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
You have two main options:

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.

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.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
GirishS 27,833 Moderator on at

Like (2)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Martin Dráb 238,828 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
Copy link

Link copied!
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; } }

Was this reply helpful? Yes No
Verified answer

Martin Dráb 238,828 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
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; } }

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
Copy link

Link copied!

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 .

Was this reply helpful? Yes No
Martin Dráb 238,828 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Please use the debug to find more information about the problem.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (0)

Report
Copy link

Link copied!
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(); }

Was this reply helpful? Yes No
Martin Dráb 238,828 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

First of all, try recompiling the model.

Was this reply helpful? Yes No