Trying to Join aggregate/grouped model with basic model on account Id


Like the title says I am trying accomplish effectively what would be a simple JOIN clause would produce in SQL between 2 models I have. One being an Aggregate model, the other being Basic and both having the account Id in common. 


Agg Model Basic Model<br /> _Account__|__# of Child Accounts__ _Account__|__Columns__ A | 1 A | data B | 8 B | data C | 1 C | data D | 4 D | data Want: _Account__|__# of Child Accounts__|__Columns etc__ A | 1 | data B | 8 | data C | 1 | data D | 4 | data

The aggregate model counts accounts and groups them by parent, resulting in a list of accounts that corresponds with the accounts in the basic model (the parent accounts). 

Does anyone know if/how this may be possible?

Yes.  Quite possible. 

Create a UI Only Formula field on one of the models that retrieves the relevant related data from the other.  Then use that first model in your table, or whatever. 

Let’s unpack a little: 

- Use your base model to start. 
- Add UI Only field - choose option “Formula”
- In the formula pane select the “MODEL_LOOKUP” function. 
- The syntax will look somthing like this:   MODEL_LOOKUP(‘AggModel’,‘SumofChildAccounts’,‘AccountId’,{{AccountId}})

Some pointers. 
- First three parameters (model to lookup,  API name of field to return,  API name of field to match on in lookup model) are all surrounded in quotes. 
- Last parameter (field from base model) is surrounding in merge syntax.  You are merging this data into the formula and passing it off to process against the other one. 
- When going at a Aggregate model,  use use the Alias of the field names to return and match, rather than the display name. 

And there you go.  SumOfChildAccounts is now in the same model as say “account type” or “billing state” and you can put it in a table, or chart, or whatever… 

One point of concern here:  If you have more than about 300 accounts  - you have some tricky thinking to do about how to make sure both models have the same conditions and so return the same account records.  You might build all your conditions on your “lookup” model,  and then add a condition on the base model that uses “field from another model – IN” statement - so it only returns accounts that are in the lookup model.  Also note that model order here is important.  Put the base model AFTER the Lookup model. 

Have fun !