Creating multiple POs for the same Item (3 replies)
Not without difficulty; the only way would be use an Aggregate transform, but you'd need to add in 50 odd Calc Records.
This wouldn't be very efficient.
So, undaunted here, I am still trying to make this work. What I am doing is an Excel Reader that inserts the data via a DB Writer into a simple table with columns for RecordID, Item, Qty. I found this SQL script that produces what I am looking for ... a list of the 150 records (based on my e.g. numbers) with the correct number of records for each Item:
WITH rcte AS (
SELECT Item, Qty, 1 AS n
FROM RawExcelData
WHERE Qty > 0
UNION ALL
SELECT Item, Qty, n + 1
FROM rcte
WHERE n < Qty
)
--INSERT INTO another table
SELECT Item
FROM rcte
So if there is no other solution, my plan is to add another DB Writer and insert the results of the script into a second table so I'd end up with: Excel Reader (grab the list of Items and Qtys) -> DB Writer (write this data to the RawExcelData table in SQL) -> DB Reader (read/transform RawExcelData using the script above) -> DB Writer (write this data to what becomes my usable source table for the job) ... I think.
From there, it should be downhill to create the rest of the job to make the POs. So I guess my question is ... is there no VB Script approach, similar to the SQL script above, that would allow me to do this more cleanly with a single Transform between the Excel Reader and DB Writer (table with complete list of records needed)?
Thanks for considering this.
Since I didn't hear back, I went with my solution above and it seems to work well. Hopefully this helps anyone else with these requirements in the future.
Client has a process (don't ask why - and they don't want to change) where they issue a series of POs for various Items each month. They prepare a list of data like ...
A11030 - 50
A1755G - 40
C1220B - 60
Etc
... which represents the Item to order and the number after the hyphen is the quantity to order (technically it's that number x 40,000). All other information for the POs are static and can be written into a Map transform.They will provide the above data in a simple two-column spreadsheet.
However, here is the catch that I need some help with: They do not want one PO containing a detail line for Item A11030 with Qty = 50 (x 40,000), a detail line for Item A1755G with Qty = 40 (x 40,000), and etc.
What they want is 50 individual POs each with a single detail line for Qty = 40,000 for A11030, then 40 individual POs each with a single detail line for A1755G for Qty = 40,000, and so on.
So the question is, how can I use a transform to take the Item Qtys stated (50, 40, 60) and use to create it to create that number (50, 40, 60) of individual POs ? Thanks all!