How to set condition to look at contacts related to custom object

  • 1
  • Question
  • Updated 5 years ago
We have a custom object called Households with related Contacts inside.

We cannot use the Household as the WhatID for tasks.

How can we create a condition for the task model which pulls all the activities related to the contacts inside the Household?
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
  • confused

Posted 5 years ago

  • 1
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Greg,

You should be able to accomplish this with a "Result of subquery" type condition on your Task model.

1. Create your task model.
2. Add the appropriate fields.
3. Create a new condition
4. Set the field to WhoId
5. Set the content type to result of subquery
6. Set the join object to "Contact"
7. Set the join field to "Id"
8. Add a subcondition to your condition by clicking on the brick with the green plus icon on your condition.
9 For your subcondition, set the field to your Household__c lookup field
10. Set the content type to "Field from another model"
11. Make your source model your Household model.
12. Make your source field "Id"

This should pull in all Tasks that have a WhoId of someone in that household. One implication for this is that now when you create a new Task from that table, Skuid will not have a default WhoId to give the task, so that will have to be set by the user.
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Ben,

We aren't able to use Data Loader in the professional edition, and temporarily won't be able to reference WhoID.

What we have to work with for now is a custom field string called WHOID on the task object with the WHOIDs from the old system.

We won't be able to import this to the real WhoID until Salesforce temporarily enables API access for data loader in our new professional edition.

I tried to use the Subquery to compare our WHOID field to the contact IDs, but since WHOID is a string and not a lookup, I'm getting an error.

Any ideas on that problem?
Photo of John Nelson

John Nelson, Product Manager

  • 3,234 Points 3k badge 2x thumb
You could do this with a subquery condition and a subcondition. The subquery would select all the tasks where the WhoId is set to one of the Contacts returned from the result of the subquery. And assuming you want to do this on a per-Household level (or per-Account in my example), you'd use the subcondition to limit the list of Contacts to only those for a given Household.



Here's a quick example page to show you that it works (again, using Accounts in place of your custom object):

<skuidpage showsidebar="true" showheader="true">

<resources>
<labels/>
<javascript/>
<css/>
</resources>
<models>
<model id="TasksFromAccountContacts" limit="100" query="true" createrowifnonefound="false" sobject="Task">
<fields>
<field id="Subject"/>
<field id="Type"/>
<field id="Status"/>
</fields>
<conditions>
<condition type="join" value="" field="WhoId" operator="in" enclosevalueinquotes="true" joinobject="Contact" joinfield="Id">
<conditions>
<condition type="param" value="id" field="AccountId" operator="=" enclosevalueinquotes="true"/>
</conditions>
</condition>
</conditions>
</model>
<model id="Account" limit="100" query="true" createrowifnonefound="false" sobject="Account">
<fields>
<field id="Name"/>
</fields>
<conditions>
<condition type="param" value="id" field="Id" operator="=" enclosevalueinquotes="true"/>
</conditions>
</model>
</models>
<components>
<skootable showconditions="true" showsavecancel="true" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="true" model="TasksFromAccountContacts" mode="read">
<fields>
<field id="Subject"/>
<field id="Type"/>
<field id="Status"/>
</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>
</skuidpage>
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Greg, do you know if you're storing the 15 digit Id or the 18 digit Id in your custom WhoId field?
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
We're using the 18 character. Also, here's the Skuid error message:

1. An error occurred while attempting to perform the following SOQL query: SELECT Id,Business_Task__c,Update_Last_Contact__c,WHATID__c,WHOID__c,Old_Task_ID__c,Alerts__c,What.Name,WhatId,Activity_Date__c,ReminderDateTime,CreatedDate,CallDisposition,Type,Who.Name,WhoId,Subject,Status,ActivityDate,Description,Owner.Name,OwnerId FROM Task WHERE ((Status in ('Not Completed','Not Started','Deferred','In Progress','Other'))AND(WHOID__c in (SELECT Id FROM Contact))) LIMIT 101 Error:The left operand field in the where expression for outer query should be an id field, cannot use: 'WHOID__c'
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Ok, The 18 character one is the right one. This looks like a limitation of SOQL. You have to have a "Real" Id field to do a subquery. I think I may have a workaround. I'll let you know in a few minutes if I have something working.
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Ok - thanks Ben! I wish we could make a custom lookup field on the task object >.<
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
PS - We are going live with our new Salesforce & Skuid implementation tomorrow, so I am really hoping to knock this out. Was an unexpected problem during migration with not having data loader available.
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Ok, I think I have a workaround. You'll need an additional model. (Which you may have already anyways.)

