I am seeking some advice on how to best structure transaction data on my order object.

High level, our customers place orders with us and we have to then purchase material from various retail stores which we then mark up and deliver to them.

We currently track the retailer that we buy the material from by using a "Retailer" field. We also use a field called "Material Cost" to determine how much the material was.

Ultimately, we are trying to create a report that breaks down spending by the retailer.

This is fairly straightforward when we only have to purchase material from one retailer for an order, however, the complexity and problem that I am trying to solve is when we have to purchase material from multiple retailers for the same order. This would require us to use two "retailer" fields and two "Material Cost" fields.

With this approach, it would then throw off our summary report because we would have to choose which retailer to summarize.

Hopefully I've provided enough detail but happy to provide further context if necessary.

Appreciate any and all advice/feedback.

  • 1
    Sounds like a case for a master-detail relationship to handle n retail purchases with a few roll-up summary fields to calculate totals. – JaredT Jul 13 '18 at 14:36
  • Possibly a junction object, if Retailer is its own object (you may want it to be). – David Reed Jul 13 '18 at 14:37

If you can guarantee that you will only purchase one material from a retailer (per item), you should add the Retailer and Material Cost field to the line item, not the order itself. Otherwise, you'll want a custom object to track that data, and link it to the Order Product (OrderLineItem) object so you can run reports on it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.