Hello all. I’ve got an issue with flattening a 1 to many relationship. For argument’s sake, let’s say I have a database that keeps track of pencils manufactured. Pencils are manufactured in lots, so two lots of pencils will not have the same lot Id.
Once a lot comes off the line the pencils are stored for later distribution. The pencils may not all be stored in one location, however. It’s possible that out of a lot of 500 pencils 200 may be stored in cabinet A, shelf 2; and another 300 may be stored in cabinet F, shelf 1.
But don’t order yet! Because the legacy system didn’t record how many individual pencils from this lot are stored in each location - only the fact that some pencils in this lot are stored across many possible locations – the new system also has this horrible constraint. (And before you tell me this is poor design I agree wholeheartedly, but the client refuses to re-inventory 70,000 pencils to record their exact location, so this is not an option.)
So now saleswoman Brenda wants to pull pencils from lot Id ABC123. She searches the system and sees that lot ABC123 has 750 pencils left in stock. She places an internal order for 400 of those pencils. The system says “can do!” and places her order. It then reports the fact that of the 400 pencils she ordered of this lot, she will have to pull from the following locations – sorry, but we can’t be any more specific Brenda!
Pencils of lot ABC123 can be found in:
Cabinet A, shelf 1
Cabinet D, shelf 1
Cabinet D, shelf 2
Cabinet K, shelf 2
When it comes time to print out Brenda’s order I had been using a user-defined function to concatenate together a list of all potential storage locations so that we kept a one-line per lot Id and qty relationship. Here order would look a bit like this:
Lot Id || QTY || LOCATIONS
ABC123 || 400 || Cabinet A, shelf 1; Cabinet D, shelf 1; Cabinet D, shelf 2; Cabinet K, shelf 2
A workaround, yes – but it did work. But wait, there’s more! Now it has been decided that some of the pencils are of such a sophisticated design that foreign powers would want to take them from the factory. The solution is to encrypt all of the storage location data so no one can actually know where the pencils are stored until the very end of their order. Big problem though – you cannot open or close a symmetric key inside a UDF so now the ability to concatenate all possible storage locations into one line of the order is lost.
But if you actually JOIN to the storage locations table you wind up duplicating order lines; one line for each storage location. And this in turn then shows false quantities ordered which in turn then sum incorrectly at the footer of the order. Hooray!
So, does anyone have a suggested workaround that returns one line of multiple storage locations for each lot ordered?