Complex Query in SF

1. Data Anatomy

2. Schema Explore

3. Query Sampling

"Think big, start small!"

3. Query Sampling (contd.)

...Add more fields, aggregation, remove redundancy

3. Query Sampling (contd.)

...Repeat

3. Query Sampling (contd.)

...Repeat

SELECT SCMC__Item_Master__r.Name, SUM(SCMC__Quantity__c) Qty, SCMC__Condition_Code__r.Name UOM
, FISCAL_QUARTER(CreatedDate) FiscalQuarter, CALENDAR_YEAR(CreatedDate) FiscalYear, SCMC__Inventory_Transactions__c TransactionType, SCMC__Purchase_Order_Line_Item__r.SCMC__Purchase_Order__r.SCMC__Buyer_User__r.Name Distributor
FROM SCMC__Inventory_Transaction_Perpetual_Record__c
WHERE (SCMC__Inventory_Transactions__c = 'Receive Purchased Material'
OR SCMC__Inventory_Transactions__c = 'Miscellaneous Receipt'
OR SCMC__Inventory_Transactions__c = 'Issue Customer Items Pick-list')
AND SCMC__Condition_Code__c <> NULL AND  SCMC__Purchase_Order_Line_Item__r.SCMC__Purchase_Order__r.SCMC__Buyer_User__c <> NULL AND SCMC__Purchase_Order_Line_Item__r.SCMC__Purchase_Order__r.SCMC__Buyer_User__r.Name <> 'CCP Sydney'
GROUP BY SCMC__Purchase_Order_Line_Item__r.SCMC__Purchase_Order__r.SCMC__Buyer_User__r.Name, SCMC__Inventory_Transactions__c, SCMC__Item_Master__r.Name, SCMC__Condition_Code__r.Name, FISCAL_QUARTER(CreatedDate), CALENDAR_YEAR(CreatedDate)

3. Query Sampling (contd.)

...Repeat

4. Custom Fields Creation

...Still not enough?

5. Resources

SOQL Best Practices

https://mohan-chinnappan-n.github.io/sfdc/soql-sosl-bp.html#/home

SOQL and SOSL Reference

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sosl_intro.htm

Working with Date Time

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm?search_text=string

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

Understanding Relationship Query Limitations

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_limits.htm