Dynamics Community Forum Thread Details

totalcostamount sum calculation oddities

Hello,

There is a standard from in the system called "Posted project transactions" - projProjectTransactionsListPage. I open it upon my project. The final query before form opens looks like that

SELECT FIRSTFAST FORUPDATE * FROM ProjPostTransView(ProjPostTransView_1) 
	ORDER BY ProjPostTransView.TransDate ASC 
		WHERE ((TransDate>={ts '2003-11-12 00:00:00.000'})) AND ((ProjId = N'0151328')) 
	JOIN FORUPDATE * FROM ProjTable(ProjTable_1) 
		ON ProjPostTransView.ProjId = ProjTable.ProjId 
	NOTEXISTS JOIN FORUPDATE * FROM ProjPostTransSaleView(ProjPostTransSaleView_Invoiced) 
		WHERE ProjPostTransView.TransId = ProjPostTransSaleView.TransId AND ((TransStatus = 1)) 
	NOTEXISTS JOIN FORUPDATE 'x' FROM ProjOnAccTransView(ProjOnAccTransView) 
		WHERE ProjPostTransView.TransId = ProjOnAccTransView.TransId AND ((TransactionOrigin = 22)) AND ((PSAIsOnAccount = 1)) 
	OUTER JOIN FORUPDATE * FROM ResourceView(ResourceView) 
		ON ProjPostTransView.Resource = ResourceView.RecId 
	OUTER JOIN FORUPDATE * FROM smmActivities(smmActivities) 
		ON ProjPostTransView.ActivityNumber = smmActivities.ActivityNumber AND ProjPostTransView.dataAreaId = smmActivities.dataAreaId 
	EXISTS JOIN FORUPDATE * FROM ProjTable(ProjTable_2) 
		WHERE ProjPostTransView.ProjId = ProjTable.ProjId}

sum of the TOTALCOSTAMOUNTCUR field on the form is 9423.00 but if i do this in sql studio using T-SQL I get this for the same project

two values are not the same. I have no idea why. the number of records is 40 on form and in sql query i do for this project. There are 40 records in table ProjPostedTransTable for my project.

I looked through the standard code thinking that maybe to TOTALCOSTAMOUNTCUR is added some value but I haven't found anything and it doesn't make any sense.

Looking at Form query object there are should be the same values for TOTALCOSTAMOUNTCUR field and therefore the sum of them should be the same as in sql query. There is no need to repeat the whole query because projtable is joined by inner join and this is the main thing for me. other datasources are added by not exists or outer join. not exists can reduce the number of records what doesn't happen. in both cases there 40 records. outer join doesn't affect the number of records from root datasource as well.

Any help would be appreciated

Thank you.

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL