Model Lookup

  • 1
  • Question
  • Updated 2 years ago
  • In Progress
I'm trying to create a simple Model Lookup formula with page with two models, ContactData and a task model.  It won't return any values.  What am I doing wrong?

MODEL_LOOKUP("ContactData","Household","ID",{{WhoId}})
Photo of Allison

Allison

  • 1,268 Points 1k badge 2x thumb

Posted 2 years ago

  • 1
Photo of Stephen Sells

Stephen Sells, Alum

  • 17,326 Points 10k badge 2x thumb
What is the setup of your underlying Salesforce Organization? Have you checked the Schema Builder?
Photo of Allison

Allison

  • 1,268 Points 1k badge 2x thumb
I think they are linked correctly because I am able to pull the Who.ID in the task object correctly in a separate field, and it matches the correct contact ID. 

The schema builder image is below.
Photo of Michael Pierce

Michael Pierce

  • 584 Points 500 badge 2x thumb
Allison, I'm relatively certain your problem is that you've used "ID" instead of "Id" in your MODEL_LOOKUP formula, which is very sensitive to syntax in my experience. Give that simple change a try and see if it helps.
Photo of Allison

Allison

  • 1,268 Points 1k badge 2x thumb
Thanks Michael.  I was using "ID" but changed to "Id" and same results.  Any other ideas?
Photo of Michael Pierce

Michael Pierce

  • 584 Points 500 badge 2x thumb
Could you post your XML so I can take a look?
Photo of Stephen Sells

Stephen Sells, Alum

  • 17,326 Points 10k badge 2x thumb
The XML would be helpful
Photo of Allison

Allison

  • 1,268 Points 1k badge 2x thumb
