2

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.

Adam Hollander
  • 41
  • 5

2 Answers2

1

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.

eyescream
  • 18,088
  • 2
  • 34
  • 46
  • 1
    I've made plenty of reports, but so far as I can tell, order_product_history is not a reportable object. It's only exposed via API/query. Hence, the need to do it via dataloader or SOQL query. – Adam Hollander Nov 13 '20 at 21:43
  • 1
    Would you consider creating helper object to hold this data in reportable way, maybe "process builder" that inserts new record every time order line item is created (or edited and the edit changes the key fields you need). It'd eat up into your storage a bit (how many changes typically happen) but there's value in having it automated and on the platform instead of export & massaging in another system... – eyescream Nov 13 '20 at 21:57
  • 1
    That sounds like a good idea. It's kind of ridiculous just how arbitrary Salesforce can be with its restrictions. Feels like the whole thing is being held together by duct tape sometimes. I believe Process Builder can expose every object. Failing that, I'll give Flow a try, or failing that, I'll try to write an Apex trigger (although the code coverage requirement has been a struggle for me).I'll give it a shot on Monday and let you know how it goes. – Adam Hollander Nov 14 '20 at 19:07
  • 1
    I feel ya, have same limitation on my current project but as the client has existing reporting solution outside of SF they're OK just extracting it via API and dumping in "data lake" to be consumed by some BW tool. If it's something your company is interested in (or perhaps you want to look into backup of SF data?) you could kill 2 birds... https://trailblazer.salesforce.com/ideaview?id=08730000000BrlxAAC is 13 years old – eyescream Nov 14 '20 at 22:13
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
Adam Hollander
  • 41
  • 5