Coalesce Function

  • 1
  • Question
  • Updated 2 years ago
  • Answered

I have two fields on my opportunity that relate to who made a sale "Opportunity Owner" and "Enroller" ; the Enroller field is always the more true of the two fields though isn't always filled out so when I create a skuid report i want to be able to say

if enroller is blank use Opportunity owner otherwise use Enroller

this would be simple in Excel or using SQL though I am finding it difficult to do in skuid

Maybe I need to add a custom field on the opportunity - Thoughts?
Photo of Manoj Aggarwal

Manoj Aggarwal

  • 262 Points 250 badge 2x thumb

Posted 2 years ago

  • 1
Photo of Mike Dwyer

Mike Dwyer, Champion

  • 4,736 Points 4k badge 2x thumb
You could create a UI-Only field in your model, as a Formula type. Conceptually, the formula would be
  • IF ( {{Enroller}}, {{Enroller}}, {{Owner}} )

The actual field names inside the braces may be different. (I don't use standard objects, and the names you provided don't match what I see in the Opportunity object.)

This way, you can use the UI-only field in a table or a field editor. If you only need this in a field editor,
you can accomplish the same with conditional rendering of the two fields: Enroller is not blank (for Enroller), and Enroller is blank (for Owner).
Photo of Manoj Aggarwal

Manoj Aggarwal

  • 262 Points 250 badge 2x thumb
Oh i forgot to mention that i am using an aggregate model not a basic model - i got the formula to work on the basic model not the aggregate
Basic Working

Aggregate not working

Formula used each time was IF(ISBLANK({{enroller2__c}}),{{Owner.Name}},{{enroller2__r.Name}})
Photo of Manoj Aggarwal

Manoj Aggarwal

  • 262 Points 250 badge 2x thumb
Zach thank you for reaching out!

Not really it didn't produce any results - i am not aggravating those fields by the way maybe i should have said this earlier. I am Opportunity Amount and grouping by the Opportunity Owner/Enroller fields would that change anything thing?
Photo of Zach McElrath

Zach McElrath, Employee

  • 54,366 Points 50k badge 2x thumb
So you're saying you are Grouping By Opportunity Owner and Enroller --- what are the Aliases of these two fields? You will need to use the aliases of these grouping fields in your formula.
Photo of Manoj Aggarwal

Manoj Aggarwal

  • 262 Points 250 badge 2x thumb
ahh what ever ill just create the formula in salesforce and bring it over

- Nope that didn't work by that i mean i got the formula to work in salesforce though its not showing up on the grouping area of the model - it shows up in the aggregations though thats not what I want
Photo of Manoj Aggarwal

Manoj Aggarwal

  • 262 Points 250 badge 2x thumb
I tried to "backdoor" it by editing the xml and got this error when i tried to view it

Here is the code

 <model id="Yesterday_Sales_by_Owner" limit="20" query="true" createrowifnonefound="false" datasource="salesforce" sobject="Opportunity" type="aggregate">            <fields>
                <field id="Amount" name="sumAmount" function="SUM"/>
                <field id="NewField" uionly="true" displaytype="FORMULA" ogdisplaytype="TEXT" readonly="true" returntype="TEXT">
                <condition type="fieldvalue" value="YESTERDAY" enclosevalueinquotes="false" field="CloseDate"/>
                <condition type="fieldvalue" value="closed won" enclosevalueinquotes="true" field="StageName"/>
                <condition type="fieldvalue" value="" enclosevalueinquotes="true" field="Owner.FirstName" state="filterableoff" inactive="true" name="Owner.FirstName"/>
            <groupby method="simple">
                <field id="Owner_Enroller_combo__c" name="Owner_Enroller_combo__c"/>
Photo of Bill McCullough

Bill McCullough, Champion

  • 13,612 Points 10k badge 2x thumb

I don't think you can do a Group By on a formula field in Salesforce.  What I would do is create a Text field and add a Workflow rule to set the field with the name of the user from the lookup field that matches your criteria.  You can 'group' a Text field.