How can I traverse through multiple related objects based on ID and return some related field?

Question

I'm a little stuck.

I am trying to generate a report that determines whether anyone has made a manual change to certain fields within our order framework. I have figured out the proper fields and structures to audit, and even how to make the report, but I used a combination of extracts from the Dataloader and Excel xlookups to make it. Now, I'm being asked to find a way to automate the generation of the report, and I suspect that means I need to write a SOQL query to figure it out. I'm having trouble traversing multiple relationships based on these ID fields. Essentially, what I'm trying to do is make multiple "left joins" based on the 18 digit Salesforce IDs and extract some related piece of information from those other objects.

For example, if I'm starting with order_product_history (with a field OrderProductID to identify the order product) and I want to bring in "Product Name", I have to first match OrderProductID with the ID field in my order_product "table", then I have to match the Product2ID field in my order_product "table" with the ID in my product "table", then I have to get the matching Product Name as a column in my report:

Matching/Traversal Process

Desired Result

That's one example for one field. I also have to bring in things like User Name from the users "table", and order number from the orders table, but once I get the general idea, I think I'll be OK. I also want to filter the results to only include my Fee__c and UnitPrice fields, ignore the automated users and set a date filter--not sure if I have to do that using a WHERE clause just in my main query, or if I have to filter the subqueries as well.

I am not a programmer and I have no formal Salesforce training; I am just an analyst who is technically inclined and sort of fell into the role of Salesforce Admin. I am familiar with programming concepts and have been writing things using the flow application and have even dipped my toes into some Apex stuff, but it can be a bit of a struggle. I am not asking you to do my job for me and I am willing to work at the problem and learn; any help is appreciated. Sorry about the links; SO won't let me embed images yet.

Answer 1

Just wanted to update this for anyone looking for a solution in the future.

Turns out you can traverse these as a parent-child relationship in the SOQL query. Here's the query I ended up using:

SELECT CreatedBy.Name, FORMAT(CreatedDate), 
       OrderItem.Order.OrderNumber, OrderItem.Product2.Name, 
       OrderItem.Product2.ProductCode, Field, OldValue, NewValue
FROM OrderItemHistory
WHERE (Field = 'Fee__c' OR UnitPrice) 
      AND (CreatedBy.Name != 'Integration User') 
      AND (Created Date >= 2020-11-24T00:00:00.000Z) 
ORDER BY CreatedDate DESC

Answer 2

There's high chance you don't have to write any code for it. I'll give you some tips, experiment a bit and edit the question or post new one?

This diagram might help: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_erd_products.htm

Developer way

It's all about writing the right query. You can play with it in Developer Console or Workbench for example. Read up about relationship queries in SF.

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

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

I don't have org with orders enabled but this should be a good start:

SELECT CreatedById, Created.Name, 
    Parent.Name, Parent.Product2.Name, Parent.Order.Name, 
    Field, OldValue, NewValue, CreatedDate
FROM OrderItemHistory

If it throws errors about "Parent" see if "OrderItem" will work. Once you have it - WHERE Field IN ('UnitPrice', 'Fee__c') AND CreatedDate = LAST_WEEK might be good next step. (dates can be filtered by date but there are few "constants" that are more human-readable too)

You could even export this with Data Loader, you just have to start the wizard on Order Product history table. But you can ignore the whole query creator and paste a query you've created.

Admin way

Have you ever created a report in Salesforce? There are self-paced trainings (for Lightning and Classic UI) and tons of YouTube videos. Get a feel of making few reports.

What you want might be doable with built-in report type (see if there's new report -> order product history). If nothing exciting - as admin you can make new report type in setup. For example Orders -> Order Products -> Order Product History. Screenshots from here might help.