Model Lookup date in Aggregate Model

  • 1
  • Question
  • Updated 5 months ago
  • In Progress
I have two aggregate models with the date fields both rendering grouped by calendar month. So for January both model displays "1" for the date. I am trying to do a MODEL_LOOKUP based on the date fields but just using the alias names of the date fields are not working. 

Formula:  MODEL_LOOKUP("Goals","Profit","StartDate",{{InvoiceDate}})
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb

Posted 9 months ago

  • 1
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
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.  
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
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
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
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

<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" useviewportmeta="true" showheader="true">
    <models>
        <model id="TasksByDueDateMonth" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="aggregate" sobject="Task">
            <fields>
                <field id="Id" name="countId" function="COUNT"/>
            </fields>
            <conditions/>
            <actions/>
            <groupby method="simple">
                <field id="CreatedDate" name="cmCreatedDate" function="CALENDAR_MONTH"/>
            </groupby>
        </model>
        <model id="OppByCloseDateMonth" limit="" query="true" createrowifnonefound="false" datasource="salesforce" type="aggregate" sobject="Opportunity">
            <fields>
                <field id="Id" name="countId" function="COUNT"/>
                <field id="LookupTaskCountByCalendarMonth" uionly="true" displaytype="FORMULA" label="LookupTaskCountByCalendarMonth" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="4" scale="0">
                    <formula>MODEL_LOOKUP("TaskByDueDateMonth","countId","cmCreatedDate",{{{cmCloseDate}}})</formula>
                </field>
            </fields>
            <conditions/>
            <actions/>
            <groupby method="simple">
                <field id="CloseDate" name="cmCloseDate" function="CALENDAR_MONTH"/>
            </groupby>
        </model>
    </models>
    <components>
        <grid uniqueid="sk-1o0pdw-351">
            <divisions>
                <division behavior="flex" minwidth="100px" ratio="1">
                    <components>
                        <skootable showconditions="true" showsavecancel="false" showerrorsinline="true" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" alwaysresetpagination="false" createrecords="false" model="OppByCloseDateMonth" buttonposition="" mode="readonly" allowcolumnreordering="true" responsive="true" uniqueid="sk-1o0ojI-338">
                            <fields>
                                <field id="CloseDate" name="cmCloseDate" hideable="true" uniqueid="fi-1o0sFT-377" valuehalign="" type="">
                                    <label>Close Date Month</label>
                                </field>
                                <field id="Id" name="countId" hideable="true" uniqueid="fi-1o0sFT-378" valuehalign="" type=""/>
                                <field id="LookupTaskCountByCalendarMonth" hideable="true" uniqueid="fi-1o2V5M-1391" decimalplaces="" valuehalign="" type=""/>
                            </fields>
                            <rowactions/>
                            <massactions usefirstitemasdefault="true"/>
                            <views>
                                <view type="standard"/>
                            </views>
                        </skootable>
                    </components>
                </division>
                <division behavior="flex" verticalalign="top" minwidth="100px" ratio="1">
                    <components>
                        <skootable showconditions="true" showsavecancel="false" showerrorsinline="true" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" alwaysresetpagination="false" createrecords="false" model="TasksByDueDateMonth" buttonposition="" mode="readonly" allowcolumnreordering="true" responsive="true" uniqueid="sk-1o14W3-592">
                            <fields>
                                <field id="CreatedDate" name="cmCreatedDate" hideable="true" uniqueid="fi-1o1IPb-705" valuehalign="" type=""/>
                                <field id="Id" name="countId" hideable="true" uniqueid="fi-1o16P0-606"/>
                            </fields>
                            <rowactions/>
                            <massactions usefirstitemasdefault="true"/>
                            <views>
                                <view type="standard"/>
                            </views>
                        </skootable>
                    </components>
                </division>
            </divisions>
            <styles>
                <styleitem type="background" bgtype="none"/>
            </styles>
        </grid>
    </components>
    <resources>
        <labels/>
        <javascript/>
        <css/>
        <actionsequences uniqueid="sk-1o0h34-299"/>
    </resources>
    <styles>
        <styleitem type="background" bgtype="none"/>
    </styles>
</skuidpage>
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
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
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
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....
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
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....
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
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
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Yes - we have "improve the formula builder experience" in our backlog... 
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
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
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
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...
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
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