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

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?

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.

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?

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>

Hi Greg, do you know if you’re storing the 15 digit Id or the 18 digit Id in your custom WhoId field?

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’

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.

Ok - thanks Ben! I wish we could make a custom lookup field on the task object >.<

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.

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.

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:

Totally, No need to recreate those models.

Forgot to mention, the field on your condition would be your “fake” WHOID field.

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:

Edit: It is actually displaying all tasks with a blank WHOID for some reason, not all tasks.

Edit 2: I also changed the operator to in and have the same result.

can you change your operator? It’s showing “is” instead of “in”

Also, Old_Contact_ID__c must be selected as a field in your Contact model.

Great Scott, I think you’ve done it! Let me do some testing and confirm, but it seems to be working.

Ben, The only problem I have now is that we only want this rule to work for contacts from the old system. Contacts from the new system will have a blank WHOID and display all tasks without a WHOID. Any ideas for that? Edit: For new users I plan on setting up another rule where it checks the real WhoID against the Contact IDs, and setting the logic condition 1 OR 2. But the problem of condition 1 above influencing new contacts is still a problem until we get data loader access.