First you need to create a Contact Model that has all of the contacts for that household in it. This should be pretty straightforward. This model should be after your main Household model, but before your Task model.

If you're using a newer version of skuid, you should be able to do a "Field from another model" condition using the IN operator.

1. Remove your subquery condition from your Tasks model.
2. Add a "Field from another model" condition
3. Make the source model your contacts model
4. Make the source field "Id"
5. Set the operator for this condition to "in"

I believe this will do the trick. You have to be using one of our later versions of Skuid though, I'm not exactly sure which one that is.
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Forgot to mention, the field on your condition would be your "fake" WHOID field.
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Trying this now. Just to cheat a little, it looks like I happened to have created my models in that exact order already. Can I get away with not recreating my models? To make sure, here they are:

Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Totally, No need to recreate those models.
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Well, that made our SOQL error go away, but now the activities list is displaying all activities in the system.

Also, to clarify, the field Old Contact ID on the contact is the ID in our old instance. The values in the custom task field WHOID are also the IDs from our old instance.

Here are my settings:

Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
If i'm understanding this right, you could add another condition that is exactly the same as the first, except that the field is the "real" WhoId. Then use a "Grouping Logic" statement. For your case it looks like the grouping logic would be 1 AND (2 OR 3)
1 would be your status condition, 2 would be your "fake" whoId condition, and 3 would be your "real" whoId condition. To enter grouping logic for your conditions, just click on the conditions bar underneath your model name.
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Yes, but we wouldn't want rule 2 to apply for new contacts, because then it will be adding tasks with blank WHOIDs to the field. (Because they will have a blank Old Account ID)
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
There are probably a few crazy things you could do, but they might be a little complicated. This is the first idea that came to me. You could create 2 new models based on the Contact object. One called "Old contacts" and one called "New contacts" and filter them appropriately. Just make sure they're placed in order before the Task model. Then you could base condition 2 on "Old contacts" and condition 3 on "New Contacts". You could still have your original Contact model for it's original purpose.
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
I'm guessing you can't write any kind of anonymous apex, or batch apex to just fix everything, since this is a PE org, right?
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Right, our best option is temporary data loader access from SF for a day or so to get it straightened out. This averts the crisis though for 99% of tomorrow, so thanks for your help Ben!
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Ben,

Thanks again for the help last night.

We are trying to work with SF to get data loader temporarily.

In the meantime, we would like to explore an option for our user's task lists which displays links to the contact / family records based on the custom WHOID field.

Do you know if you could possibly point us in the right direction with a Render Snippet for the WHOID column?
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Greg,

Since you already have a contacts model on your page, you can use that to match a name to your custom WhoId field in your custom field renderer. Here would be the code to do something like that. Include this in your resources tab as and "inline (snippet)" type. Then reference that snippet in a custom field renderer on your custom WhoId field.


var $ = skuid.$;

var field = arguments[0];
var value = arguments[1];

// Get the Contact model you already have on this skuid page
var contactModel = skuid.model.getModel('Contacts');

// Search the contact model for a record with this id
var contactRow = contactModel.getRowById(value);

var link = $('<a>').prop('href','/' + value);

if (contactRow) {
link.text(contactRow.Name);
field.element.append(link);
}
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
well, i'm at a loss, it seems like it should be working. Can you copy/paste your exact code for the snippet here, or grant Skuid LLC access to your org, so I can take a look?
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Rob, I gave login access from our admin account to Skuidify LLC Support. Do you know when you might have time to take a look? Thanks so much!
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Greg, I logged into your org, and I'm assuming that the FamilyView page is what you were working on, but I didn't see that field or snippet on the page. Should I be looking at a different page?
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Hey Ben - it's the HomePage skuid page.
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
Hi Greg, I took a look at your page. I was thinking this was for the same page as we worked on earlier. On the "HomePage" page, your contact model is not limited to just the contacts for a particular household. Since your Contact model does not have any conditions, it just pulls in all Contacts in your org, (up to the limit on the model which defaults to 100). This snippet will only work if you have the contacts already loaded into your contact model when the snippet runs. You could remove the limit on your contact query, but that could cause other problems, especially if you have a large number of contacts in your org. I'm not sure if this solution will work for your particular situation.
Photo of Kerri Russ

Kerri Russ

  • 120 Points 100 badge 2x thumb
Thank you, Ben and Greg! I'm working with the Wealth Management edition (sounds like Greg is too), and just spent half the day wrestling with a similar issue. I was trying to get all activity onto an Individual page whenever their name was in WhatID or WhoID on the activity. Wish I'd seen your comments sooner.

The key for me was that I had NO idea that it mattered what sequence the models were in on the tab. It made all the difference.