How can I add the results of 2 aggregate models?

  • 1
  • Question
  • Updated 11 months ago
  • In Progress
  • (Edited)
I've read through many of the posts about the MODEL_LOOKUP formula but it just isn't clicking for me, so I apologize if this question has already been answered elsewhere.

Here's is what I'm trying to do:

I'm trying to display the total of 2 aggregate models (MyOpenContacts and MyOpenLeads) in a 3rd Ui-only model called "MyOpenProspects".

I've confirmed that my aggregate models are working correct for the Leads and Contacts, and for the time being, I'm displaying them separately as shown below:


(1 Open Contact & 1 Open Lead) - obviously this isn't an idea solution.


Here's what I tried, which I thought would work but doesn't (silly me!):

  1. Created an Ui-Only model called "MyOpenProspects" 
  2. Checked the "Create default row if Model has none"
  3. Created a formula field called "ProspectCount" (shown below)
  4. Dropped the formula field into a Template component with the MyOpenProspects as the model source.



Here's my formula as shown in the field:



Here's my template containing the formula field:




What are all of the things I'm doing wrong?

What is the best practice for accomplishing what I am trying to do?

Thanks!
Photo of Gary Bailey

Gary Bailey

  • 1,628 Points 1k badge 2x thumb

Posted 11 months ago

  • 1
Photo of Gary Bailey

Gary Bailey

  • 1,628 Points 1k badge 2x thumb
Am I complicating matters by using a Ui-Only model when all I really need is a Ui-Only field on one of other models?
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
Hi Gary, it looks like the two fields are being concatenated instead of added. Can you try enclosing the formulas with VALUE() to get their numerical value? Something like VALUE({{{$Model.MyOpenLeads.dta.0.countLeadId}}}) + VALUE({{{$Model.MyOpenContacts.dta.0.countContactId}}}) might work. Also, be sure you're using more than one mustache bracket whenever you're in Skuid. I think triple brackets might be the right choice here, as that just takes the field's raw data. 
Photo of Gary Bailey

Gary Bailey

  • 1,628 Points 1k badge 2x thumb
Unfortunately that did not work.  The template is just blank.
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
A few follow-up thoughts:
  • When you set up the UI-only formula field, make sure the Formula Return Type is Number.
  • Make sure the two fields you're adding have values in them before MyOpenProspects loads. For this, ensure that your MyOpenProspects model is listed below the other two models it's pulling numbers from, as the models will load in order from top to bottom of the list.
  • You probably don't need the VALUE() portion after all, considering that you're using aggregations on another model. Those should already be of the right field type to be added.
  • I think a UI-only model should work fine for this. However, if you wanted to try and add this formula field to another model, perhaps you'd want to add it to a model on the User object. You can also add it to one one of the two aggregate models for Contacts and Leads, but sometimes it's useful to keep a formula / sum like this separate from the models it's looking at.
I've been trying to reproduce what we see in the top screenshot (1 + 1) but it sounds like that's not what your formulas have been outputting. Can you clarify what, if anything, displays when using the formula in the 3rd screenshot?
(Edited)
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Gary,

You need to uncheck "do not run template on each row".

That's probably the most poorly named property in skuid... but you want the template to process your merge syntax in row mode, and checking that box means it will only process model and global merge syntax.
Photo of Gary Bailey

Gary Bailey

  • 1,628 Points 1k badge 2x thumb
Still no good.  Thanks though, Matt!
Photo of Gary Bailey

Gary Bailey

  • 1,628 Points 1k badge 2x thumb
The problem seems to be with using this format inside formula fields:  {{$Model.MyOpenLeads.data.0.countLeadId}}

If I make that exact code the content of a template component, it displays the number of Open Leads (which is the output of the "MyOpenLeads" aggregate model).

In my example, I can put the following as the content of a template field:

{{$Model.MyOpenLeads.data.0.countLeadId}} + {{$Model.MyOpenContacts.data.0.countContactId}}

And it will display (without quotes):  "4 + 8" 

The "4" is the result of the MyOpenLeads aggregate model and the "8" is the result of the MyOpenContacts aggregate model .   Makes sense.

However,  if I create a formula field on any other model on my page (i set the output to numeric) and make the content of the formula:

{{$Model.MyOpenLeads.data.0.countLeadId}}

