Automatically Populating Catalogue Data

[dave_reach]

Hi,

I have written an application which connects to our client's current database and converts the data, before inserting it into the AbleCommerce database.

I am transferring Product data including Options/Variants.

Code: Select all

Step 1. INSERT INTO ac_Products
Step 2. INSERT INTO ac_Options
Step 3. INSERT INTO ac_OptionChoices
Step 4. INSERT INTO ac_ProductVariants - not sure why I have to do this, but the drop-down boxes wouldnt appear on the product pages without them.

Now, I have a very strange problem - if a Product has more than one Options (Ie. Wood Type/Metal Type) the first one is displayed correctly. When I select a value from the first drop down, the correct secondary drop down is displayed, however, it has no values inside it.

If I then go (via the AbleCommerce admin panel) to see the Options/Variants associated with this Product and click "Manage Variants" to get a list of all the Variants for this product the list is correct. If I then click 'Save' without actually changing anything then the product will work as desired.

I am guessing that something extra happens when you save the variants - but I have no idea what! Can anyone shed any light on this?

[dave_reach]

Ahh!

I believe I have to create a new variant for each previous variant, right?

Code: Select all

ac_Options
Id          Name
4           Wood
5           Metal

ac_OptionChoices
Id          OptionId            Value
1           4                       Oak
2           4                       Beech
3           4                       Wenge
4           5                       Chrome
5           5                       Brass

ac_ProductVariants
Id          Option1          Option2
1            1                   0                 - not needed
2            2                   0                 - not needed
3            3                   0                 - not needed
4            4                   0                 - not needed
5            5                   0                 - not needed
6            1                   4                 - Oak/Chrome
7            1                   5                 - Oak/Brass
8            2                   4                 - Beech/Chrome
9            2                   5                 - Beech/Brass
10          3                   4                 - Wenge/Chrome
11          3                   5                 - Wenge/Brass

Right?

[AbleMods]

Dave,

Creating Variants through code is tricky. Creating them directly in the tables is even harder.

Build the Option
First you build a new Option in ac_Options. This acts as a collection of choices you will be adding later. It also decides whether swatches will be used etc.

Load the Choices
Then you load your option choices into ac_OptionChoices and assign them to the OptionId of the option you created in ac_Options.

Build Variants and Assign Choices
Finally, you put everything together by creating ac_ProductVariants records. Each record gets assigned the ProductId for the product you want to have variants. On each ac_ProductVariants record, there are Option1, Option2, Option3 etc fields. Each field represents the OptionChoiceId of that particular variant.
If you are creating a multi-dimensional variant i.e. Color and Size, then you would populate both Option1 and Option2. I only create 1-dimensional variants so I only ever populate Option1.

It's far easier and safer to manage and construct variants using the Able classes and methods.

From your examples you've listed, it looks like you've got it right but it's a little hard to tell.

[AbleMods]

By the way, there are some methods called by AC7 when pulling up the Variant screens in the Admin side. These methods construct the final variant structure for display prior to saving to disk.

That's the reason you can make it work after pulling it up in the Admin edit screen.

So the moral of the story is you are missing something in your manual variant construction. Able "fills in the gaps" when you pull up the Variants edit screen.

I'd suggest just building what you want first in AC7 - then study the DB records to see how Able wrote it.

[dave_reach]

Cheers for the reply, I'm fairly certain i've cracked it now but right now, on a friday evening my head is far too cluttered to make the final push so i'll leave it till Monday.

Thanks again,
Dave.

[dave_reach]

Hi,

Successfully done this, and it works like a dream.

Next step... Product > Category relationships. Am I being silly? I cannot find a table which would hold this data - we have a lovely ac_Products, and a lovely ac_Categories but nothing linking the two together?

I wondered if this is something to do with CatalogNodes, but can't find anything that would do what I want.

Can anyone shed any light on this?

Thanks,
Dave.

-- edit --

Is there any sort of database diagram available? If so I probably wouldn't have to ask these questions, I shall see if I can find one!

[AbleMods]

No database diagram from Able yet. It will be huge when it does become available.

ac_CatalogNodes connects the categories, webpages and products to each other.

[RKS_213]

Can you add fields to Options and Variants? The site I'm working on has SKUs assigned to sizes, operating ranges, and part numbers. So each of their product display pages needs a table like :

SKU Size Part Number Operating Range

So I can add each option, but I don't see how I would associate all of these with one SKU unless it is all attached together. Then, if I'm not adding these fields I will be left with the task of figuring out how to populate the table with all of the details needed correctly under their headings.

