Hi
I have data in this format. It tells me what the product code is and what new code has replaced that product:
| Product | Replaced By |
| 1000001 | 1000003 |
| 1000002 | 1000004 |
| 1000003 | 1000005 |
| 1000004 | 1000010 |
| 1000005 | 1000007 |
| 1000006 | 1000008 |
| 1000007 | 1000008 |
| 1000008 | |
| 1000009 | 1000011 |
| 1000010 | |
| 1000011 | |
As you can see sometimes a product is replaced many times e.g. 1000001 is replaced by 1000003 which is replaced by 1000005, and so on until we get to the last product 1000008 which has no replacement.
Alas, the numbers do not always increase with each step (normally they do but I have a tiny % where the replacement code is a lower lower number than the original).
What I want to get is a result that tells me what the very, very last replacement is in the chain, like this:
| Product | Replaced By | Final Replacement |
| 1000001 | 1000003 | 1000008 |
| 1000002 | 1000004 | 1000010 |
| 1000003 | 1000005 | 1000008 |
| 1000004 | 1000010 | 1000010 |
| 1000005 | 1000007 | 1000008 |
| 1000006 | 1000008 | 1000008 |
| 1000007 | 1000008 | 1000008 |
| 1000008 | | |
| 1000009 | 1000011 | 1000011 |
| 1000010 | | |
| 1000011 | | |
How could I do this in the desktop designer?
