Hi Ruthy,
We do something similar via a workflow. Our example is copying a "Catalog" record with linked "Item" records. The "Item" records also have linked "DAM Link" and "Item SKU" records. As shown below:
When a user wants to make a copy of the catalog, they will start the Copy Catalog workflow and provide a new catalog code as a parameter on the workflow.
We utilize a variety of BICs in the workflow but I'll just mention the ones relevant to your scenario.
Since the WorkItem in the workflow is associated with the 'copy from catalog', the first step we do is create a WorkItem property with the "SQL Set Properties BIC" that will be the key that will be placed on the new catalog record (it's a hidden attribute only used to help this workflow). This way we always know what the new catalog is from the workflow since copying the catalog record doesn't tell us the new sequence generated key (Catalog Id) on that record (at least that I know of anyway). Then we can always reference that key from the WorkItem property as a parameter in later SQL queries. For us, the key we use is a concatenation of the InternalRecordId of the 'copy from catalog' and the current timestamp - just to ensure uniqueness - to create the WorkItem property 'NewTargetCatalogKey'.
So after we get the key, we use the "SQL Copy Repository Records BIC" to copy the catalog record ('copy to catalog'). We set an attribute on the new catalog record to the value of our 'NewTargetCatalogKey' from the previous step. This is what helps us in the next few steps.
Next is copying the "Item" repository records. You'll notice in the diagram above that we need Catalog ID on the Item record. So we use the "SQL Copy Repository Records BIC" to copy the Item records that have Catalog ID that matches that of the 'copy from catalog'. When these records are created, we set the Catalog ID attribute on all the new Item records to the Catalog ID of our 'copy to catalog' (which is identified by the 'NewTargetCatalogKey'). Another thing to note that will make sense in the next step is that our "Item" records have an 'Item Code' attribute that is user set and must be unique for all Items in a Catalog. (The 'Item Code' attribute comes along for the ride when the records are copied). This is our SQL for copying "Item" records.
SELECT I.InternalRecordId AS OldInternalRecordId, NULL AS NewInternalRecordId, NewC.CatalogId AS 'CatalogId'
FROM Item_STG I
INNER JOIN Catalog_STG OldC ON I.CatalogId = OldC.CatalogId
INNER JOIN Catalog_STG NewC ON NewC.NewTargetCatalogKey = '%NewTargetCatalogKey%'
WHERE OldC.InternalRecordId = %itemIds%
Next is copying the "Item SKU" repository records. Similar to the 'Item' record needing a Catalog ID, the "Item SKU" records need an Item ID - and we need them to be the Item ID from the Items in the new 'copy to catalog'. So we use the "SQL Copy Repository Records BIC" again to copy the "Item SKU" records that are linked to "Item" records linked to the "Catalog" record of the 'copy from catalog'. We make the identification of "Item SKU" records with several join conditions. Essentially, we are selecting the "Item SKU" repo joined to "Item" repo joined to the "Catalog" repo associated to the original 'copy from catalog' record. And we are selecting 'Item ID' from the "Item" repo joined to the "Catalog" repo associated to the new 'copy to catalog' record. And here is where we reference the 'Item Code' attribute on the "Item" repo mentioned in the last step, so we are sure to link the new "Item SKU" records to the correct corresponding "Item" records. Below is a diagram to help describe this as well as our SQL in the BIC.
SELECT OldItemSKU.InternalRecordId AS OldInternalRecordId, NULL AS NewInternalRecordId, NewItem.ItemId
FROM [ItemStockingUnit_STG] OldItemSKU
INNER JOIN [Item_STG] OldItem ON OldItemSKU.ItemId = OldItem.ItemId
INNER JOIN [Catalog_STG] OldCat ON OldItem.CatalogId = OldCat.CatalogId
INNER JOIN [Catalog_STG] NewCat ON NewCat.NewTargetCatalogKey = '%NewTargetCatalogKey%'
INNER JOIN [Item_STG] NewItem ON NewCat.CatalogId = NewItem.CatalogId AND OldItem.ItemCode = NewItem.ItemCode
WHERE OldCat.InternalRecordId = %itemIds%
Next step is essentially the same as copying the "Item SKU" records, but for copying "DAM Link" records instead. Again, we use the "SQL Copy Repository Records BIC" to copy "DAM Link" records. So I won't go into the detail here, but this is the SQL to get the proper "DAM Link" records and substitute with the new 'Item ID's from the new "Item" records.
SELECT DL.InternalRecordId AS OldInternalRecordId, NULL AS NewInternalRecordId, NewItem.ItemId AS 'PK1'
FROM DAMLink AS DL
INNER JOIN [Item_STG] OldItem ON DL.PK1 = OldItem.ItemId AND DL.RepositoryName = 'Item_STG'
INNER JOIN [Catalog_STG] OldCat ON OldItem.CatalogId = OldCat.CatalogId
INNER JOIN [Catalog_STG] NewCat ON NewCat.NewTargetCatalogKey = '%NewTargetCatalogKey%'
INNER JOIN [Item_STG] NewItem ON NewCat.CatalogId = NewItem.CatalogId AND OldItem.ItemCode = NewItem.ItemCode
WHERE OldCat.InternalRecordId = %itemIds%
One thing to add is that we also have another level of linked relationships under the "Item SKU" records for "Item SKU Prices" that we also copy in certain scenarios. So I'd be more than happy to share details of that as well if it would be useful for you in your example.
I hope this all helps! Let me know if I can help clarify anything.
Thanks,
Josh
------------------------------
Joshua Swett | Sr Applications Engineer
Steelcase, Inc | 2696251984
------------------------------
Original Message:
Sent: 05-17-2022 14:13
From: Ruthy Sleeth
Subject: most efficient way to copy
hi,
we're looking for the most efficient way to copy a Product Record - including all the link relationships and dam links. the Copy function in EnterWorks copies a repository, which is good, however we are looking for something that will copy the entire record including relationship links.
does anyone else do this? and if so, are you willing to share "How" this is done?
thanks!
: )
------------------------------
Ruthy Sleeth | Business Process Analyst
New Pig | 814-686-2254
------------------------------