Thoughts?

[AbleMods]

Can you add fields to Options and Variants?

Only if you have full source code or add a new table via custom programming.

Populating a large variant structure is rarely simple. It might be possible to build your variant structure in Excel and then use Dataport to import that variant setup into Able. It's not well documented though, so plan extra coffee for the project.

The other option is to hire a custom developer to write an import routine or a catalog-construction routine to build your variant setups the way you want. Still though, somebody, somewhere, has to tell the system what sizes, operating ranges etc go together.

I just finished a similar project a few weeks ago for a client. The need in that case was digital goods with 3 choices per product, over 50,000 products in all. No small task if you had to make them one-by-one-by-one using the Able interface. With the import routine I wrote for the client, all of that work was done in about 7 minutes. Even considering the custom development costs, it was far more economical (and accurate) to write an import routine rather than build it all manually.

Data-driven storefronts are great until you realize just how much data you really have

[RKS_213]

I'm not sure what you're saying is what I'm thinking. For my use case, there are only two options.

Option1 : Large blue shirt
Option2 : Small green shirt

What I'm saying is that right now, if I choose Option1, I'm basically choosing the color blue (that is, if I set option1 based on color.) That is fine, but Option1 in my case is more than just the color blue. Imagine this, you have two choices, a large blue shirt, or a small green shirt. Small+green and large+blue will ALWAYS go together at my store. If I use the default settings, I'm letting my customer choose between blue and green, and then again between large and small. So they could end up with a small blue order when that doesn't exist because blue+large will always go together.

I don't need the system trying to match up different options or validate that admin input is the correct correlation. It's almost like Option1 will just change parts of the description and I just need to let the user choose what color they want, but also inform them at the same time of all the details the option holds.

Maybe I'm not thinking about options and variants the correct way. I think this might be the case because it seems like I'm trying to lock variants with other variants and at the same time to one option. To me it seems AC is built in a way to only lock variants to an option.

If I could figure out the adding custom fields in 7.0.5 than maybe I could use that to display what I need. Alas, there's no documentation for that either.

[david-ebt]

Could you create an option called "large blue" and a second option called "small green"?

[jmestep]

In Able by default, the options get created first. Then if there are certain kinds of changes to the option combination the resulting variant is created in the variants table. Those changes include things like price modifier, weight modifier etc.
In your case for the shirts, you could set an option called Size and Color, then add the two you mentioned as two choices for the option. If you have two options, like size and color, you can see the combinations created on the manage variants page, but that doesn't necessarily mean there are entries in variants table. If you want to create your example product with both size and color option, then you would need to uncheck the non-existent combinations on the manage variants page.

[RKS_213]

Ok. I figured out what I'm trying to say and why my thinking isn't matching up with the rest of comments here.

What I'm really looking for is a Product Class. The Product Class is the actual product display and there are no displays for the individual products, just an options list.

So if I say I sell T-shirts, navigation looks like Home->Apparel->T-Shirts and that's the end. The T-shirt class isn't for sale, but all the products of that class are and displayed on the page.

I can see in the options and variants that I can add the "small green", "large blue" options but the problem is that there isn't enough flexibility that I need. For example, in my real use case, each product option should have it's own SKU, Part Number, etc. I can only save a part number and sku for the top product that is displayed. The UI for options let me set a SKU and different choices on the option list, which is great, but that SKU doesn't show and if I delete the SKU and Part Number from the main product, the area is left blank in the product

Last question. I'm using the BuyProductDialogOptionList ConLib and it has everything sorted in a grid by default, which seems backwards to me. How can I change that to a straight across list view, just like in their own example (http://help.ablecommerce.com/mergedProj ... ptlets.htm) So the end result will look like:

Code: Select all

SKU            Part Number         SIZE            OPERATING RANGE          
1x               1x                 1x               55 degrees                      add to cart
2x               2x                 2x               55 degrees                      add to cart

Right now it looks like this:

Code: Select all

Model (Main Product)    Part Num (Main Product)     Option1Name      Option2Name
1x                       1x                           Size 1          Size 2          add to cart (both sizes?)
1x                       1x                           Size 1          55 degrees       add to cart (?)

I know it's going to look bad once this box is resized after posting but maybe it won't. I'm going to take a screen shot and put it up since the site is not live.

[AbleMods]

Ok sounds like you're wanting to do dynamic SKU values based on the variant choices made by the shopper. Here's how it works using your example above:

Usually there's a base SKU value for the shirt. In this case, let's say the sku is 'POLO'. So edit the main product for your Polo shirt and set the SKU value to 'POLO'.

