Orphaned Product Variants in DB?

[michaewa]

Good evening - I am helping a client develop some inventory reports using the data in their Able store, version information as follows:
PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2005
AC SCHEMA v2000

I am attempting to create a report that shows their current inventory by querying ac_products and ac_productVariants, and pulling in labels as needed from the option tables.

I am running across a lot of records in the ac_productVariants table that appear to be orphaned or otherwise invalid. For instance, if there are 10 possible variants for a product, there will be 20 rows in ac_productVariants tied to the product via the productID. When I query the DB directly, I get all 20 records, and cannot discern which are actually valid, other than the fact that the invalid records generally appear first. When I view the product 'Variants' page through the administration, I only see the valid records.

The invalid records sometimes show an 'inStock' value of 0, and sometimes have a value of 1 or more.

Here is a sample of what I am seeing for a product with the ID of 505. You can see the first and second groups of records flip-flop the value of the option1 and option2 columns, otherwise I can see no difference. The variants with an ID of 2934 and greater show in the administration, those before it do not.

ProductVariantId ProductId Option1 Option2 Option3 Option4 Option5 Option6 Option7 Option8 VariantName Sku Price PriceModeId Weight WeightModeId CostOfGoods InStock InStockWarningLevel Available
2389 505 3438 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2390 505 3439 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2391 505 3440 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2393 505 3436 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 0 0 True
2395 505 3445 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2397 505 3442 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2398 505 3444 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2399 505 3449 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2401 505 3441 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2402 505 3446 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2403 505 3447 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2404 505 3448 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2405 505 3450 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2406 505 3451 3432 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2934 505 3432 3437 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2935 505 3432 3438 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
2936 505 3432 3439 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
2937 505 3432 3440 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2938 505 3432 3442 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2939 505 3432 3443 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
2940 505 3432 3444 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2941 505 3432 3445 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2942 505 3432 3446 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2943 505 3432 3447 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2944 505 3432 3448 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2945 505 3432 3450 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
2946 505 3432 3451 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True
3278 505 3432 3441 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 2 0 True
3279 505 3432 3449 0 0 0 0 0 0 <null> <null> <null> 0 <null> 0 <null> 1 0 True

I am not familiar with how the queries are structured in Able, but my question this: Is this expected behavior? If so, any suggestions on how to structure a SQL query to avoid the invalid records? (I say invalid as they don't show in the administration section). If not, is there a suggested fix?

Thanks in advance, as this has been driving me quite mad.

[mazhar]

Instead of making direct quires to better make use of AbleCommerce API to access products and variants by using their different loader functions. Have a look at those pages in website where products and variants are being processed to understand how to load and process variants.

[jmestep]

Because a variant is showing up in Able, it doesn't mean that there are entries in the variants table. There will be an entry in the ac_ProductOptions table. The admin manage variant screen will show variants when there is nothing in the table. For example, I have a test product that has 10 options for color (productId 25, optionId 29).
There are 10 entries showing in the manage variants screen, but no records in the variants table.
Then if I change one of the variants in the admin to have something like a price modifier or not available, that record is added to the variants table, but the other color options aren't.
That's one reason mazhar is saying to make use of the AbleCommerce api.

[michaewa]

Thanks for the quick reply mazhar. Unfortunately the application in question is not a .NET application. I've had a look at the API guide but there does not seem to be any provision for calling the API from a non-.NET application.

Can you point me to a reference guide for using the API from other platforms (this particular application is written in classic ASP)?

Also, is it expected that there would be orphaned records such as these in the database? I'm not familiar with Able, but that seems an unusual practice to me.

Thanks.

[mazhar]

If you want something like cross plate farm means report client side is in classic ASP then most probably you would need to write some webservice in AbleCommerce application. This service then can be used by classic ASP to fetch results from AbleCommerce application.

[michaewa]

Great point Judy - I completely understand that logic.

However, since I am concerned with inventory, I am interested in the converse of the case you described - namely, when there *is* an entry in the ac_productVariants table. Not because of price modifier, but because of the value of the inStock field.

Maybe I'm thinking about it the wrong way, but it seems like if you're tracking stock at the variant level, then for each variant that has stock there would be one and only one record tied back to that product that says how many units are available for purchase. What do the extra records represent?

Thanks very much for your assistance.

[michaewa]

Ok, if direct query is a non-starter due to the orphaned records in the database, can anyone point me to the correct functions that should be called? I would imagine this is a common request, I would basically like an inventory report with one line for each product or variant, along with the number of units in stock.

Are there third parties that sell add on modifications to Able like other carts?

[mazhar]

For variants and product code samples look at corresponding web application parts. We have a number of partners providing custom development services, have a look at this list here http://www.ablecommerce.com/Shopping-Ca ... 11C33.aspx

[jmestep]

I've never seen orphaned variants before. How did you create them at the beginning? Did you do them via the admin, or did you add some of them to the table directly?

[michaewa]

Thanks Mazhar, I'll have a look.

Judy - No records have ever been manually inserted into the database, only through the 'natural' process of adding options to products via the admin. Probably 20% of products with variants have these records associated with them. I think the fact that the Able admin interface can distinguish between the orphans and the 'correct' records without fail strongly suggests it is an issue with Able, either the current or a legacy version from which the current cart was upgraded.