Generate all attribute combinations for all stock in one job?

Hi all,
We're setting up a store that sells a lot of products that come in different colours. We've set this up by having the top-level product, then setting up a 'colour' attribute, and then creating the individual colours for that product line, along with associated images. All good so far.

The problem is this:

1) We now need to generate all possible combinations in order to list volume of stock of each colour. Is it possible to do this without going into each and every product we sell, and clicking on the 'generate all combinations' button?

2) We're going to perform a full audit of our stock prior to migrating to our new site. Ideally, I'd like us to populate a spredsheet in the warehouse, then import this to all our products, and sub-products. Has someone created a SQL script for this that I can pinch, or shall I just develop my own?

Thanks in advance.

Hi

Is this possible in version 2.1 or 2.2?

Hi Axel,
AxelAndersen wrote:

I think no, it not available in nopCommerce 2.1/2.2 as this feature is available from nopCommerce 3.10

AHHHH My bad :-)

I meant of course 4.10 or 4.20 :-)

AxelAndersen wrote:
Yes, it available in nopCommerce 4.10 and 4.20

So how do you do that?
I only see a button on single products, not something that will generate ALL combinations for ALL products at once.

AxelAndersen wrote:
Oh for all products's, it not available.
It is available only for single product. Apologies for miss-understand.

You got any idea why not?
It seems like a feature that we will use always on all products.
Actually i don't understand why its not default.
Why do you even have to click a button to generate these combinations.
We need combination to use GTIN on the variants.

LocoLoic wrote:

It's possible to generate the XML in SQL.  Assuming you have your legacy data imported/accessible in the db and you have already populated the following Attribute tables:

ProductAttribute
ProductAttributeValue
Product_ProductAttribute_Mapping

I split my queries into 3 cases:

1 combination (color only)
2+ combinations (size and color)
Grouped Products using combinations of simple products

I migrated my attribute names exactly so they can be matched on later, or you could temporarily set the table to allow index inserts and match on them (as I did for master products).  

I concatenated my legacy attribute IDs into a CSV string so they could be easily parsed with STRING_SPLIT, which is available from SQL 2016 on.  This made figuring out 2+ combinations a little easier later on.

2+ combinations gets fairly complex quickly, but below should get you going in the right direction for 1 combo.  

INSERT INTO ProductAttributeCombination (
  --Id,
  ProductId,
  AttributesXml,
  StockQuantity,
  AllowOutOfStockOrders,
  Sku,
  ManufacturerPartNumber,
  Gtin,
  OverriddenPrice,
  NotifyAdminForQuantityBelow,
  PictureId
)
SELECT DISTINCT
  --Id
  --ProductId (master)
  nop.ProductId,
  --AttributesXml
  (SELECT Id AS "@ID",
  (SELECT Id AS "ProductAttributeValue/Value"
  FROM ProductAttributeValue val
  WHERE ProductAttributeMappingId = map.Id
    AND legacyOptions.optionDescrip = val.[Name]
  FOR XML PATH (''), TYPE) 
  FROM Product_ProductAttribute_Mapping map
  WHERE map.Id = nop.Id     
  FOR XML PATH ('ProductAttribute'), root ('Attributes')),
  --StockQuantity
  legacyStock.inventory,
  --AllowOutOfStockOrders
  1,
  --Sku
  legacyStock.actualSKU,
  --ManufacturerPartNumber
  null,
  --Gtin
  null,
  --OverriddenPrice
  null,
  --NotifyAdminForQuantityBelow
  0,
  --PictureId
  0
FROM [YOUR_STOCK_TABLE] legacyStock
  INNER JOIN dbo.Product_ProductAttribute_Mapping nop
    ON legacyStock.idProduct = nop.ProductId
  INNER JOIN [YOUR_OPTION_TABLE] legacyOptions
    ON legacyOptions.idOption IN (
     SELECT value
     FROM STRING_SPLIT(legacyStock.optionsCSV, ',')
    )
--limit to 1 option combo only (no commas exist)
WHERE CHARINDEX(',', legacyStock.OptionsCSV) = 0
--testing
--AND legacyStock.idProduct = 2047;

Yes, very nice using pure sql.

However I have a problem as I don't have legacy data, but just want to create all possible combinations on existing Nop Products.

I can't seem to get this to work using your sql