Apex Code Development You need to sign in to do that
Don't have an account?
Nested SOQL queries won't work
Can't seem to get this to work. I have a left outer join query but it doesn't like the relationship syntax:
for (Account acct:[SELECT Id,(SELECT Solution__c FROM Org_ID__r)FROM Account WHERE Id in :list_Accts.ID]) {
It says it doesn't understand the relationship from Org_ID__r but I check and thats the name of the child object as shown in the lookup field.
Here is what i'm trying to do. I have a related list under the Account that is lookup, not master-detail. It holds different external IDs an account could possibly have (duplicates). There is a field on the account that functions the same and is the old way we did it when it was a one to one relationship. To speed things up for our service teams to enter accounts they just put the account number on the account and a trigger decides if a new entery in the related list needs to be added or not.
1. user enteres a number in a text field to represent an external Key.
2. If that number doesn't match any of the numbers in the related list then create a new one, else leave it alone.
Is the Left Outer Join the way to go? I did something like this on another trigger but that was a master-detail and also standard objects so I don't know if my syntax is correct.
Here is the old code with the nested SOQL that I am trying to fix:
trigger CPMOrgIDCreate on Account (after insert, after update) {
try {
// List of account IDs
List<Id> list_Accts = new List<Id>();
// Add to the list all opps associated with an agreement in the trigger that has been signed
for (Account AcctID: Trigger.New) {
if ((trigger.isInsert && AcctID.CPM_Org_ID__c != NULL)|| ( trigger.isUpdate && AcctID.CPM_Org_ID__c != NULL && trigger.oldMap.get(AcctID.id).CPM_Org_ID__c != AcctID.CPM_Org_ID__c )){
list_Accts.add(AcctID.ID);
}
}
// Loop through all accounts who were added to the list previously
for (Account acct:[SELECT Id, CPM_Org_ID__c FROM Account WHERE Id in :list_Accts]) {
// Check to see if a project already exists that is related to the opportunity
List<ORG_ID__c> list_OrgIDs = new List<ORG_ID__c>();
for (ORG_ID__c SystemID:[SELECT Id FROM ORG_ID__c WHERE ID__c = :acct.CPM_Org_ID__C AND Account__c = :acct.id]) {
list_OrgIDs.add(SystemID);
}
if (list_OrgIDs.size() == 0) {
// If ID does not exist, create one
ORG_ID__c OID = new ORG_ID__c();
OID.Account__c = acct.Id;
OID.ID__c = acct.CPM_Org_ID__c;
OID.Solution__c = 'CPM';
// Create the new project
insert OID;
}
}
} catch (Exception e) {Trigger.new[0].addError(e.getMessage());}
}
Before adding that code to a trigger, try using the Force.com Explorer, or the Soqlxplorer for Mac or the Anonymous query in the developer console.
I think you are using incorrect relationship.
In my understanding, there is an ORG_ID__c object with an account lookup field? Go to the field definition page, find the "Child Relationship Name" value, then add "__r" to it.
For example if the "Child Relationship Name" is "ORG_IDs", you can write the query like this:
I did have the wrong child name. Turns out there was an 's' in the child name that shouldn't have been there.
The Force.com Explorer helped me find that. Nice tool, i could use more like it.
Still having one problem. In the code below there is a Where clause filtering results if the ID__c !='123'. Thats a static value I put in just to get it to work. I need it to dynamically reference the field on the account, 'CPM_Org_ID__c'. Being new to these join queries I don't know how to reference that. I conceptually understand how it works with nested queries but I need to get away from that which is why I am trying joined queries.
Again the basic business case is that every time a string is entered into the account field 'CPM_Org_ID__c' SF will check all realted Org_ID__c records for a match. If there is a match, if there is no match create a new Org_ID__c record using that number, if there is a match, do nothing.
trigger CPMOrgIDCreate on Account (after insert, after update) { try { // List of account IDs List<Account> list_Accts = new List<Account>(); List<Org_ID__c> newID = new list<Org_ID__c>(); MAP<ID,String> AccID = NEW MAP<ID,String>(); // Add to the list all opps associated with an agreement in the trigger that has been signed for (Account AcctID: Trigger.New) { if ( (trigger.isInsert && AcctID.CPM_Org_ID__c != NULL) || (trigger.isUpdate && AcctID.CPM_Org_ID__c != NULL && trigger.oldMap.get(AcctID.id).CPM_Org_ID__c != AcctID.CPM_Org_ID__c ) ){ list_Accts.add(AcctID); AccID.put(AcctID.ID,AcctID.CPM_Org_ID__c); } } // Loop through all accounts who were added to the list previously for (Account acct:[SELECT Id, CPM_Org_ID__c, (SELECT ID__c,Solution__c FROM Org_IDs__r WHERE Solution__c = 'CPM' AND ID__c = '123') FROM Account WHERE Id in :list_Accts]) { IF(acct.Org_IDs__r.size() == 0){ // If ID does not exist, create one ORG_ID__c OID = new ORG_ID__c(); OID.Account__c = acct.Id; OID.ID__c = acct.CPM_Org_ID__c; OID.Solution__c = 'CPM'; insert OID; //newID.add(OID); } //insert newID; } } catch (Exception e) {Trigger.new[0].addError(e.getMessage());} }