HI,
Wondering if someone could help.
I'm trying to create a calculated field on an account to return a count of all opps on the account that are at a certain sales stage and type = NEW?
I also want to add in a count per product - 1 opp at sales stage A for product X, 1 Opp at sales stage A for product x .
Thanks
Ger
Ge
Hi Geraldine Hunt,
As André Lopes mentions, it is not currently possible to use Sugar Logic to conditionally count on multiple fields. An alternative approach you could take to this issue is to have a custom field (e.g. opportunity_overview_c) on the Opportunities module that determines whether your desired sales stage and type fields are set to the desired values. The formula would have nested ifElse statements such as:
ifElse(
equal($opportunity_type, "New"),
ifElse(
equal($sales_status, "Closed Won"),
"New Business Won",
"New Business Opportunity"
),
"Existing Business Opportunity"
)
Then, once you have that field created, you would go to your Opportunities list view, select all records and recalculate the values from the list view action menu to update those records with the proper value.
Then, create a custom field on the Accounts module to count any records that meet your criteria using the countConditional formula. Using the above example where the custom field is called 'opportunity_overview_c' and the successful criteria outputs a value in the field of 'New Business Won', the resulting formula would look like:
countConditional($opportunities, "opportunity_overview_c", createList("New Business Won"))
Once that field is created, you will want to do the same on the Accounts list view where you recalculate the values to get the proper rollup count. If you wanted to do counts by product, you would do a similar set up as above between the Accounts and Revenue Line Items modules.
Chris
Hi Geraldine Hunt,
As André Lopes mentions, it is not currently possible to use Sugar Logic to conditionally count on multiple fields. An alternative approach you could take to this issue is to have a custom field (e.g. opportunity_overview_c) on the Opportunities module that determines whether your desired sales stage and type fields are set to the desired values. The formula would have nested ifElse statements such as:
ifElse(
equal($opportunity_type, "New"),
ifElse(
equal($sales_status, "Closed Won"),
"New Business Won",
"New Business Opportunity"
),
"Existing Business Opportunity"
)
Then, once you have that field created, you would go to your Opportunities list view, select all records and recalculate the values from the list view action menu to update those records with the proper value.
Then, create a custom field on the Accounts module to count any records that meet your criteria using the countConditional formula. Using the above example where the custom field is called 'opportunity_overview_c' and the successful criteria outputs a value in the field of 'New Business Won', the resulting formula would look like:
countConditional($opportunities, "opportunity_overview_c", createList("New Business Won"))
Once that field is created, you will want to do the same on the Accounts list view where you recalculate the values to get the proper rollup count. If you wanted to do counts by product, you would do a similar set up as above between the Accounts and Revenue Line Items modules.
Chris
