+ Start a Discussion
JoyJobingJoyJobing 

SOQL query SOMETIMES works and sometimes not - is AccountID not populated all the time?

I wrote a whole Task trigger around updating a field Last Activity by Account Owner.  Tested fine, and deployed, and I'm noticing it's not always working.  I've tracked the issue to the SOQL query (I think):

SELECT id, (SELECT id, ActivityDate, AccountId FROM Tasks WHERE Owner_Matches_Account_Owner__c = true AND IsClosed = true ORDER BY ActivityDate DESC LIMIT 1) FROM Account	WHERE id IN :dates.keySet()

 I know the list of ID's coming into the query is correct, but it looks like most times there are 0 results.

 

Based on the Task documentation (http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_objects_task.htm), it seems like this is set at some point (I made my trigger an AFTER to be sure):

AccountID: ID of the related Account.This is the account of WhatId if it exists, otherwise it’s the account of WhoId if it exists, otherwise it’s set to null. 

 

So far the Contacts are related to Accounts so I don't believe that's the issue.

 

Any thoughts?  I'm so stuck on this I'm going to have to comment the whole thing out and just do the update manually every day.  Argh.  PS - I originally got help writing this query from these boards here: http://boards.developerforce.com/t5/Apex-Code-Development/Select-newest-Task-from-Map-of-AcctId-s/m-p/643891#M119210

 

Edit: So far, what I've found is that the salesforce BCC logging email tasks work, and tasks that users add themselves do not work.  Neither is attached directly to the Account thru the Related To - only the Contact is filled in.

Best Answer chosen by Admin (Salesforce Developers) 
Michael_TorchedloMichael_Torchedlo

While I don't know why the AccountId is behaving this way, I don't think it's odd in the sense that I think it's always been that way.  It's nothing we can control, you could try to log a case with Salesforce support and see if they can explain it better.  

 

If it was me in your shoes, I would probably just change my queries to get around the whole mess.  I'd split it into two separate queries, one for the Accounts, and another for the Tasks.  By querying the Task object directly, I think you avoid worrying about which object was the Task's parent.

 

   ... FROM Task WHERE AccountId IN :dates.keySet() OR Who.Accountid IN :dates.keySet() ORDER BY ActivityDate DESC

 

and then I'd loop through the Tasks checking the AccountID and Who.AccountID so that I only updated a single date for each account.

 

 

All Answers

MellowRenMellowRen

JoyJobing

 

I suppose there may be a bug in AccountID and if so, ouch.

 

But my first question would be about how are your users creating the tasks and, as a result, what is the Status they are assigning to them? Basically I am wondering if the IsClosed field is actually set to true for these tasks? Remember IsClosed on Events is date related but on Tasks it is Status related.

 

