Coalesce Function

Manoj Aggarwal
edited September 3, 2019 in Questions

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?


  • Mike Dwyer
    edited September 3, 2019
    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).

  • Manoj Aggarwal
    edited June 5, 2018
    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 aggregateimage
    Basic Working

    Aggregate not working

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

  • Zach McElrath
    edited June 1, 2018
    If you are on an aggregate model, you will need to use the aggregate alias names within your formula function, e.g. if your fields are aliased as:

    enroller2__c ---> enroller2c
    enroller2__r.Name ---> enroller2rName
    Owner.Name ---> ownerName

    you would do this:


  • Zach McElrath
    edited June 3, 2018
    @Manoj were you able to try using the aggregate fields' alias names?
  • Manoj Aggarwal
    edited June 4, 2018
    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?
  • Zach McElrath
    edited June 4, 2018
    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.
  • Manoj Aggarwal
    edited June 4, 2018
    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
  • Manoj Aggarwal
    edited June 5, 2018
    I tried to "backdoor" it by editing the xml and got this error when i tried to view itimage

    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"/>
  • Bill McCullough
    Bill McCullough 💎💎
    edited June 5, 2018

    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.


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!