[julie.kelly]

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.
This is a cached copy. Click here to see the original post.

[Olivier Gagnon NC]

Julieeeeeeeeeeee!!!!

I don't know about the dense_rank, that might work too. But my first instinct would be to do the same you're trying right now. Maybe you're just getting the error because NS always wants you to protect against divisions by zero… so just alter the last bit to be /nullif(COUNT({custrecord_project_name.internalid}) ,0)

Not guaranteeing it'll work, but it's the easiest thing to try first…

Cause I'm also concerned that NS won't like the COUNT command embedded into a SUM group.

[julie.kelly]

Olivieeeeer! I was hoping you might answer.

Thank you so much – I needed to add 'DISTINCT' also, but the nullif got me that much closer. Here is what works (so far):Formula (currency) > Sum — sum (case when {transaction.posting} = 'T' then case when {transaction.account} like '11000.1200%' or {transaction.account} like '11000.1300%' or {transaction.account} like '11100.1300%' or {transaction.account} like '11200.1000%' or {transaction.account} like '11200.1100%' or {transaction.account} like '11240.1300%' then {transaction.amount} end end) /nullif(COUNT(distinct({custrecord_project_name.int ernalid})),0)

[Olivier Gagnon NC]

Good to hear this worked! This comes up as a use case every so often.

[Author]

Posts