<skuidpage theme="BWM" unsavedchangeswarning="yes">
    <models>
        <model type="" id="Scheduling" createrowifnonefound="false" query="true" limit="50" sobject="Task" orderby="Subject DESC, ActivityDate ASC" datasource="salesforce" doclone="">
            <fields>
                <field id="WhoId"/>
                <field id="Who.Name"/>
                <field id="Subject"/>
                <field id="ActivityDate"/>
                <field id="Priority"/>
                <field id="ScheduledByInitials__c"/>
                <field id="Account.MasterRecord.hrmny__Rep__r.Id"/>
                <field id="Who.Id" overridemetadata="false" ogdisplaytype="ID" displaytype="ID" readonly="true" returntype="TEXT" defaultvaluetype="fieldvalue"/>
                <field id="Type"/>
                <field id="TaskSubtype"/>
                <field id="ContactEm" uionly="true" displaytype="FORMULA" ogdisplaytype="TEXT" datasource="salesforce" targetobjects="Contact" rel="" keyfield="Who.Id" readonly="true" returntype="TEXT" defaultvaluetype="fieldvalue" defaultValue="" mergefield="Id" enclosevalueinquotes="true">
                    <batchfields>
                        <batchfield/>
                    </batchfields>
                    <formula>MODEL_LOOKUP("ContactData","Email","Id",{{WhoId}})</formula>
                </field>
            </fields>
            <conditions>
                <condition type="userinfo" value="" enclosevalueinquotes="true" field="OwnerId" operator="=" mergefield="Id" userinfotype="userid" novaluebehavior="deactivate" state="off" inactive="true"/>
                <condition type="fieldvalue" value="false" enclosevalueinquotes="false" field="IsClosed"/>
                <condition type="fieldvalue" value="Specialty" enclosevalueinquotes="true" field="Priority"/>
                <condition type="multiple" value="" field="ActivityDate" state="filterableoff" inactive="true" name="LowTaskTodayFilter" operator="in" enclosevalueinquotes="false">
                    <values>
                        <value>TODAY</value>
                        <value>LAST_N_DAYS:10000</value>
                    </values>
                </condition>
                <condition type="multiple" value="" enclosevalueinquotes="false" field="ActivityDate" operator="in" userinfotype="userid" novaluebehavior="" state="off" inactive="true">
                    <values>
                        <value>TODAY</value>
                    </values>
                </condition>
                <condition type="fieldvalue" value="true" enclosevalueinquotes="false" field="IsRecurrence" operator="!=" state="" inactive="false"/>
                <condition type="fieldvalue" value="00537000000p6Y2" enclosevalueinquotes="true" field="OwnerId"/>
                <condition type="fieldvalue" field="ActivityDate" operator="gte" inactive="true" enclosevalueinquotes="false" name="__autofilter__start__ActivityDate" state="filterableoff" value=""/>
                <condition type="fieldvalue" field="ActivityDate" operator="lte" inactive="true" enclosevalueinquotes="false" name="__autofilter__end__ActivityDate" state="filterableoff" value=""/>
            </conditions>
            <actions/>
        </model>
        <model id="ContactData" limit="20" query="true" createrowifnonefound="false" datasource="salesforce" type="" sobject="Contact">
            <fields>
                <field id="Email"/>
                <field id="Email2__c"/>
                <field id="Spouse_Email__c"/>
                <field id="Household__c"/>
                <field id="HomePhone"/>
                <field id="MobilePhone"/>
                <field id="OtherPhone"/>
                <field id="Phone"/>
                <field id="Alt_Phone__c"/>
                <field id="Spouse_Work_Phone__c"/>
                <field id="PreferredContact__c"/>
                <field id="Id"/>
            </fields>
            <conditions/>
            <actions/>
        </model>
    </models>
    <pageregioncontents>
        <pageregioncontent uniqueid="sk-34Y0Rp-60" regionid="sk-2ZEFpZ-282">
            <components>
                <panelset type="standard" uniqueid="sk-34my30-168" scroll="">
                    <panels>
                        <panel>
                            <components>
                                <skootable showconditions="true" showsavecancel="true" showerrorsinline="true" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="50" createrecords="true" model="Scheduling" buttonposition="" mode="read" allowcolumnreordering="true" uniqueid="sk-2wNPc2-440" emptysearchbehavior="query">
                                    <fields>
                                        <field id="WhoId" hideable="true" uniqueid="fi-2wRH_C-481" valuehalign="" type="">
                                            <label>Contact</label>
                                        </field>
                                        <field id="Subject" hideable="true" uniqueid="fi-2wRLkq-486" valuehalign="" type="" allowordering="true"/>
                                        <field id="ActivityDate" hideable="true" uniqueid="fi-2wRbQR-501" valuehalign="" type="" allowordering="true">
                                            <label>Due Date</label>
                                        </field>
                                        <field id="Priority" hideable="true" uniqueid="fi-2wRbQS-502"/>
                                        <field id="ScheduledByInitials__c" hideable="true" uniqueid="fi-2wRbQS-503" valuehalign="" type="">
                                            <label>Set By</label>
                                        </field>
                                        <field id="Who.Id" hideable="true" uniqueid="fi-2wSUk1-549" valuehalign="" type=""/>
                                        <field type="COMBO" hideable="true" uniqueid="fi-2wScRM-571" valuehalign="">

                                        </field>
                                        <field id="ContactEm" hideable="true" uniqueid="fi-3UTP8d-639" valuehalign="" type="">
                                            <label>Email</label>
                                            <renderconditions logictype="and" onhidedatabehavior="keep"/>
                                            <enableconditions/>
                                        </field>
                                        <field type="COMBO" hideable="true" uniqueid="fi-2wVXJu-828" valuehalign="">
                                            <label>Emails-1,2,3. Phone 1,23, Last WMR</label>
                                            <template>
                                                <contents/>
                                            </template>
                                        </field>
                                    </fields>
                                    <rowactions>
                                        <action type="edit"/>
                                        <action type="delete"/>
                                    </rowactions>
                                    <massactions usefirstitemasdefault="true">
                                        <action type="massupdate"/>
                                        <action type="massdelete"/>
                                    </massactions>
                                    <views>
                                        <view type="standard"/>
                                    </views>
                                    <searchfields/>
                                    <filters>
                                        <filter type="daterange" filteroffoptionlabel="New Filter" createfilteroffoption="true" affectcookies="true" autocompthreshold="25" conditionsource="auto" labelmode="no" conditionfield="ActivityDate"/>
                                        <filter type="toggle" filteroffoptionlabel="New Filter" createfilteroffoption="true" affectcookies="true" autocompthreshold="25" conditionsource="manual" labelmode="auto" label="TODAY (w/ past)">
                                            <effects>
                                                <effect action="activate" value="" condition="LowTaskTodayFilter"/>
                                            </effects>
                                        </filter>
                                    </filters>
                                </skootable>
                            </components>
                        </panel>
                    </panels>
                    <renderconditions logictype="and"/>
                </panelset>
            </components>
        </pageregioncontent>
    </pageregioncontents>
    <components/>
    <resources>
        <labels/>
        <css/>
        <javascript/>
    </resources>
</skuidpage>
Photo of Michael Pierce

Michael Pierce

  • 584 Points 500 badge 2x thumb
I think your problem is that your ContactData model is AFTER your Scheduling model, so the data hasn't loaded in time for the MODEL_LOOKUP formula to grab it. I switched the order and I think it worked for me.
Photo of Allison

Allison

  • 1,268 Points 1k badge 2x thumb
Hmmm, I tried switching and still not getting anything.
Photo of Michael Pierce

Michael Pierce

  • 584 Points 500 badge 2x thumb
I see you have an Email2 field. Are you completely certain you have data in the Email field you're referencing in the MODEL_LOOKUP field?
Photo of Allison

Allison

  • 1,268 Points 1k badge 2x thumb
Yes, and I've adjust the field pulled to another and still nothing coming through.
Photo of Stephen Sells

Stephen Sells, Alum

  • 17,326 Points 10k badge 2x thumb
What are you hoping will show in the lookup field?
Photo of Allison

Allison

  • 1,268 Points 1k badge 2x thumb
The email address from the matching contact object.
Photo of Bill McCullough

Bill McCullough, Champion

  • 13,492 Points 10k badge 2x thumb
Allison,

Can you share the XML for the ContactData and Household models?

Thanks,

