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:
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.
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