...and then drop that formula field into a template, it returns a blank value.  So the problem isn't with the calculation of trying to add the output of the 2 aggregate models, it's that the formula field doesn't recognize that particular format.  

The format appears to work fine for outputting to templates, but not for inclusion in any type of calculations.  

Maybe someone who knows more about these things would care to weigh in.  I feel like I'm missing something really obvious.
(Edited)
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
Gary,

Try the 'triple mustache' for your formula:
{{{$Model.MyOpenLeads.data.0.countLeadId}}} + {{{$Model.MyOpenContacts.data.0.countContactId}}}
When you use the triple braces for the merge syntax, you are telling Skuid to 'give me the raw value' (i.e. don't apply any formatting).  This worked for me in a UIOnly model.

The other thing to check is that you have set the 'Formula Return Type' to 'Number'.

Thanks,

Bill
(Edited)
Photo of Gary Bailey

Gary Bailey

  • 1,628 Points 1k badge 2x thumb
Sorry Bill, I should have clarified that I tried with both double and triple mustaches. 
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
Gary,

What version of Skuid are you using?

Here is a sample page that works using the triple mustache.  It's based on Opportunities.  This is from Skuid 11.0.3

Thanks,

Bill

<skuidpage unsavedchangeswarning="" personalizationmode="server" showsidebar="true" showheader="true" tabtooverride="Opportunity">
    <models>
        <model id="Opportunity" limit="100" query="true" createrowifnonefound="false" sobject="Opportunity" type="" datasource="salesforce">
            <fields>
                <field id="Name"/>
                <field id="CreatedDate"/>
                <field id="AccountId"/>
                <field id="Account.Name"/>
                <field id="StageName"/>
            </fields>
            <conditions/>
            <actions/>
        </model>
        <model id="OppQual" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="aggregate" sobject="Opportunity">
            <fields>
                <field id="Id" name="oppQualCount" function="COUNT"/>
            </fields>
            <conditions>
                <condition type="fieldvalue" value="Qualification" enclosevalueinquotes="true" field="StageName"/>
            </conditions>
            <actions/>
            <groupby method="simple"/>
        </model>
        <model id="OppNeeds" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="aggregate" sobject="Opportunity">
            <fields>
                <field id="Id" name="oppNeedsCount" function="COUNT"/>
            </fields>
            <conditions>
                <condition type="fieldvalue" value="Needs Analysis" enclosevalueinquotes="true" field="StageName"/>
            </conditions>
            <actions/>
            <groupby method="simple"/>
        </model>
        <model id="Total" query="true" createrowifnonefound="true" datasource="Ui-Only" processonclient="true">
            <fields>
                <field id="Total" displaytype="FORMULA" label="Total" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="4" scale="0">
                    <formula>{{{$Model.OppQual.data.0.oppQualCount}}} + {{{$Model.OppNeeds.data.0.oppNeedsCount}}}</formula>
                </field>
            </fields>
            <conditions/>
            <actions/>
        </model>
    </models>
    <components>
        <pagetitle model="Opportunity" uniqueid="sk-3Bsoue-80">
            <maintitle>
                <template>{{Model.labelPlural}}</template>
            </maintitle>
            <subtitle>
                <template>Home</template>
            </subtitle>
            <actions>
                <action type="savecancel" uniqueid="sk-1sTJR2-232"/>
            </actions>
        </pagetitle>
        <grid uniqueid="sk-1_HxNb-443" columngutter="4px" rowgutter="2px">
            <divisions>
                <division behavior="flex" minwidth="100px" ratio="1">
                    <components>
                        <wrapper uniqueid="sk-1_pL1k-428">
                            <components>
                                <richtext multiple="false" uniqueid="sk-1_HzED-465" model="OppQual">
                                    <contents>&lt;p&gt;&lt;span style="font-size:22px;"&gt;Qualification: {{oppQualCount}}&lt;/span&gt;&lt;/p&gt;
</contents>
                                </richtext>
                            </components>
                            <styles>
                                <styleitem type="background"/>
                                <styleitem type="border" borders="all" margin="all" padding="left,">
                                    <styles>
                                        <styleitem property="border" value="4px solid #2196f3"/>
                                        <styleitem property="padding-left" value="4px"/>
                                        <styleitem property="margin" value="2px"/>
                                        <styleitem property="box-sizing" value="border-box"/>
                                    </styles>
                                </styleitem>
                                <styleitem type="size"/>
                            </styles>
                        </wrapper>
                    </components>
                </division>
                <division behavior="flex" verticalalign="top" minwidth="100px" ratio="1">
                    <components>
                        <wrapper uniqueid="sk-1_pgdv-608">
                            <components>
                                <richtext multiple="false" uniqueid="sk-1_HzoJ-474" model="OppNeeds">
                                    <contents>&lt;p&gt;&lt;span style="font-size:22px;"&gt;Needs Analysis: {{oppNeedsCount}}&lt;/span&gt;&lt;/p&gt;
</contents>
                                </richtext>
                            </components>
                            <styles>
                                <styleitem type="background"/>
                                <styleitem type="border" borders="all" margin="all" padding="left,">
                                    <styles>
                                        <styleitem property="border" value="4px solid #2196f3"/>
                                        <styleitem property="padding-left" value="4px"/>
                                        <styleitem property="margin" value="2px"/>
                                        <styleitem property="box-sizing" value="border-box"/>
                                    </styles>
                                </styleitem>
                                <styleitem type="size"/>
                            </styles>
                        </wrapper>
                    </components>
                </division>
                <division behavior="flex" verticalalign="top" minwidth="100px" ratio="1">
                    <components>
                        <wrapper uniqueid="sk-1_q3H8-782">
                            <components>
                                <richtext multiple="false" uniqueid="sk-1_IqLg-762" model="Total">
                                    <contents>&lt;p&gt;&lt;span style="font-size:22px;"&gt;Total: {{Total}}&lt;/span&gt;&lt;/p&gt;
</contents>
                                </richtext>
                            </components>
                            <styles>
                                <styleitem type="background"/>
                                <styleitem type="border" borders="all" margin="all" padding="left,">
                                    <styles>
                                        <styleitem property="border" value="4px solid #2196f3"/>
                                        <styleitem property="padding-left" value="4px"/>
                                        <styleitem property="margin" value="2px"/>
                                        <styleitem property="box-sizing" value="border-box"/>
                                    </styles>
                                </styleitem>
                                <styleitem type="size"/>
                            </styles>
                        </wrapper>
                    </components>
                </division>
            </divisions>
            <styles>
                <styleitem type="background" bgtype="none"/>
            </styles>
        </grid>
        <skootable showconditions="true" showsavecancel="false" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="true" model="Opportunity" mode="read" uniqueid="sk-3Bsoue-81" buttonposition="">
            <fields>
                <field id="Name" allowordering="true" uniqueid="sk-1sTJRE-238"/>
                <field id="AccountId" hideable="true" uniqueid="fi-1sTSWt-298" valuehalign="" type=""/>
                <field id="StageName" hideable="true" uniqueid="fi-1_HXV1-265" valuehalign="" type=""/>
                <field id="CreatedDate" allowordering="true" uniqueid="sk-1sTJRG-241"/>
            </fields>
            <rowactions>
                <action type="edit"/>
                <action type="delete"/>
            </rowactions>
            <massactions usefirstitemasdefault="true">
                <action type="massupdate"/>
                <action type="massdelete"/>
            </massactions>
            <views>
                <view type="standard"/>
            </views>
            <filters>
                <filter type="select" filteroffoptionlabel="Any Account" createfilteroffoption="true" affectcookies="false" autocompthreshold="25" conditionsource="manual" filtermethod="server" labelmode="no" condition="AccountId">
                    <sources/>
                </filter>
            </filters>
        </skootable>
    </components>
    <resources>
        <labels/>
        <css/>
        <javascript>
            <jsitem location="inline" name="newInlineJS" cachelocation="false" url="">(function(skuid){
var $ = skuid.$;
$(document.body).one('pageload',function(){
    var tablecomponent = skuid.$C('sk-3Bsoue-81');
    
    console.log(tablecomponent);
});
})(skuid);</jsitem>
        </javascript>
        <actionsequences uniqueid="sk-1sTSWe-293"/>
    </resources>
    <styles>
        <styleitem type="background" bgtype="none"/>
    </styles>
</skuidpage>
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
Thank you Bill for providing this example. Gary, I'm wondering if the formula field is being evaluated before the aggregate models have data in them. If you can share the version of Skuid you're on, and let us know if the above example behaves as expected, that will be helpful. If the page is simple enough and you're comfortable sharing, being able to look at the XML of this page may also shed some light.