Apex Code Development You need to sign in to do that
Don't have an account?
Arvind Mahadevan 5 Writing SOQL query to replace an existing custom report
We currently have a custom report in Salesforce that we would like to schedule using a custom SOQL query to retrieve the same dataset that is retrieved using the custom report.
The custom report retrieves all the CPE Location records with or without Inventory Asset records.
We want to write a custom SOQL that performs the same function of retrieving all CPE Location with or without Inventory Assets. However, the SQOL query below in this email only retrieves ~63,136 records.
Could you assist me on why the SOQL query and the custom report are retrieving different datasets?
I am using the following custom objects for my SOQL query: CPE_Location__c and Inventory_Asset__c. The Inventory_Asset__c object has a custom field CPE_Location__c that is a look up to the CPE_Location__c object. There is no relationship to INventory_Asset__c object from CPE_Location__c object.
Here is the SOQL query that will replace the custom report mentioned above:
SELECT CPE_Location__r.CPE_Location_Record__c,CPE_Location__r.Name,CPE_Location__r.Account__r.Name,CPE_Location__r.CPE_Location_Type__c,CPE_Location__r.Virtual_Type__c,
CPE_Location__r.Legacy_Company__c,CPE_Location__r.Splice_Complete_Date__c,CPE_Location__r.On_Net_Date__c,CPE_Location__r.Location_Name__r.Name,CPE_Location__r.Location_Address__c,
CPE_Location__r.Location_City__c,CPE_Location__r.Location_State__c,CPE_Location__r.Location_Zip__c,CPE_Location__r.Latitude__c,CPE_Location__r.Longitude__c,CPE_Location__r.CPE_Location_Coordinates__c,
CPE_Location__r.CPE_Location_URL__c,
Inventory_Asset__c.Asset_Number__c,Inventory_Asset__c.Name,Inventory_Asset__c.Condition__c,
Inventory_Date__c,Scan_Date__c,Inventory_Product_Details__c,Placed_in_Service_Date__c,PO_Number__c,Project__c,RFID_Tag__c,RMA_Number__c,
Serial_Number__c,Vendor__c,Warranty__c,Treatment__c
from Inventory_Asset__c where
CPE_Location__r.Division__c != 'Uniti Leasing' AND
CPE_Location__r.CPE_Location_Type__c != 'Virtual' AND
ORDER BY CPE_Location__r.CPE_Location_Status__c ASC
Thank you in advance.
Arvind Mahadevan.
The custom report retrieves all the CPE Location records with or without Inventory Asset records.
We want to write a custom SOQL that performs the same function of retrieving all CPE Location with or without Inventory Assets. However, the SQOL query below in this email only retrieves ~63,136 records.
Could you assist me on why the SOQL query and the custom report are retrieving different datasets?
I am using the following custom objects for my SOQL query: CPE_Location__c and Inventory_Asset__c. The Inventory_Asset__c object has a custom field CPE_Location__c that is a look up to the CPE_Location__c object. There is no relationship to INventory_Asset__c object from CPE_Location__c object.
Here is the SOQL query that will replace the custom report mentioned above:
SELECT CPE_Location__r.CPE_Location_Record__c,CPE_Location__r.Name,CPE_Location__r.Account__r.Name,CPE_Location__r.CPE_Location_Type__c,CPE_Location__r.Virtual_Type__c,
CPE_Location__r.Legacy_Company__c,CPE_Location__r.Splice_Complete_Date__c,CPE_Location__r.On_Net_Date__c,CPE_Location__r.Location_Name__r.Name,CPE_Location__r.Location_Address__c,
CPE_Location__r.Location_City__c,CPE_Location__r.Location_State__c,CPE_Location__r.Location_Zip__c,CPE_Location__r.Latitude__c,CPE_Location__r.Longitude__c,CPE_Location__r.CPE_Location_Coordinates__c,
CPE_Location__r.CPE_Location_URL__c,
Inventory_Asset__c.Asset_Number__c,Inventory_Asset__c.Name,Inventory_Asset__c.Condition__c,
Inventory_Date__c,Scan_Date__c,Inventory_Product_Details__c,Placed_in_Service_Date__c,PO_Number__c,Project__c,RFID_Tag__c,RMA_Number__c,
Serial_Number__c,Vendor__c,Warranty__c,Treatment__c
from Inventory_Asset__c where
CPE_Location__r.Division__c != 'Uniti Leasing' AND
CPE_Location__r.CPE_Location_Type__c != 'Virtual' AND
ORDER BY CPE_Location__r.CPE_Location_Status__c ASC
Thank you in advance.
Arvind Mahadevan.
If i have understood it correct then you are creating a report on CPE Location object with Asset Records.
But if I go through your query it is on Inventory Asset.
The query will return Invntory Asset records with CPE Location tagged onto it or not.
Cheers!!!
Thank you for your quick response and inputs. I already have a custom Salesforce report with report type (CPE Locations with or without Inventory Assets) which retrieves 188,500 records. This retrieves multiple Inventory Assets tied to the same CPE Location record. For Example, one location record can have 1 or more Inventory Asset records.
However, I am trying to build a custom SOQL query with the same column and filter condition as the above custom Salesforce report. However, I see that the custom SOQL query only retrieves ~63K records. I am guessing that the SOQL query is only retrieving distinct Inventory Asset record based on a CPE Location record.
Hope this provides additional information on what I am trying to accomplish through the custom SOQL query.
Regards,
Arvind Mahadevan.