Now go into variants. You're going to add two options.

The first option is named 'Color' with choices of 'Red,Green,Blue'.

The second option is named 'Size' with choices of 'Small,Med,Large'.

Once you've added both options to the variants page, do NOT click the Manage Variants button. Take a look at the each of the two options listed in the upper portion of the page. You'll see an Edit icon (little notepad image) next to each option. Click that edit icon for the Color option.

Once the Edit Option page appears, each option (Red, Green and Blue) will have a SKU value. Here is where you specify the dynamic portion of the SKU for each color. I usually use "-R" or "-GR" but you can do anything you want. Once you've set all the values, save it. Rinse and repeat for the Size option.

Now save the product and preview it.

Now the system will dynamically adjust the SKU of the main product based on the shopper choices. So initially the Item # will display as 'POLO'. That's fine, you haven't made any choices yet. Change the Color option to 'Green' and see what happens.

The page refreshes and the Item # field becomes 'POLO-GR'. Choose a size and the Item # becomes 'POLO-GR-XL'.

[RKS_213]

I did that but that's what I mean. Here's how the options list looks now after following your post:

Code: Select all

13602	Size	Price	
		Red	  S	$0.00	Add to Cart
		Green	S	$0.00	Add to Cart
		Blue	 S	$0.00	Add to Cart
		Red	  M	$0.00	Add to Cart
		Green	M	$0.00	Add to Cart
		Blue	 M	$0.00	Add to Cart
		Red	  L	$0.00	Add to Cart
		Green	L	$0.00	Add to Cart
		Blue    L	$0.00	Add to Cart
		Red	  XL	$0.00	Add to Cart
		Green	XL	$0.00	Add to Cart
		Blue	 XL	$0.00	Add to Cart
		Red     XXL	$0.00	Add to Cart
		Green	XXL	$0.00	Add to Cart
		Blue    XXL	$0.00	Add to Cart

I don't understand why that is even an option. I don't need ANY option grid, matching, etc. I want a straight across list like in my original posting.

Ie

Code: Select all

SKU1     red     large     longsleeves
SKU2     blue    small     longsleeves
SKU3     green   med       shortsleeves
SKU4     yellow  XL        shortsleeves

There are no other options. You cannot mix and match. It's just a list, that's all you get. You choose one. There is no reason to match anything because it's not a mix and match type thing. Yes, there are four options to choose from, but each option is one or the other and the attributes are unchanging.

[RKS_213]

If you look here, this is EXACTLY what I need the options to do.
http://www.jetlubecanada.com/pages/Nikal.html

The problem is that I think everyone is thinking about options as it refers to clothing etc. Machinery and industrial goods are not the same way.

Take this for example, if you're building a Wench (an extremely large vehicle with a long arm to lift extremely heavy items) you cannot mix and match attributes. If you need your Wench to be able to lift 5 tons, it's going to be huge. Due to physics, the back end of the Wench must match the maximum load of arm or else it will tip over.

if you buy a wench that is 5 times smaller, you're only going to get 1 ton of lift out of it. There is no saying you want a small Wench that lifts 5 tons. It's physically impossible. So the option list for this Wench would look like

Code: Select all

SKU      Size      Lift
111      Large     5 tons
222      Small     1 ton

That's the way it has to be because it does not exist in any other form. A large Wench with 5 ton lift or small Wench with 1 ton lift. As this example refers to the link I provided above, there are five options with different attributes that don't exist in any other form. You get one of those options, no mixing, and if you don't like it, you're going to have to compromise on the closest thing you need.

[AbleMods]

Ok.

So 'lift' isn't really an 'option'. It's mandatory because it's dictated by the size. So include the lift in the option choice for Size.

Just make a single option on the variants page for 'Size'. For the options choices, specify "Large 5 Tons,Small 1 Ton". That'll give your shoppers one option with two choices. Obviously you can add more choices for that option if you desire.

Onec you've added the option, click the Edit icon next to that option and specify the SKU and other values you want for each specific option.

If you're wanting a 'range' of values but only specific ones apply because other ones are physically impossible, that is available as well. You'll just need to uncheck the 'Available' checkbox on the variations that do not apply from the Variants Grid page.

The system won't be able to mathematically derive what option choices are valid without being customized by a developer.

[RKS_213]

That way seems to work, AbleMods. It was hard to see since it structured it like it did when I added two options. Doing it that way provides a line item of sorts just the way I wanted. Thanks.

[AbleMods]

Excellent, glad you got it figured out.

If this stuff were easy, everybody would be doing it