Strangeness when Using UI-Only Field on Aggregate Model to Sum From Another Aggregate Model

  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have two aggregate models in one page to count the Ids and group by a 5-option picklist from Leads and Contacts.

The leads do not have any of one option from the picklist, where contacts have several thousand of that option selected.

The two models look alike, however, I created a UI-only aggregation on the contacts model that looks at the leads model to add the Id count on the leads model to the Id count on the contacts model to give a sum.

This works well when both models have a non-zero number in the same grouping, ex: if both models have a count in the grouping for "Marketing" status, then I get the sum.

The problem is when one model has an Id count for another status such as "Unqualified," let's say 23 for contacts - and the other model leads does not have any rows that would aggregate to "Unqualified," instead of the contacts model UI-only field stating 23 for "Unqualified," the number the UI-only field reports is 0.

            | Contact | Leads | Combo (UI-only on Contact Model)
Unqualified |   23    | NULL  |    0   
Marketing   |  104    |   21  |  125 
Sales       |   56    |   15  |   71
Won         |    9    | NULL  |    0
Lost        | NULL    |    2  | NULL
Combo's formula is: {{countId}}+MODEL_LOOKUP("Leads","countId","marketingStatusc",{{marketingStatusc}})

  • On an aggregate model grouped by a field, should a UI-only field fail to register a number at all when added with the result of a Model_Lookup function that did not return a number?
  • Off the top of your head is there a better way to combine the results of two aggregate models split on fields with the same options in their picklists?
Thanks.
Photo of David Lin

David Lin

  • 516 Points 500 badge 2x thumb

Posted 1 year ago

  • 1
Photo of David Lin

David Lin

  • 516 Points 500 badge 2x thumb
So, after looking at the models data with a table, I've discovered that Combo is actually null wherever there was a count on Contact but not on leads. It was my visualization that was inserting/assuming the 0.

            | Contact | Leads | Combo (UI-only on Contact Model)
Unqualified |   23    | NULL  | NULL   
Marketing   |  104    |   21  |  125 
Sales       |   56    |   15  |   71
Won         |    9    | NULL  | NULL
Lost        | NULL    |    2  | NULL
This is a far more understandable behavior, although I still think the desired behavior is that Model_Lookup merely returns a null whenever it does not find a row in the model and doesn't invalidate the whole formula it is a part of.
Photo of David Lin

David Lin

  • 516 Points 500 badge 2x thumb
Workaround was to use another UI-Only field to isolate the nullification of the result. So the first UI-only field uses the Model_Lookup function and the second UI-only field adds the count and other UI-only field. This gives me the desired result.