Developer forum

Hi Espen,

I confirm I got the same problem when importing or changing variant data in the database through integration.

I suspect the same thing as you mentioned. The SortOrder is ignored and some sort of AutoID is used instead. My only solution was to delete the options and add them again (reconfigure the Variants|) it's not pretty, but that the only thing I can think of until this bug is solved.

Although I don't think DW has marked it as a bug yet since defining variants from the interface does not generate this problem.

Adrian

If we can get a copy of the wrong data (probably a database copy) - then we can have a look at the code and see if we can come up with a fix.

@Espen - can you provide that?

Thanks, Nicolai

Greetings Nicolay.

I have sent site information regarding access to the site to support@dynamicweb.dk

Best regards,

Espen

Any progress Nicolai?

Best regards,

Espen

Hi Espen

No, sorry - we did not have time for this one yet. It is awaiting an available ressource.

BR Nicolai

I will dive into it and get back shortly,.

/Morten

Thanks for the tips Morten.

It did solve the problem by adding a clustere index. As a suggestion to you; it would be nice if this could be solved using the already existing field VariantGroupProductRelationSorting (i.,e. that you order by that field when reading data from the table)

Regards,

Espen

Dear Espen,

I am also facing the same problem with you when I tried to import the variants with data integration activity.

Could you please share how did you add the clustered index?

May I get the sample data or something please?

Thanks in advance.

Looking forward to hearing from you.

Best Regards,

May Thaw.

Hi May Thaw.

Maybe something like this:

CREATE CLUSTERED INDEX CI_VariantGroupProductRelation ON VariantGroupProductRelation (VariantGroupProductRelationProductId,VariantGroupProductRelationSorting); 

Regards

Espen

Hi,

We've been bitten by the "Variant combination order does not match" bug, so I'd like to second Espen Strømsnes' suggestion that it be fixed by making use of the already-available VariantGroupProductRelationSorting column on the EcomVariantGroupProductRelation table.

Although the variant groups were imported to EcomVariantGroups in the same order in which they're used in EcomProducts.VariantGroupId, that is not sufficient to overcome the non-determinant nature of SQL.  The problem affected several product families in our solution.

We're currently using the clustered index work-around.  It technically works, but it's not standard to depend on it. Adding an order by to the platform always works, regardless of the index. Then the index can be considered a bonus and not a requirement.

A second aspect of this bug fix will require that the EcomProvider populate the VariantGroupProductRelationSorting column when the EcomVariantGroups table is populated.  Currently, the column is 0 for all records.

Thanks,
Carolee

Hi, checking in on a status for this guy. 

Will the bug be addressed so an otherwise unnecessary clustered index isn't required in certain cases?

Hi Carolee

Let's look into it.

We need to find a way so we do not break existing solutions. If we add "ORDER BY VariantGroupProductRelationSorting" existing solutions will potentially break because that can affect the order of which records are returned.

I can add "ORDER BY VariantGroupProductRelationAutoId" which will also change the order - but should be compliant with how things was created in the index. 

Will "ORDER BY VariantGroupProductRelationAutoId" solve this for you? Or do we need a switch to change behavior so either one of these can be used?

BR Nicolai

I have created this in devops#14519 as a feature (since we are changing behavior)

https://dev.azure.com/dynamicwebsoftware/Dynamicweb/_workitems/edit/14519

Hey Nicolai,

Here's what Scott and I came up with.

1. A switch seems like the right way to accommodate both legacy projects and projects who want to make use of the sort order
   1. But if we sort by VariantGroupProductRelationSorting and VariantGroupProductRelationAutoId, do you think that eliminates the need for the switch mentioned in #1?
2. We also need to address how a value is assigned to the VariantGroupProductRelationSorting field.  We're currently unable to set the value via integration or the backend.
   1. Integrated solutions could take advantage of a new "VariantGroupSorting" field that follows the GroupSorting pattern described in the documentation.  It'd be made available by the EcomProvider and would be mapped on EcomProducts.
      1. It's a little different from GroupSorting in that the first record of any variant "family" would set the VariantGroup order for the whole family.  Subsequent records' VariantGroupSorting values should be ignored
   2. Non-integrated solutions (and integrated solutions not using the VariantGroupSorting field) need backend support for revising the variant group sort order once it has been set (DW10 wishlist item, please!)

Thoughts?

Carolee

I have committed your suggested change to the branch and we will test it with current solutions.

Are you able to verify that it solves your issue? I have attached a built version of this change.

Once verified, we will crate a feature to implement the changes on the ecomprovider.

Nicolai,

If you got notifications of my earlier update, pls hold.  When I create a new mapping job with the dll you sent, I get something rather different than I'm used to, although it does include the new parameter we discussed.  Anyway, I'm mostly out of the mapping-job business these days, so checking w/Shawn to find out if that's what we expect to see.

Nicolai,

Did you implement a parameter that I need to set to turn on the new functionality?  Doesn't seem to be working yet.

Here's what I did, in https://int-hager.mydwsite1.com/Admin/Default:

1. Delete existing Dynamicweb.Ecommerce.dll and replace with new
2. Delete clustered index on (VariantGroupProductRelationProductId,VariantGroupProductRelationSorting)
3. Map the VariantGroups field on the Import Products mapping job
4. Get a "before" screenshot of my test case 1734 (below)
5. Pull the XML for 1734 and run it through the mapping job (attached to previous comment)
6. Check the db (below)

Result

The EcomVariantGroupProductRelation.VariantGroupProductRelationSorting values are 0 for all records for product 1734.

Expected result

The VariantGroupProductRelationSorting values for 1734 would be numbered according to the order of VariantGroupId appearance in this list from the imported XML:

Does this match your expectation?

"Before" screenshot

Note: these Sorting numbers were assigned as part of an earlier work-around.

"After" screenshot

Hi Carolee

Thank you for the detailed explanation.

The change I made was only for retrieving the variant groups by their sort order. I did not make changes to the ecom provider. I did not get that part as I thought you already added the sort values.

I will talk to the integration team about this remaining thing. Should be fairly simple to imlpement.

BR Nicolai

Ah, understood. 

I clicked through a BUNCH of variant families in the site where I'm testing (and have deleted the clustered index), and all are happy.  It's not definitive proof, but it also doesn't seem to have broken anything :)

If I don't hear from you first, can I check with you in a week on the enhancement to the EcomProvider, or is it a bit longer-term than that?

Thanks for your help!

Hi Carolee,

Due to danish summer vacation, we are not able to plan it before the beginning of August. I have created the feature and put it into sprint #14645.

BR Rasmus

Hi Nicolai

To add to this we have a customer with a similar issue where all products that contains a "." in their ProductNumbers have this error, it only affects products with the "." in ProductNumber.

The solution is running on 9.15.4 currently.

Best regards
Torben Grove

Hi Torben

I cannot see that . in product number is related. But did you create a familiy based on the product.

Having variants with .net in the product number is possible.