Hello,
So I have a situation where I have a stocks table where I have basic PC components and assembled computers. I have a second table of conversions where the components are assembled into computers or the computers are disassembled into components. I am trying to use this second table in order to show only basic components into the stocks. Let me give you an example of the two tables:
Stocks Table:
| Product ID |
Product Name |
Quantity |
| P100 |
Graphics Card AB09 |
9 |
| P101 |
HardDisk AR12 |
3 |
| P103 |
HardDisk AR002 |
11 |
| P109 |
Notebook BC01 |
3 |
| P110 |
Notebook BR02 |
4 |
| P112 |
Graphics Card CR22 |
14 |
Conversions table:
| Initial Product ID |
Initial Product Name |
Initial Quantity |
Final Product ID |
Final Product Name |
Final Quantity |
Transaction Code |
| P100 |
Graphics Card AB09 |
2 |
P109 |
Notebook BC01 |
2 |
TRA001 |
| P101 |
HardDisk AR12 |
4 |
P109 |
Notebook BC01 |
2 |
TRA001 |
| P110 |
Notebook BR02 |
2 |
P112 |
Graphics Card CR22 |
6 |
TRA002 |
| P110 |
Notebook BR02 |
2 |
P103 |
HardDisk AR002 |
4 |
TRA002 |
So basically from the conversion table we learn the following:
2 x Notebook BC01 include 2 x Graphics Card AB09 and 4 x HardDisk AR12 therefore 1 x Notebook BC01 includes 1 x Graphics Card AB09 and 2 x HardDisk AR12 (based on transaction code TRA001)
2 x Notebook BR02 include 6 x Graphics Card CR22 and 4 x HardDisk AR002 thereffore 1 x Notebook BR02 includes 3 x Graphics Card CR22 and 2 x HardDisk AR002 (based on transaction code TRA002)
This is the tricky part, as transactions go both ways as you can see. You can have either assembly of stocks or disassembly of stocks. If we have 1 Initial Product ID and more than 1 Final Product ID in a transaction then it is a disassembly and if we have more than 1 Initial Product ID and 1 Final Product ID then it is an assembly.
So in my stocks table, instead of the notebooks, I want them replaced by their basic components. So instead of the quantity of notebooks, I want the quantity of harddisks and graphic cards. So the final stocks table should look something like this:
| Product ID |
Product Name |
Quantity |
| P100 |
Graphics Card AB09 |
12 |
| P101 |
HardDisk AR12 |
9 |
| P103 |
HardDisk AR002 |
19 |
| P112 |
Graphics Card CR22 |
26 |
(the 3 x Notebook BC01 and 4 x Notebook BR02 have been substituted by the quantity of their components)
Could you please help me ? Thanks in advance !
