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

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.

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.

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)

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.

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?

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!

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?