Question -Roll up child Count/Sum/Average in UI only field that I can use to limit results shown in table

  • 1
  • Question
  • Updated 3 years ago
  • Answered
I want a roll up field that will show me the total number of of child records matching certain criteria from a rolling date range. You can't do the rolling date ranges in Salesforce roll-ups. You have to buy another (rather expensive) app. Plus salesforce limits your number of roll-ups. I then need to be able to filter results displayed in a table based on this field.
Example: Parent Object named TREES and Child Object named APPLES
I need to display only TREES that grew at least 5 APPLES This month.

Salesforce roll ups don't allow for "This Month" filter. Can I formulate a UI field to display the number of apples the tree grew this month and then filter the results in my table based on whether that UI field is greater than 4?
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb

Posted 3 years ago

  • 1
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
You could do this using javascript and child relationships in the one model.



This XML will work with Agile Accelerator.

<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" showheader="true" tabtooverride="agf__ADM_Epic__c">
   <models>
      <model id="agfADMEpic" limit="100" query="true" createrowifnonefound="false" sobject="agf__ADM_Epic__c" adapter="" type="">
         <fields>
            <field id="Name"/>
            <field id="CreatedDate"/>
            <field id="agf__Work__r" type="childRelationship" limit="10">
               <conditions>
                  <condition enclosevalueinquotes="false" operator="=" type="fieldvalue" field="CreatedDate" value="THIS_MONTH"/>
               </conditions>
               <fields>
                  <field id="Name"/>
               </fields>
            </field>
         </fields>
         <conditions/>
         <actions/>
      </model>
   </models>
   <components>
      <pagetitle model="agfADMEpic" uniqueid="sk-d4CLi-97">
         <maintitle>
            <template>{{Model.labelPlural}}</template>
         </maintitle>
         <subtitle>
            <template>Home</template>
         </subtitle>
         <actions>
            <action type="savecancel"/>
            <action type="multi" label="Remove Epics w/ less then 5 Stories">
               <actions>
                  <action type="custom" snippet="remove"/>
               </actions>
            </action>
         </actions>
      </pagetitle>
      <skootable showconditions="true" showsavecancel="false" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="true" model="agfADMEpic" mode="read" uniqueid="sk-d4CLi-98">
         <fields>
            <field type="COMBO" valuehalign="">
               <label>User Stories</label>
               <template>{{agf__Work__r.totalSize}}</template>
            </field>
            <field id="Name"/>
         </fields>
         <rowactions>
            <action type="edit"/>
            <action type="delete"/>
         </rowactions>
         <massactions usefirstitemasdefault="true">
            <action type="massupdate"/>
            <action type="massdelete"/>
         </massactions>
         <views>
            <view type="standard"/>
         </views>
      </skootable>
   </components>
   <resources>
      <labels/>
      <css/>
      <javascript>
         <jsitem location="inlinesnippet" name="remove" cachelocation="false">var params = arguments[0],
	model = params.model,
	$ = skuid.$;

$.each(model.getRows(),function(r,row){
    if(typeof row.agf__Work__r === 'undefined' || (typeof row.agf__Work__r !== 'undefined' &amp;&amp; row.agf__Work__r.totalSize &lt; 5)){
        model.abandonRow(row);
    }
});</jsitem>
      </javascript>
   </resources>
   <styles>
      <styleitem type="background" bgtype="none"/>
   </styles>
</skuidpage>
 
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Raymond - In addition to Pat's suggestion, check out the Declaritive Lookup Rollup Summary Tool.  It's a free open source package that provides way more features than standard Rollup Summary fields do.  You can specify your conditions allowing you to rollup values that fall in a date range.

Some community chatter about it:
https://community.skuid.com/skuid/topics/ive-recently-discovered-declarative-rollups-for-lookups

Link to the tool:
https://github.com/afawcett/declarative-lookup-rollup-summaries
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
I was trying to think of a way for this to work, but doesn't DLRS only work based on triggers? A rolling date range doesn't seem to be setup to manage something of this type since the values are stored in static fields.

The solution needs to provide the records based on the current time.

There is a proposed idea that has a lot of votes already that would accommodate this.

I can see creating a Timeframe picklist field on Trees for the Timeframe. ie. This Month.
Then I'd create a checkbox formula field on Apples that checks to see if Apple is within Tree Timeframe.

