Model Lookup, Multiple Grouping

  • 1
  • Question
  • Updated 5 months ago
  • Answered
Hi All, 

I'm trying to use Model Lookup function and I have some problems. Maybe you'll be able to help me. 

Long story short:
a) I have model called Sales Plan (object is Sales Plan also) which is aggregate and grouped by Year&Quarter field + Account field (normally in this object I have 4 records for each year, so I can go with the basic); 

the object Sales Plan is used to store target for every account / quarter of year; 

b) I have object model called Contracts (object is Contract also) that is aggregate and grouped by Year&Quarter field + Account field (normally in this object I have hundred records for each year / account). 

The goal is to show table that will have data:
Year&Quarter | Account | Target | Completion

whichever way I used I'm having duplicated values. The problem is with 2 groups I'm using in the second model i.e. account and year&quarter. 

Example set-up: 
1. Model = "Contracts", aggregated, aggregation "salesresult", groups "year_quarter", "account". 
2. Model = "SalesPlans", basic, field Account__c holding the account. The records in the object already split by year & quarter. 

UI-Only field on 2 model with model lookup: 
MODEL_LOOKUP("Contracts","salesresult","account",{{{Account__c}}})

it shows duplicated value for agencies. 
Photo of Jakub Dulny

Jakub Dulny

  • 166 Points 100 badge 2x thumb

Posted 6 months ago

  • 1
Photo of Stephen Sells

Stephen Sells, Official Rep

  • 16,856 Points 10k badge 2x thumb
Are you able to create a page using standard objects that replicates this behavior and then post the XML here so we can see what your setup is and can begin solving this step by step?
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
Jakub,

I think when you say 'agencies' you mean Account. I think you are seeing duplicate values from your Contracts model since it is grouped by both 'year_quarter' and 'account'. When you do a lookup where 'account = A', you  have multiple rows of 'A' for 2018_01, 2018_02; etc.

Since the Model Lookup function only supports 'lookup' on a single field, try adding a Model UI field on your Contracts and SalesPlans models that concatenates the 'key' that you want to match and bring into your UI only model.  That is, something that combines the 'year_quarter' and 'account'.

Thanks,

Bill