How are Options / Variants encoded in ac_OrderItems?

[ZLA]

Can someone explain to me how options get encoded into the ac_OrderItems and /or ac_OrderItemInputs table? We have a simple option with a few choices the user can select from. I expected to have the choice appear in ac_OrderItemInputs as a separate record but instead, it's entered into the optionlist and variantname fields as:

• optionlist = 88,0,0,0,0,0,0,0
• variantname = Assembled +$25.00

Why is the optionlist field a list of comma separated values? Our site doesn't use multiple quantities for a given item but I suspect this is why the optionlist is encoded this way.

Can someone please educate me with a few more details?

Thank you in advance.

[jmestep]

Because that's the way Grandma did it?
It's one of my pet peeves.

[ZLA]

Hi Judy. Can you explain just how Grandma did it? I haven't worked with the options much so I'm not sure how that data gets encoded into the option items. I'm going to run some tests but if you can provide some quick guidance, I'd appreciate it.

[jmestep]

"That's how Grandma did it" was a joke for something that didn't make much sense. I don't know why Able does it that way, but I believe they always have.
I know the option list is comprised of the id for each choice and you can see the ids in view source of a product page for each selection;


<option value="7952">13</option>
<option value="7953">14</option>
<option value="7954">15</option>
<option value="7955">16</option>

[Logan Rhodehamel]

[quote="ZLA"]optionlist = 88,0,0,0,0,0,0,0[/list]

The first choice associated with the order item is OptionChoiceId 88. The second through eighth choices are zero (no choice).

Judy - is your frustration that we store the values in a comma delimited list, or that we include the zero placeholders that seem unnecessary?

[ZLA]

Judy - is your frustration that we store the values in a comma delimited list, or that we include the zero placeholders that seem unnecessary?

Obviously I can't speak for Judy but I would say I am also nonplussed byt this encoding. I don't care about the zero placeholders. I do care that it isn't treated the same as the other order input items. Other than grandfathered code, perhaps there is a reason the user's choices can't be stored the same as a product template's values, but I haven't figured that out yet. Can you explain to me what I'm missing here?

In my case, I'm building an XML file based on the user's selections. I have perfectly good code that iterates over the item choices. But now I have to create entirely separate code that will not only have to address the optionlist field but will also have to split this code into it's constituent parts. I'm doing this in SQL but fortunately, I have a user function that can split a string. But this encoding of optionlist complicates the issue.

Thank you.

[jmestep]

It so happened I am more frustrated with it because of a customization someone wanted today that he thought would be included in a normal site design package.
He wants to have packing lists print out with the name of the option and the selection and it's a reasonable request. In the basket forward, the option selection shows, but not the name of the option. For example, the order item might show Black, 12 inch, 10 inch, left. So it's hard to decipher. Black what? what is 12 inch? It should be like Book Cover:black, Width: 12 inch, Height:10 inch, Monogram location:left. As it is, if you want those to show, you have to include them in your selection text. Instead of saying Color:[drop down of black, blue, red], you have to say Color:[color-black, color-blue, color-red] when you build your options for the product.

[ZLA]

Now that I've looked into this more, I have to say this is more than a pet peeve to me. I would consider it a design flaw as noted below.

Variants let you modify the price and cost of goods. But in the Order Items table, it only updates the price field. Cogs is left at the main product value. This is bad because:

1. It's Inconsistent. We have product.price and product.cogs and variant.price modify and variant.cogs. Adding these "mathematically", we should get orderitem.price = product.price + variant.price and orderitem.cogs = product.cogs + variant.cogs. But AC says product.cogs + variant.cogs = product.cogs.
2. So let's say you decide to parse the optionlist values and look up the cost of goods for the selected choices. What happens when there's a price increase? Your orders' cost of goods amounts are now wrong! Isn't that why the order tables store all details of an order - so it is preserved against future changes.
3. This last issue is bad if you are a drop shipper and email orders to your vendors. Let's say you include the wholesale price (cogs) in the email. And say you have to resend the last order to a vendor but you just updated all your cost of goods for their annual price increase. Now your email has the wrong wholesale price (but the retail price hasn't changed).

I can solve this by adding a new table (orderitemchoices?) which is inserted, updated or deleted based on a trigger from ac_orderitems. I'll parse the optionlist and populate the costofgoods for the selected choices. Because the underlying AC code uses @@IDENTITY instead of Scope_Identiry(), I can't use an identity column but will have to roll my own. I shouldn't have to.

Is there a better way to solve this problem? I'm sure someone will tell me to use kits. Perhaps AC should remove the COGS field from the Manage Variants screen if it's not really implemented.

[allcourtsports]

Can anyone tell me how to include COGS in Vendor email template?