CASE(TEXT(Tree__r.Timeframe__c),"This Month",
AND( 
MONTH( DATEVALUE(CreatedDate ) ) == MONTH( TODAY() ), 
YEAR( DATEVALUE(CreatedDate )) == YEAR( TODAY() ) 
),FALSE)

Then a rollup on Tree, with this idea, would be a snap.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hey Pat -

Maybe I'm underthinking it or not understanding exactly what the use case is here.  But with DLRS, you can specify the actual WHERE claused used for querying child records.  Given that, I believe you can just use TODAY(), THISMONTH(), etc.  To be honest, I've never tried but I think it should work.  

Assuming it does, you could write the WHERE clause to COUNT() the number of apples this month.  From there, you could use the Scheduling feature of DLRS to run the recalc whenever you'd like as a background job.

There are multiple ways to use DLRS.  The part I'm using the most (as you'll see in my committs to that project :)) are the developer API features.  That said, you can use process builder, scheduling, realtime, etc.  Lots of awesome options in DLRS.

Short story, is I think it would work assuming it was OK that the data was refreshed on whatever schedule you built the rollup to run as.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
Ah .... the scheduling feature. How does that work? I see it as an option for "Calculation Mode" but it isn't intuitive as to how it works or how to set it up.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
TBH, I've never actually used it, just know it's there.

This might help - https://andyinthecloud.com/2014/02/09/new-release-spring14-declarative-rollup-summary-tool/
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Fantastic! It would have taken me 7 years to write that snippet. I haven't tried this yet, but I'm going to try to add it as a model action on query. I don't want to have to click a button to run it. Do you think that would work? Guess I'll find out!
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Barry's Option looks great too! It is like a smorgasbord of options! Pat's code looks like a good example of a custom action!
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
I use DLRS a lot and it's awesome.

You can use Scheduler simply by setting the calculation mode to: Scheduled

then go to setup - Apex classes---> Schedule Apex-->Schedule the class: RollupJob.

Here a basic sample of a random one of mine i currently use to roll up the most recent call on account



The documentation is not great on that tool, but the tool is

So if you need help, feel free to ask anytime

Enjoy
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Thanks Dave! How tightly can you schedule them? Also, I assume it could be executed by a trigger as indicated by Barry and Pat. For one of my use cases, I would need the Rollup to calculate upon saving a record.
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hey Raymond -

If you mark the rollups to Calculation Mode 'Realtime', DLRS provides a button during rollup configuration to deploy a trigger to the child SObject.  Once this trigger is deployed, it will execute the rollups real-time.  DLRS even includes a test class to provide proper test coverage to the deployed trigger.  In short, you get real-time coverage without having to write a line of Apex :)

For the scheduled stuff, I'll leave that to Dave to provide insight since it sounds like he's more familiar than I am with that configuration option :).
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
If you want to rollup upon saving then I recommend to use the default calculation mode: 'Real time"
This is what i use in 95% on my cases...

that way once u save record  the Rollup will execute in real time.

As well to make sure all 'historical records' in database have the proper rollup values , DLRS provided a function for that.

On top of page , click button 'Calculate' and enter condition (optional) and run job. you can follow progress in apex job monitoring. 

Regarding Scheduling, I only have 2 of them scheduled (on 2 different object), so not 100% sure ,but i believe there is only 1 Class for Schedule, therefore all scheduled rollups will execute on the days/time you specified at once

Hope it helps
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
I am having trouble with DLRS. After install, when I navigate to the app it gives me an error message indicating that it is unable to connect to metadata API. It then gives instructions for adding a remote site setting manually, or clicking a button for the app to do it for you. Neither remedy the problem. Did any of you have this issue when you installed it?
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Hey Raymond -

I haven't had a problem with this.  Make sure that your user and/or profile has Api access enabled.  Short of that, the DLRS Issues list is the best place to go for this type of information.  Here's a link that will take you directly to all issues that contain the words "remote site".  Hopefully one of these will drive you to a solution.

https://github.com/afawcett/declarative-lookup-rollup-summaries/issues?utf8=%E2%9C%93&q=remote+s...
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Hi Raymond,

It's been a while I installed it, but i do not recall any issue with installation.

Link Barry gave should be able to help.
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Thanks guys, I'll take a look.