Forum Discussion
Collapsing/manipulating PC hierarchy
Thank you very much for your help!
There was an error in my example, sorry about that. If we look at the same offer/order/product structure in all the example tables, the component e should be present in all the tables. It was missing on the offer and order tables (should be in the first level), sorry! Of course in reality the data on order and offer product structure tables contain every product structure and component that we have, and the relationship with offer table and product table enable us to filter just the right product structure rows for any particular product.
I have tried to solve the problem the following way. I made a duplicate of the table 2, deleted the rows that link the product a (and other products) to its subcomponents and used this table to create a flattened order stage product structure table. This way the subcomponent a and subcomponent b became a level 1 titles. Also I had to delete some duplicated rows and make some minor adjustments. After this the relationship from order table to product structure table seems to work (via intermediary table), at least in some rather straightforward cases. I still have to verify this also with some more complex product structures.
But thank you very much for your help, and if you have any additional comments or smarter ways to achieve the desired result I'm eager to hear them. I will get back to you when I have verified if my current solution actually works!
Problem here is that "Component e" is missing completely at "production stage". So it's not just about a problem of a missing parent (which might be replacable by the reference to the order no), but one component is missing completely.
I recommend to check again and correct that error in your ERP-system.
If that isn't possible, one could create a translation table that stores the data from the offer-table and use it as a lookup once you are in production phase. But that would probably require an 1:1-relationship between those.
Thank you very much for your help!
There was an error in my example, sorry about that. If we look at the same offer/order/product structure in all the example tables, the component e should be present in all the tables. It was missing on the offer and order tables (should be in the first level), sorry! Of course in reality the data on order and offer product structure tables contain every product structure and component that we have, and the relationship with offer table and product table enable us to filter just the right product structure rows for any particular product.
I have tried to solve the problem the following way. I made a duplicate of the table 2, deleted the rows that link the product a (and other products) to its subcomponents and used this table to create a flattened order stage product structure table. This way the subcomponent a and subcomponent b became a level 1 titles. Also I had to delete some duplicated rows and make some minor adjustments. After this the relationship from order table to product structure table seems to work (via intermediary table), at least in some rather straightforward cases. I still have to verify this also with some more complex product structures.
But thank you very much for your help, and if you have any additional comments or smarter ways to achieve the desired result I'm eager to hear them. I will get back to you when I have verified if my current solution actually works!