I've been working on a custom report tool. Basicly, it's an export in CSV format out of data from 8 objects. There is some user input needed so I have a visualforce page and a custom controller.
I have 1 parent object (Mutatie_Regel__c) with 4 childs (Vloeroppervlak_T_A__c, Relatie_Transactie_Aanbod__c, Prijsinfo__c, Vastgoed_Link_Object__c), 1 of those children is a link object for a many-to-many relationship to Vastgoedobject__c with 2 children (Adres__c, Relatie__c).
All the data in those objects will be one record in the csv.
I made the code in two ways, each hit a different governor limit. I need a third solution.
The governor limits as you all know:
- Total number of executed code statements: 200 000
- Total number of SOQL queries issued: 100
1) Too many script statements
I load every object in a collection.
public void getAanbodData() {
clearData();
mapMr = new Map<ID, Mutatie_Regel__c>(
[ SELECT Id, VGM_OnlineId__c, Beschikbaar_Vloeroppervlak_Berekend__c, Datum__c, Beschikbaar__c, Soort_Huur_Koop__c,
Aantal_Parkeerplaatsen__c, Type_Mutatieregel__c
FROM Mutatie_Regel__c
WHERE Meest_Recent_Aanbod__c = true
AND LastModifiedDate >=: dummy.Looptijd_contract_van__c
AND LastModifiedDate <=: dummy.Looptijd_contract_tot_en_met__c
LIMIT 1000 ]);
mapVota = New Map<ID, Vloeroppervlak_T_A__c>(
[ SELECT Type__c, Bijzonderheden__c, Aantal_Units__c, Units_vanaf_m2__c, Mutatie_Regel__c
FROM Vloeroppervlak_T_A__c
WHERE Mutatie_Regel__c in : mapMr.keySet()
LIMIT 1000 ]);
mapRelta = New Map<ID, Relatie_Transactie_Aanbod__c>(
[ SELECT Bedrijfsnaam__c, Marktpartij__c, Marktpartij__r.Name, Soort_Relatie__c, Mutatie_Regel__c
FROM Relatie_Transactie_Aanbod__c
WHERE Mutatie_Regel__c in : mapMr.keySet()
LIMIT 5000 ]);
mapPi = new Map<ID, Prijsinfo__c>(
[ SELECT Eenheid__c, Type__c, Verklaring_prijspeil__c, Prijs__c, Mutatie_Regel__c
FROM Prijsinfo__c
WHERE Mutatie_Regel__c in : mapMr.keySet()
LIMIT 5000 ]);
mapVglo = new Map<ID, Vastgoed_Link_Object__c>(
[ SELECT Vastgoedobject__c, Mutatie_Regel__c
FROM Vastgoed_Link_Object__c
WHERE Mutatie_Regel__c in : mapMr.keySet()
LIMIT 1000 ]);
setVgoFromVglo = new Set<ID>();
for ( Vastgoed_Link_Object__c vglo : mapVglo.values() ) {
setVgoFromVglo.add(vglo.Vastgoedobject__c);
}
mapVgo = new Map<ID, Vastgoedobject__c>(
[ SELECT Id, VGM_OnlineId__c, Type_Locatie__c, Gemeentecode__c, Plaats__c, Name, Status_bouw__c, Kwartaal_Start_Bouw__c, Jaar_Start_Bouw__c,
Kwartaal_Oplevering__c, Jaar_Oplevering__c, Info_Start_Bouw__c, info_start_bw_anders__c, Internetadres__c, Single_Multi_tenant__c,
COROP_Gebied__c, Frontbreedte__c, Aantal_Bouwlagen__c, VGM_Regio__c, Gebouwnaam__c, Type_Vastgoedobject__c
FROM Vastgoedobject__c
WHERE Id in : setVgoFromVglo
LIMIT 1000 ]);
mapAdres = new Map<ID, Adres__c>(
[ SELECT Straat__c, Huisnummer_Van__c, Postcode_Numeriek__c, Postcode_Alfa__c, Vastgoedobject__c
FROM Adres__c
WHERE Hoofd_Adres__c = true
AND Vastgoedobject__c in : mapVgo.keySet()
LIMIT 1000 ]);
mapRelvgo = new Map<ID, Relatie__c>(
[ SELECT Marktpartij__c, Marktpartij__r.Name, Vastgoedobject__c
FROM Relatie__c
WHERE Soort_Relatie__c = 'Ontwikkelaar'
AND Vastgoedobject__c in : mapVgo.keySet()
LIMIT 1000 ]);
}
I loop through the collections and match on the id of Mutatie_Regel__c
Because each collection contains all the data. Alot of looping is done and alot of script statements are used that are not needed.
2) Too many SOQL queries
I figured I should limit the collections to only contain the data of that particular parent record. The problem with this solution is that if I put the queries of the child objects during the creation of the output and inside the loop of the parent object, I'll easily hit the too many soql query limit.
I can solve each limit by going to the other solution ...
I uploaded a ton of data from Oracle to Salesforce with Pervasive Data Integrator but the proces halted with this error. I've been unable to find a solution for this. When I continue the mapping from the record it stopped, it can continue, so it's not an invalid data issue. The API call limit of the Salesforce account was not reached. The server didn't go down.
07/05/2010 18:43:31 1 0 O Global *** Execution Begin: [xmldb:ref:///xxx.tf.xml] 07/06/2010 00:18:09 4 8 O Global Update: error Server.userException (Error posting message: ) 07/06/2010 00:18:09 4 25508 O Global Error writing to file or table 07/06/2010 00:18:09 4 25508 O Global Error writing to file or table 07/06/2010 00:18:09 4 8 O Global Update: error Server.userException (Error posting message: ) 07/06/2010 00:18:09 1 0 O Global *** Execution End: [xmldb:ref:///xxx.tf.xml] (version 1.44) completed with errors 07/06/2010 00:18:09 1 0 O Global *** Execution Statistics: [xmldb:ref:///xxx.tf.xml] executed in 5 hours, 34 minutes, 37.441 seconds
I've upserted a list of companies to Account with their original id placed into an external id field called "Account_ExtID".
I've got a second list of Child Accounts that I want to upsert. The mapping is the same for the most part, but now I'm also filling in the Parent ID which is the default Lookup field for account.
This is what I could find in the documentation of Pervasive for the Salesforce API connector
In the reference field of the child entity, enter the unique External Id information of the parent entity in the following format (parentheses are included in the syntax):
"(entityname:externalIdname:externalIdvalue)"
MAPPING: Parent ID = "(Account:Account_ExtID:"&Fields("Company_ID")&")"
ERROR: Upsert: error sf:INVALID_FIELD (INVALID_FIELD: No such column 'Account' on entity 'Account'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.)
Then I thought I shouldn't specify the entityname because he was checking for a column on the entityname location.
MAPPING: Parent ID = "(Account_ExtID:"&Fields("Company_ID")&")"
ERROR: Upsert error code MALFORMED_ID: Parent Account ID: id value of incorrect type: (Account_ExtID__c:300078)
I've done upserts with external id's several times already with Pervasive succesfully, but never on Account and never with both parent and child being the same object type.
What is the correct way to do this in this situation ?
I've upserted a list of companies to Account with their original id placed into an external id field called "Account_ExtID".
I've got a second list of Child Accounts that I want to upsert. The mapping is the same for the most part, but now I'm also filling in the Parent ID which is the default Lookup field for account.
This is what I could find in the documentation of Pervasive for the Salesforce API connector
In the reference field of the child entity, enter the unique External Id information of the parent entity in the following format (parentheses are included in the syntax):
"(entityname:externalIdname:externalIdvalue)"
MAPPING: Parent ID = "(Account:Account_ExtID:"&Fields("Company_ID")&")"
ERROR: Upsert: error sf:INVALID_FIELD (INVALID_FIELD: No such column 'Account' on entity 'Account'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.)
Then I thought I shouldn't specify the entityname because he was checking for a column on the entityname location.
MAPPING: Parent ID = "(Account_ExtID:"&Fields("Company_ID")&")"
ERROR: Upsert error code MALFORMED_ID: Parent Account ID: id value of incorrect type: (Account_ExtID__c:300078)
I've done upserts with external id's several times already with Pervasive succesfully, but never on Account and never with both parent and child being the same object type.
What is the correct way to do this in this situation ?