My second question would be along similar lines. Do these tasks have Owner_Matches_Account_Owner__c set to true? How does it get set in the first place, and is that working properly? Are the users only creating activities for people who own the Accounts (if I am understanding that field's purpose correctly)?

 

Regards

MellowRen

 

 

Michael_TorchedloMichael_Torchedlo

Are you saying that it sometimes works and sometimes not on the exact same record?  That would be a bizarre issue.

 

If the performance is inconsistent, it could be because of differences in your data.  For one thing, Tasks only appear in

query results if they are < 1 year old.  If the activity date is more than one year old, Tasks and Events are "archived" and can only be returned by using the ALL ROWS clause. 

 

SELECT id,accountid,isclosed FROM Task WHERE isclosed = true ALL ROWS;

 

Is it possible that some of the account records in your org only have a very old Task that qualifies (which would return no values in your original query), but different accounts have more recent Tasks (which would execute your code as expected)?

 

Generally speaking, it is very possible for a Task to have no accountid.  Tasks can be created with a blank whoid and a blank what id, but I don't think that is causing your problem.

JoyJobingJoyJobing

Thank you both for your comments.  I'll try to run though the questions below and for a few, I think I'll do more debugging that will lead me closer.

 

To start - the field "Owner matches Account Owner" is a simple formula (albeit sneaky, but it definitely works!):

IF( OwnerId = Account.OwnerId, TRUE, FALSE)

 I initially ran a report to dump in starting dates to all the Accounts.  So pulled all closed Tasks with a true matching owner, did a pivot table in excel to get the max on the Task dates and loaded back to Salesforce.  (PS - we have the archive activities turned OFF for our org - but generally speaking they've logged a task in the last few months)   At the moment because the field isn't working, I'm having to do this daily to update the new Tasks created each day.  So I know that they are Closed, Owner matching, and fairly new (last few days) Tasks that are being added to the specific set of Accounts I'm loading daily.

 

From this report, I can see the last month of tasks logged to the Account - and my Last Activity by Owner field.  I can see from this report that some of the Accounts are updated and some are not.  From what I can tell - the tasks logged directly from the Contact (ie Log a Call button) are NOT getting updated, and those logged automatically from the BCC email address ARE getting updated.  Both have WhoID filled in, and WhatId blank.

 

I'll go do some more testing on my own with a test account, but I just don't understand why the test class works (which replicates adding the task with the WhoId only), and in practice it doesn't work.  If anyone has thoughts please let me know!  Very many thanks!

 

JoyJobingJoyJobing

More troubleshooting:

Put a test Account into my name (as admin).  Both logging a Task manually (Log a Call button) and via the BCC email worked!

Put the Account into my test user's name (as a salesperson like most users).  Logged in as test and logged a task manually and BCC, both worked again!

 

Frustrated. :-(

Michael_TorchedloMichael_Torchedlo

I presume you've ruled out the possibility that the Tasks that didn't fire originated from Leads, which were later converted into contacts.  In such a scenario, as a Lead, there is no accountid to match, so it would never execute your code.

 

But I think I see one possible explanation.  The main filter in your query is =: dates.keySet().  You are creating a map of Account Ids in some other piece of code that we don't see.  I think the reason this is giving you trouble is because your relationship query goes Parent-Child.  It starts with the Account Object, and then goes to look at the child Tasks Directly related to the Account.  It is NOT traveling from the Account to the Contact child and THEN to the Task, which is another layer.  It might be possible to modify your query to do that, or add a second query to handle the "Contact" tasks that are being missed.  If I'm right, you might need to either add a second query to your code that will find all the Tasks where the Who contact is related to the account ids in =: dates.keySet();

JoyJobingJoyJobing

We don't use Leads, so that case is completely covered :-)

All the Tasks originate from the Contact actually - we removed the button to create new from the Account page a long time ago.  So ALL of them have WhoId filled out and it should be a Contact not a Lead - and some of them MAY have a WhatId also, but generally don't.  Unless we have a Task logged from an Opp - in which case the WhatId will be filled out and the WhoId would be blank.

 

I based this whole thing off the AccountID field on the Task, which is supposed to automatically populate on all Tasks based on the WhoId or WhatId (see my original post here for link to the documentation) - regardless of what is filled out.  That's how we get that automagical roll-up of all Activities on the Account, regardless of if they're logged to different Contacts or Opps on the Account.  If I need to traverse down to each Contact and then Tasks, I think it'd be much more cumbersome - and still miss anything else logged to Opps, etc (whatever all the automagical relationships are that roll up to the Account).  Does that make sense?

 

I'll try doing some more debugging with the actual users that are being affected, when I get back from lunch.  It MUST be something in the way they're logging, that I can't see on my end (or replicate even though I'm using the exact same user Profile and Role as they are).  Why would MINE work but half of theirs not work?  So weird!

Michael_TorchedloMichael_Torchedlo

While I don't know why the AccountId is behaving this way, I don't think it's odd in the sense that I think it's always been that way.  It's nothing we can control, you could try to log a case with Salesforce support and see if they can explain it better.  

 

If it was me in your shoes, I would probably just change my queries to get around the whole mess.  I'd split it into two separate queries, one for the Accounts, and another for the Tasks.  By querying the Task object directly, I think you avoid worrying about which object was the Task's parent.

 

   ... FROM Task WHERE AccountId IN :dates.keySet() OR Who.Accountid IN :dates.keySet() ORDER BY ActivityDate DESC

 

and then I'd loop through the Tasks checking the AccountID and Who.AccountID so that I only updated a single date for each account.

 

 

This was selected as the best answer
MellowRenMellowRen

JoyJobing

 

If AccountID is being left blank then this formula would not work as intended (ie it would report "false" even if the owners match):

 

  • IF( OwnerId = Account.OwnerId, TRUE, FALSE)

 

The corollary to this is that if this formula is working correctly, in all cases, then AccountID must be OK. That leaves us with:

 

  1. SOQL has a bug (or “feature”) when it comes to AccountID—not a support call I would enjoy with Salesforce; or
  2. The query itself is logically wrong when applied to your structure and/or data.

 

I hate to flog a dead horse and, if you tell me again that you know this is not it, I will not bring it up a third time but are you really really really truely really sure that the Users are logging calls as you think they should be?

 

You said it yourself:

 

  • When you BCC an e-mail, it works.
  • When you log in as a user and BCC an e-mail, it works.
  • When a user BCCs an e-mail, it works.
  • When you manually create a task (using the "Log A Call" button), it works.
  • When you log in as a user and manually create a task (using the "Log A Call" button), it works.
  • When a user manually creates a task, it doesn't always work.

 

This still screams at me. Are they always using the "Log A Call" button? If your page layouts are anywhere near standard then as the user scrolls down they will find the Open Activities section and a "New Task" button before they get to Activity History and "Log A Call". On the New Task page (almost identical to the Log A Call page) the one big difference is that they have to change the Status before saving if the Task is to be considered Closed—and if they don't your SOQL query will, correctly, not find them.

 

Regards

MellowRen

 

EDITS: Spelling and Grammar

 

JoyJobingJoyJobing

OK folks, well it looks like we have it working.  I had adjusted my test code to account for some cases I failed to catch first time and it was continually broken so I was working that out as well as the query issue.

 

I ended up having to take Michael's method, I'm just worried about the performance hit of grabbing ALL qualifying activities on an Account - sometimes reps talk to a Client every day.  But armed with Workbench querying, I realized that the Tasks are inherently linked through the WhatId to Account - there's not a way to query the relationship using AccountId instead, from what I can tell.  Here's the final code:

List<Task> allResults = new List<Task> ([SELECT id, ActivityDate, AccountId FROM Task 
	WHERE Owner_Matches_Account_Owner__c = true AND IsClosed = true AND 
	(AccountId IN :dates.keySet() OR Who.Id IN :dates.keySet())
	ORDER BY ActivityDate DESC]);

//loop through all the results, compare each to the current new date, if newer or null, update the date on the Dates Map
for(Task t:allResults) {
	if((dates.get(t.AccountId) == null) || (t.ActivityDate > dates.get(t.AccountId))) {
		dates.put(t.AccountId, t.ActivityDate);
	}
}

 

To MellowRen's points - totally valid and I would think the same thing.  BUT I was literally looking at a report of Tasks set today, 

                           Owner matches Account Owner equals True Clear     AND Closed equals True Clear  

So I knew they were logged correctly.  I honestly still dont' understand why sometimes my code works and sometimes it doesn't, but hopefully with this change it will just ALWAYS work.

 

Thank you both of all  your help, I don't know if it's better to know why something broke or to just fix it (I usually tend toward the former) but in this instance I am crossing my fingers that alls well that ends well...