Variant Sku Search

[nethrelm]

One of the features that seems to be missing from the product manager, and I feel really should exist, is the ability to put in a Variant Sku in the Sku field and have it still find the product. Right now, this does not happen. If you have a product with a base Sku of say, '1234' and there is a variant that is '1234-M-BK' (for Medium, Black), inputting '1234-M-BK' will yield no results. I understand that a direct search of the Variant Sku field is not really practical or even possible given the dynamic nature of variants, however, incorporating a reverse matching criteria into the query would work to some extent. In SQL language this would be something like (simplified for brevity):

Code: Select all

SELECT *
FROM ac_Products
WHERE Sku LIKE @sku OR @sku LIKE Sku+'%'

Sure, there would be false positives if you have product Skus such as '1234' and '12345', but that would preferable to getting no results at all, in my opinion. Thoughts?

[sweeperq]

In the past we've worked around this by putting the sku variants in the Search Keywords field on the product page.

[nethrelm]

I appreciate the response, and indeed that would be a possible workaround - keyword being work, however. We have some 10,000 products, so to go through and add all the variant skus to the search keywords would be quite time consuming. It would also be error prone from a data entry standpoint. I need to get at the variants for inventory management purposes, so being able to scan a barcode with the variant sku and pull up the correct product is very important.

I've decided to roll my own inventory management interface and write the various search queries myself. We also need to search by GTIN and vendor code, which is a custom field I have added that is distinct from the model number since in many cases the vendor is not the manufacturer and there are two different values that need to exist. Nothing new for me here, I've done it before on a previous version of the software. I was just a little surprised that they still haven't built this kind of stuff into the included package.

[Katie]

I'll run this past the developers and see what they think. Are you able to get the products with options in your results by inputting 1234* for example?

[nethrelm]

Yes, of course the normal wildcard search works, but that is not really helpful since I need to scan in complete SKUs from barcodes. I have already gone ahead and created the interface I needed and the relevant queries. It will even return dynamic variants (though if it reaches that point it can be a bit slow). It does this by doing the reverse matching query I mentioned before, then loading the variant grid and returning the matching variant from the calculated list. If you're interested, here is the method I use (it references a couple other functions and objects, but you get the idea at least):

