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.