How can I add the results of 2 aggregate models?

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!

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?

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. 

Unfortunately that did not work.  The template is just blank.

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?

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.

Still no good.  Thanks though, Matt!

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:

<br />{{$Model&#46;MyOpenLeads&#46;data&#46;0&#46;countLeadId}} + {{$Model&#46;MyOpenContacts&#46;data&#46;0&#46;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:

<br />{{$Model&#46;MyOpenLeads&#46;data&#46;0&#46;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.

Gary,

Try the ‘triple mustache’ for your formula:

{{{$Model&#46;MyOpenLeads&#46;data&#46;0&#46;countLeadId}}} + {{{$Model&#46;MyOpenContacts&#46;data&#46;0&#46;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

Sorry Bill, I should have clarified that I tried with both double and triple mustaches. 

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

{{{$Model.OppQual.data.0.oppQualCount}}} + {{{$Model.OppNeeds.data.0.oppNeedsCount}}} {{Model.labelPlural}} Home &lt;p&gt;&lt;span style="font-size:22px;"&gt;Qualification: {{oppQualCount}}&lt;/span&gt;&lt;/p&gt; &lt;p&gt;&lt;span style="font-size:22px;"&gt;Needs Analysis: {{oppNeedsCount}}&lt;/span&gt;&lt;/p&gt; &lt;p&gt;&lt;span style="font-size:22px;"&gt;Total: {{Total}}&lt;/span&gt;&lt;/p&gt; (function(skuid){ var $ = skuid.$; $(document.body).one('pageload',function(){ var tablecomponent = skuid.$C('sk-3Bsoue-81');
console.log(tablecomponent);

});
})(skuid);






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.