Bill
Photo of Allison

Allison

  • 1,258 Points 1k badge 2x thumb
Yes, they are above.  Do you need additional details?
Photo of Bill McCullough

Bill McCullough, Champion

  • 13,492 Points 10k badge 2x thumb
Allison,

My mistake on the XML request.  I jumped to the last comment and missed it.  Take a look at your XML for the formula field.  The XML definition does not look right.

Here is a simple working page that you can try in your org:

<skuidpage unsavedchangeswarning="yes" personalizationmode="server" useviewportmeta="true" showsidebar="true" showheader="true" tabtooverride="Task">
    <models>
        <model id="Task" limit="100" query="true" createrowifnonefound="false" datasource="salesforce" sobject="Task" type="">
            <fields>
                <field id="Subject"/>
                <field id="CreatedDate"/>
                <field id="WhoId"/>
                <field id="Who.Name"/>
                <field id="WhoEmail" uionly="true" displaytype="FORMULA" label="WhoEmail" ogdisplaytype="TEXT" readonly="true" returntype="TEXT">
                    <formula>MODEL_LOOKUP("ContactData","Email","Id",{{WhoId}})</formula>
                </field>
            </fields>
            <conditions/>
            <actions/>
        </model>
        <model id="ContactData" limit="200" query="true" createrowifnonefound="false" datasource="salesforce" type="" sobject="Contact">
            <fields>
                <field id="Name"/>
                <field id="Email"/>
            </fields>
            <conditions>
                <condition type="modelmerge" value="" field="Id" operator="=" state="" model="Task" enclosevalueinquotes="true" mergefield="WhoId" novaluebehavior="noquery" fieldtargetobjects="Contact,Lead"/>
            </conditions>
            <actions/>
        </model>
    </models>
    <components>
        <pagetitle model="Task" uniqueid="sk-3cu4K8-78">
            <maintitle>
                <template>{{Model.labelPlural}}</template>
            </maintitle>
            <subtitle>
                <template>Home</template>
            </subtitle>
            <actions>
                <action type="savecancel" uniqueid="sk-3cu4K6-77"/>
            </actions>
        </pagetitle>
        <skootable showconditions="true" showsavecancel="false" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="true" model="Task" mode="read" allowcolumnreordering="true" uniqueid="sk-3cu4KR-88">
            <fields>
                <field id="Subject" hideable="true" allowordering="true" uniqueid="fi-3cu3Ty-238"/>
                <field id="CreatedDate" hideable="true" allowordering="true" uniqueid="fi-3cu3Ty-239"/>
                <field id="WhoId" hideable="true" uniqueid="fi-3cu9Fg-121" valuehalign="" type=""/>
                <field id="WhoEmail" hideable="true" uniqueid="fi-3cuqFr-307" valuehalign="" type=""/>
            </fields>
            <rowactions>
                <action type="edit"/>
                <action type="delete"/>
            </rowactions>
            <massactions usefirstitemasdefault="true">
                <action type="massupdate"/>
                <action type="massdelete"/>
            </massactions>
            <views>
                <view type="standard"/>
            </views>
        </skootable>
    </components>
    <resources>
        <labels/>
        <css/>
        <javascript/>
    </resources>
    <styles>
        <styleitem type="background" bgtype="none"/>
    </styles>
</skuidpage>

Thanks,

Bill
Photo of Allison

Allison

  • 1,258 Points 1k badge 2x thumb

Thanks Bill.  I updated the code and the page works but still doesn't produce any data :(

Photo of Bill McCullough

Bill McCullough, Champion

  • 13,492 Points 10k badge 2x thumb
Allison,

I don't think your ContactData model is getting any contact records that would match a WhoId in you task model.  Install my page and take a look at the condition on the ContactData model.  I am getting contact records where the Id of the Contact is 'IN' the set of WhoId returned by the task model.  I am guaranteeing some matches.

You can verify this manually if you view the models in your console or add the Who.Id field to your table and add another table to display the contact Id's.

Thanks,

Bill
Photo of Allison

Allison

  • 1,258 Points 1k badge 2x thumb
Thanks Bill.  I appreciate all the help but something still isn't matching up correctly.  I'm having the table also just display the WhoIDs.  I can then go and manually search for them on a different contact table and find the data manually.  The lookup feature just isn't working (or I'm not doing something right! :/ ...
Photo of Allison

Allison

  • 1,258 Points 1k badge 2x thumb
The really frustrating thing is that I can get this to work in the basic Salesforce reporting menu too.  Not very often that SF native is easier to figure out than Skuid!
Photo of Bill McCullough

Bill McCullough, Champion

  • 13,492 Points 10k badge 2x thumb
Allison,

I recommend that you install my sample page and see if it returns email addresses.

I think what is happening is that your ContactData model is not returning enough records to find a match in your Task model.  You could increase the record limit on your ContactData model to 1000 and see if you get some matches.  The better option is to add a condition to your ContactData model to return contacts who are in the list of Tasks that you are viewing (that is where the ContactData contact Id matches the WhoId from your Task model).

Thanks,

Bill