I have an issue with a formula saved search where I want to show the sum of a project's related transactions in one column for the given date range, but I also want to show a specific joined custom record for this project as well. It would be similar to trying to pull data from 2 specific linked messages or cases in two separate columns.
So, there is no criteria. It is a project search for all projects.
Here is the results setup:
Name > Group
Formula (Currency) > Sum -- case when {transaction.posting} = 'T' then case when {transaction.account} = '11050.3550 Work in Progress - Parent Co.' or {transaction.account} like '11000.1200%' or {transaction.account} like '11000.1300%' or {transaction.account} like '11200.1000%' or {transaction.account} like '11200.1100%' or {transaction.account} like '11240.1300%' then {transaction.amount} end end
Formula (Currency) > Max -- case when {custrecord_project_name.custrecord_year}='2018' and {custrecord_project_name.custrecord_revision}='Nov 2017 Budget Cycle' then {custrecord_project_name.custrecord_total_budget} end
Formula (Currency) > Max -- case when {custrecord_project_name.custrecord_year}='2019' and {custrecord_project_name.custrecord_revision}='Nov 2017 Budget Cycle' then {custrecord_project_name.custrecord_total_budget} end
When I click RUN, the results are showing the values for the last two columns just fine, but if there are 4 project budget records attached to the project, the Sum of the transactions amount is quadrupled. If there are 2, it is doubled.
I have tried something like the below, but this returned an unexpected error:
Formula (currency) > Sum -- sum (case when {transaction.posting} = 'T' then case when {transaction.account} = '11050.3550 Work in Progress - Parent Co.' or {transaction.account} like '11000.1200%' or {transaction.account} like '11000.1300%' or {transaction.account} like '11200.1000%' or {transaction.account} like '11200.1100%' or {transaction.account} like '11240.1300%' then {transaction.amount} end end) /COUNT({custrecord_project_name.internalid})
I feel like there must be a way to do this - maybe with keep dense rank? Any help is appreciated.