Skip to main content

Hi, all. I’m hoping you can help. I’m just starting to try to solve this problem and am researching the best approach. I’m building a table that I’m hoping to use to aggregate the results of different model conditions and so far what I’m coming up with seems needlessly complex. 

What I’d like to display is:


Requests  ||  New  ||  Total  || % Service 1 || 1 || 10 || 10.0% Service 2 || 4 || 16 || 25.0% TOTAL || 5 || 26  || 19.2% 

I built an aggregate model to show all requests that were received in the given time period and another to display all of them that were in New status. I don’t know how to get them in the same row or add the percentage UI-Field formula so that it will perform the calculations on each row rather than overall.

Any suggestions? Thanks in advance for your help.

Hi Shane, if you are just looking to display the results, you may be able to use a template component, or template fields in a table. Global Merge Syntax will let you pull data from other models and fields and display it anywhere you want. 

If your use case is more involved and requires that your aggregations are displayed in context of other objects, you may find this recent post involving UI-Only formula fields and the MODEL_LOOKUP function helpful as you look for a solution that works for you. 


Thanks for the heads up, Mark. I’ll take a look at these references.



The following is the XML to create the beautiful table that is shown above. Your data may vary based on your own org, but nothing on this page is custom.


This takes Groupings, Aggregate Models, Formula Fields, Models arranged in the right manner, PEMDAS, and patience to accomplish.


Both tables you’re looking at combine two models by using the MODEL_LOOKUP that Mark is referring too. They are robust and take some time to work through to understand.


Unfortunately, at this time, we are not able to call upon column summaries declaratively, nor are we able to manipulate them to make a weighted average like your post has indicated. Hopefully, we’ll be able to do this in the future. If it is something that interests you, write it up in the community as an idea and vote “me too” if it is posted below.


If you have any questions on how this was built, please ask.


MODEL_LOOKUP("CaseAggregate1","countId","type",{{type}})


{{Bringing_in_New_Status_From_CaseAggregate1}}/{{countId3000}}*100












MODEL_LOOKUP("CaseAggregate","countId3000","type",{{type}})
























{{$Model.Summation_of_New_Status.data.0.countStatus_here}}


{{NewStatusCount}}/{{countStatus}}












New

sumsum

Total


Percentage

avg














New


Total


Weighted Average

Did this help you out Shane?


Stephen, I’m still playing with it to get it to work, but not having much luck at this point. I need to go through your code a little deeper. Will update if I have any luck or not. Thanks for the reply!


No worries Shane. I didn’t know whether to dive into a deep tutorial or whether just seeing it and playing with it would be enough.

Please,  don’t hesitate to ask if you think it would be helpful.


How are you doing Shane?


Late to the party, but I was able to do this by creating an empty model used javascript to traverse my other data and populate this model.  Because I stored all the data in a model instead of writing directly to a template component, I was able to use the data in standard table and field editor page components. This is assuming that javascript is a viable alternative for you.  Hope this helps!