Code: Select all

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static IList<ProductInventoryDetailExt> GetInventoryBySku(string sku, int maximumRows = 0, int startRowIndex = 0, string sortExpression = "")
        {
            if (string.IsNullOrEmpty(sku))
                return new List<ProductInventoryDetailExt>();

            //First check for matching variant
            IList<ProductInventoryDetailExt> inventoryDetails = NHibernateHelper.CreateCriteria<ProductVariant>("PV", maximumRows, startRowIndex, string.Empty)
                .CreateCriteria("Product", "P", NHibernate.SqlCommand.JoinType.InnerJoin)
                .Add(Restrictions.Eq("PV.Sku", sku))
                .Add(Restrictions.Eq("P.InventoryModeId", (byte)InventoryMode.Variant))
                .SetProjection(Projections.ProjectionList()
                    .Add(Projections.Property("P.Id"), "ProductId")
                    .Add(Projections.Property("P.Name"), "Name")
                    .Add(Projections.Property("PV.Id"), "ProductVariantId")
                    .Add(Projections.Property("PV.VariantName"), "VariantName")
                    .Add(Projections.Property("PV.InStock"), "InStock")
                    .Add(Projections.Property("PV.Sku"), "Sku"))
                .SetResultTransformer(Transformers.AliasToBean(typeof(ProductInventoryDetailExt)))
                .List<ProductInventoryDetailExt>();

            //If there were no matches, it is either a dynamic variant sku or a product base sku
            //Start with exact matches for a product base sku
            if (inventoryDetails.Count == 0)
            {
                inventoryDetails = NHibernateHelper.CreateCriteria<Product>(maximumRows, startRowIndex, string.Empty)
                    .Add(Restrictions.Eq("Sku", sku))
                    .Add(Restrictions.Eq("InventoryModeId", (byte)InventoryMode.Product))
                    .SetProjection(Projections.ProjectionList()
                        .Add(Projections.Property("Id"), "ProductId")
                        .Add(Projections.Property("Name"), "Name")
                        .Add(Projections.Property("InStock"), "InStock")
                        .Add(Projections.Property("Sku"), "Sku"))
                    .SetResultTransformer(Transformers.AliasToBean(typeof(ProductInventoryDetailExt)))
                    .List<ProductInventoryDetailExt>();
            }

            //If still no matches, it might be a dynamic variant sku
            //We need to do a reverse match on the product base sku
            //then load up the variant manager and attempt to locate
            //a matching variant sku. This will be slow.
            if (inventoryDetails.Count == 0)
            {
                IList<int> productIds = NHibernateHelper.CreateSQLQuery(
                    "SELECT ProductId" +
                    " FROM ac_Products" +
                    " WHERE :sku LIKE Sku+'%'" +
                    " AND InventoryModeId = :mode"
                ).SetString("sku", sku)
                 .SetByte("mode", (byte)InventoryMode.Variant)
                 .List<int>();

                int index = 0;
                foreach (int id in productIds)
                {
                    if (inventoryDetails.Count >= maximumRows)
                        break;

                    ProductVariantManager pvm = new ProductVariantManager(id);
                    IEnumerable<ProductVariant> matchingVariants = pvm.LoadVariantGrid().Where(v => v.Sku == sku);
                    foreach (ProductVariant variant in matchingVariants)
                    {
                        if (inventoryDetails.Count >= maximumRows)
                            break;

                        if (index >= startRowIndex)
                        {
                            ProductInventoryDetailExt detail = new ProductInventoryDetailExt();
                            detail.Sku = variant.Sku;
                            detail.ProductId = variant.ProductId;
                            detail.Name = variant.Product.Name;
                            detail.ProductVariantId = variant.Id;
                            detail.VariantName = variant.VariantName;
                            detail.InStock = variant.InStock;
                            inventoryDetails.Add(detail);
                        }
                        ++index;
                    }
                }
            }

            SortResults(inventoryDetails, sortExpression);
            return inventoryDetails;
        }

[AbleMods]

I just ran into this same issue with a Gold R8 client. They want the search to include variant SKU and variant name.

[AnySupport]

In hopes of being helpful...

While initially the topics may seem a bit separate, the whole functionality may end up overlapping with (or being answered by) the approach that AbleC decides to take on the pending R8 functionality of a Filtered Search functionality.

Forum topic: viewtopic.php?f=65&t=17892

There doesn't seem to have been any further discussion or June 19th (although I just posted a message to check in on it), but if you have more input, it may be useful to supply it in that post as well.

