Model Lookup date in Aggregate Model

Nothing Immediately stands out as problematic in your formula.  I assume that “StartDate” and “InvoiceDate” are the alias names of the date fields you have grouped by months. 

One idea.  Maybe the “Goals” model is after the model where this formula field is?  Model order matters here.  The goals model has to be queried before the formula has data to pass along. 

Hope that helps.  

Hey Rob, thanks for the response. You are correct the “StartDate” and “InvoiceDate” are the alias names of the date fields grouped by months. 

The “Goals” model is the first in the list so it loads first. 

This is a head scratcher!

Thanks

Rob,

Is this a bug? I was able to reproduce what Tami is seeing in my developer org. I have Skuid 11.0.5 installed.

Here is my test page using an aggregate models on Opportunity and Task.

Thanks,

Bill

Bill,

I was able to get around this issue by creating a UI-only field to turn the month numbers into month names with multiple “IF” statements. Then I was able to use MODEL_Lookup by Month name.

Tami,

Thanks for the reply back.  Seems like the issue is a model lookup on Number type fields.  Great to know that UI only field is viable workaround.

Best,

Bill

Thanks Bill.  I’ll look at your repro and pass it along to the dev team. 

Tami.  Did you use the new CASE Function?  That is a lot more elegant for evaluating multiple scenarios.  No nested IF’s needed…

Bill.  There is actually a bug in your page. 
- Your model id is  “TasksByDueDateMonth”   
- In the formula you call for model:  “TaskByDueDateMonth”.   

See it?

One stupid “s”   
Once this gets changed your repro page actually works. 

This of course doesn’t explain Tami’s original problem…

Rob,

Nice catch!  Can I turn this into a feature request?  🙂 (i.e. a ‘model lookup’ user interface so we can only pick from models and fields that actually exist).

I can confirm that setting the model name correctly fixes the lookup field.

Still a mystery why Tami’s page did not work.

Thanks,

Bill

Yes - we have “improve the formula builder experience” in our backlog… 

Hello all,

I can only get the model lookup to work if I format the last parameter:

MODEL_LOOKUP(“milestones”,“sumAmcMilestoneBudgetc”,“endQc”,FORMAT_DATE(“yy-mm-dd”,{{endQc}}))

This is despite the two endQc fields being identical in each of the objects/ models

bit of an odd one.

Ben

That is interesting.  When you inspect the model data of both models the values match exactly?  Or is one an aggregate and the other a basic model?  I’m guessing that the date in “milestones” just has yy-mm-dd segments while the other has a full SFDC date output.  But when you format it - it matches what’s coming from the milestones model…

Rob,

they are both aggregate models, the fields are both formula fields(date) and use exactly the same formula (see below). Only difference is a source field used within each formula, but given the formulas both create a date I don’t see how that would impact.

Ben