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?