(Yes I'm in the same boat of having a client who not only wants, but rather strongly needs the ability for search results to include variants, as well as the ability to filter products by values there. Otherwise there is a massive amount of manual entry that is going to be required for getting them set up.)

Thank you!

[mazhar]

I looked into admin search and ability look for variant SKU. We can have our SQL based search provider adjusted for this ability. Its complicated for FTS providers due to some constraints of Full Text Searches and how we manage our variants. For example considering most variant SKU will have pattern like BASESKU-VARSKU which means FTS will not be able to index such SKUs considering they have punctuation marks(-). If some one tries to search a keyword with punctuation marks on FTS provider it falls back to SQL Criteria queries. Our SQL Criteria search provider will have this ability in next version and product manager screen in admin will be able to search with variant SKU.

[sweeperq]

Our SQL Criteria search provider will have this ability in next version

This was almost 2.5 years ago. Has this feature been added yet?

[efficiondave]

Any update on this?

[Katie]

Yes, it was added in AbleCommerce Gold R9. All the change logs between versions are located at help.ablecommerce.com
Took me awhile to find this, since this discussion was so old.

Anyway, this is issue AC8-2589 (SQL Provider) Search by product SKU should also be searching the ac_ProductVariants Sku table

[AbleMods]

Unfortunately this does not work as expected Katie.

While the search provider may have been updated to include the sku field in ac_ProductVariants, Able Gold (by default) does not store product variants in that table. A default variant matrix is computed "on-the-fly" and does not actually get persisted to the database unless specific variant entries are changed in the Manage Variants page. So by default there's nothing for the search provider to find unless you "touch" every variant.

I've attached a short Jing video to demonstrate in a default fresh-install of Gold R12.

https://www.screencast.com/t/iIdDwy8ZF

[Katie]

The table should be populated as soon as you hit the Manage Variants button.

[AbleMods]

In Able 7.x, that's how it worked.

But not in Gold. In Gold, variant records are not written to disk unless you hit Save on the Manage Variants page. So if you just create your options and choices and VIEW the manage-variants page (not clicking Save)...no records are written to the disk. Hence to get the records written, you have to 'touch' each product's manage-variants page and click the Save button.

Here's a video demonstrating this behavior.
https://www.screencast.com/t/2NB3f6x2z2E

[Katie]

I'm having trouble viewing the video, but I can log a bug report. At a very minimum, the Manage Variants button should populate the table like in AC7.

[Katie]

I wonder if the behavior changed when we added the feature to filter variant options. Once the Manage Variants button is hit, we take you to a page that allows for selection of specific fields to edit. If you pick SKU and show, the variant list will generate on-screen. Just hit save at this point. I didn't have to change or edit anything. It just populated the variants table.

I'm sorry. I wish I could see your video. My computer is acting up again.

[AbleMods]

Technically, the records should be written to the table as soon as they're generated. The variant table is active and in use at that point. So regardless of the admin clicking anything, that data should now be in a persistent storage.

I don't know exactly when it changed. I remember asking you about it a few years ago. It's been in Gold for as long as I can remember.

It's a big issue for clients because many use variants. When their customers can't find products by sku because the catalog is variant heavy, their sales suffer.

[Katie]

the records should be written to the table as soon as they're generated.

I know why it was changed. It happened in Gold R4 (issue AC8-1679). This is a summary of the issue:

BUG REPORT: Customer has a product with over 12,000 variants. When you go to the product page it shuts the server down. They believe it's doing a query for each of the 12000+ rows in the database.

FOR TESTING: Create a product with 5 options, each with 8 choices, for a total of 32,768 variants.

SOLUTION: This was fixed by not enumerating the variant grid if the option row control is hidden.

Obviously, we're going to need to find a solution that works for all cases. Let me get a bug report logged, so a discussion can get started. If you have any thoughts, please feel free to share.

Thanks

[AbleMods]

The solution sounds like it involved just the product page?

[efficiondave]

We found the same thing as Joe and it was a real issue for us as we were trying to do a syncronization with a clients SAGE MAS 100 system for pricing and inventory and the Variant data wasn't there to sync to. I know we found a way to force it to write the the DB but would have to dig around a bit to find the code.

I love that you are being so responsive to the forums! Thanks!

[Katie]

This is going to take some thought. The performance issues were not just limited to product editing.

David, it might help if you told us exactly which variant data you need to sync with.

Joe, is there anything besides search by variant SKU that is a known issue?

I am aware of another related issue where adding another option will re-generate all the variants. Yes. I know...it's bad. But we are going to try to get that fixed too.

[AbleMods]

Joe, is there anything besides search by variant SKU that is a known issue?

You mean in general? Or in terms of other potential issues created by variants not being persisted?

[Katie]

I am actually thinking of specific issues that we haven't talked about here yet. I just want to make sure we aren't missing anything.

[jmestep]

On thing I can think of that might come up in the future- it doesn't look like the fix in R9 would apply if merchant is using FTS.

[AbleMods]

I think Judy's got the key point, solution must work with FTS. The majority (if not all) of my client base uses FTS. Nobody uses Lucene so far as I know.

I don't know of any other issues involving variants. Overall it works really well aside from the search issue.

There are still going to be one-off scenarios (like you described in your bug ticket) where product page speed is impacted. Not sure how that can be handled from a design standpoint, unless maybe a new flag on the parent product. Call it "Variant Booster" or some such thing and when it's set, the variants are rendered on-the-fly for speed advantage. When not set, the SQL tables are used. That way, the store owners and developers can decide how variants persist instead of the software deciding for them.