Adding a field to Orders table and Source Code

[Mike718NY]

I'm going to need to add fields to the Orders and OrderItems tables:
Question:
Would modifying the Source Code make those fields appear in the Class view (see pic)?
I'm guessing I would just add the fields to the Classes and they would appear.
And I would also have to update the Select, Insert, and Update code where needed.
Is this correct?

I really don't want to get and modify the Source Code, but I can't do what I
need without added some fields to the tables.

[Logan Rhodehamel]

If you add fields to the database to these tables, you will have to make corresopnding edits in source code - including load/save methods. Then the code will need to be recompiled to create a custom version of CommerceBuilder.DLL. Then the fields will appear in the class designer.

Generally, I would argue against going this route. Once you customize the source, you inhibit your ability to upgrade. It becomes a new maintenance task for you - instead of being able to install patches you'll have to manually merge the source tree and recompile your custom DLL.

There are almost certainly better choices. What version of AbleCommerce are you working on?

[Mike718NY]

thanks Logan,
I would be getting the current version of it.

[Logan Rhodehamel]

Are you aware in 7.0.3 that we added native custom fields ability to OrderItems? For instance:

Code: Select all

OrderItem myItem = order.Items[0];
myItem.CustomFields["YourFieldName"] = "your field value";
Response.Write("My value is " + myItem.CustomFields["YourFieldName"]);

[AbleMods]

Are you aware in 7.0.3 that we added native custom fields ability to OrderItems?

That's in 7.0.3? Seriously??

[Logan Rhodehamel]

Are you aware in 7.0.3 that we added native custom fields ability to OrderItems?

That's in 7.0.3? Seriously??

You bet. It had been requested for a while. Then it turned out to be useful for our own changes in kitting.

Code: Select all

OrderItem myItem;
myItem.CustomFields["YOURCUSTOMFIELDNAME"] = "your custom field data";
myItem.Save();
string myValue = myItem.CustomFields["YOURCUSTOMFIELDNAME"];

And actually you can set custom fields on the basket items in the same way. These will be copied over at the time of checkout into the corresponding order item. That means you could track custom values from the basket through to the order.

On SQL 2000 installs, total size of custom field data is limited to 4000 characters. On SQL 2005 the field is nvarchar(max) so a limit is not likely to be reached.

The data isn't natively searchable. In the database it stores into a URL encoded format, like

YOURCUSTOMFIELDNAME=your+custom+field+data&ANOTHERCUSTOMFIELD=some%20more%20data

So there's a new tool that can be leveraged. 7.0.3 and up only.

[AbleMods]

Oh.

So there's an OrderItem.CustomFields AND there's a BasketItem.CustomFields? That's completely cool !

Is there an Order.CustomFields?

p.s. sorry I don't mean to sound custom-fields-greedy but I so want an Orders.CustomFields

[Logan Rhodehamel]

Oh.

So there's an OrderItem.CustomFields AND there's a BasketItem.CustomFields? That's completely cool !

Is there an Order.CustomFields?

p.s. sorry I don't mean to sound custom-fields-greedy but I so want an Orders.CustomFields

In retrospect, we should have added this at the same time. A basket.Customfields / order.customfields combo would certainly be useful. But we didn't put it in there. One way around this (sort of) without custom coding is to create a dummy $0 basket item. Set the basket item's .IsHidden property to true. You can create this as a charge or credit item, then use it to track order level variables. Imperfect, but workable.

[Mike718NY]

Logan,
Here is an example of what I'm looking to do. Can this be done using native custom fields:

The boolean field "InStock" is added to the OrderItems table.
In orders, I add a button that will update it to True (see pic).
I then display a label to indicate the status for that product, like: "Item In Stock"

Now, when I return to that same Order page later,
is there a way to display the correct label for the current status (True or False) of the products?

And if it can, could you give me a quick code example on how to do it? Thanks

[Logan Rhodehamel]

Let me see if I follow. So the order is placed and you initially have the "instock" indicator set to false. Then you go through each order and when you have the item is in stock you click the button for the item in the order. On the customer side, viewing an order lets the customer see the current status of the order item (instock or not).

Is that correct?

[Mike718NY]

That field is entirely for internal use only.
Take a quick look at this post you'll see what my eventual goal is:

viewtopic.php?f=42&t=9422

Later on I would like to use the AC7 Inventory feature to achieive this.
But for now I just have to get the new site up.

[Logan Rhodehamel]

I am confused on the purpose of your suggested field OrderItems.InStock.

Are you just wanting to show the current stock level on the orders screen, based on the inventory data in AC? If not, please explain the button in more detail, what it's function does and what data it modifies. It does not seem right to me to manually toggle "InStock" to true or false for each order.

if you put this in Admin/Orders/ViewOrder.aspx.cs:

Code: Select all

    protected string GetStockStatus(object dataItem)
    {
        OrderItem orderItem = (OrderItem)dataItem;
        // CHECK IF THE ITEM IS A PRODUCT WITH INVENTORY ENABLED
        if (orderItem.OrderItemType == OrderItemType.Product && orderItem.Product != null && orderItem.Product.InventoryMode != CommerceBuilder.Products.InventoryMode.None)
        {
            // DETERMINE IF THERE ARE CHILD KIT PRODUCTS
            List<int> kitProductIds = new List<int>();
            if (!string.IsNullOrEmpty(orderItem.KitList))
                kitProductIds.AddRange(AlwaysConvert.ToIntArray(orderItem.KitList));
            // GET THE CURRENT INSTOCK QUANTITY AND RETURN
            int quantityInStock = CommerceBuilder.Products.InventoryManager.CheckStock(orderItem.ProductId, orderItem.OptionList, kitProductIds).InStock;
            return quantityInStock.ToString();
        }
        // NOT A PRODUCT, PRODUCT NO LONGER PRESENT IN DATABASE, OR INVENTORY DISABLED
        return string.Empty;
    }

Then you can do something like this in Admin/Orders/ViewOrder.aspx:

Code: Select all

    <asp:GridView ID="OrderItemGrid" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="OrderItemId" SkinID="NestedList" CellPadding="4" CellSpacing="0"
        Width="800px" OnDataBinding="OrderItemGrid_DataBinding">
        <Columns>
            <asp:TemplateField HeaderText="InStock" SortExpression="Sku">
                <ItemStyle HorizontalAlign="center" />
                <ItemTemplate>
                    <%# GetStockStatus(Container.DataItem) %>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="SKU" SortExpression="Sku">
                <ItemStyle HorizontalAlign="center" />
                <ItemTemplate>
                    <asp:Label ID="Sku" runat="server" Text='<%# ProductHelper.GetSKU(Container.DataItem) %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>

[Mike718NY]

>> Are you just wanting to show the current stock level on the orders screen . .

No. I currently have "Enable Inventory Management" unchecked, .. so I'm not using that at all.

I need to set that field to True when an item is In Stock from the inventory the company keeps on hand.
Later when I run the report (viewtopic.php?f=42&t=9422)
to order their products, those products in stock won't be included.

But for display purposes I need show whether the "In Stock" button was clicked.
I can display the status while they are still working on the current screen, but if
they come back to that particular order screen later I won't be able to display them again.
Does this explain it?

[Logan Rhodehamel]

Well, using the built in custom fields would allow you to maintain state - to know whether the InStock button had been clicked or not. When you are loading the page, just check the value of the custom field. If it's true, you show that instock has been clicked. (It might be more natural to use a checkbox?)

When you do your report though, it sounds like you'll need to check the value of this field. Let's assume your field is InStockClicked and it's value is either "true" or "false". Then in pure SQL, the filter would look something like this to get items where instock had not been clicked:

... AND ac_OrderItems.CustomFields LIKE '%InStockClicked=false%'

[Mike718NY]

thanks . . good idea about using Checkbox instead.
I'll be using Crystal Reports for that report so I'll be writing SQL away from AC7
and will still need to update that field in the database. But that's no problem.

I'm still not sure how to use custom fields:

OrderItem myItem = order.Items[0];
myItem.CustomFields["YourFieldName"] = "your field value";

In the ViewOrder.aspx, I need to add it to the grid : <asp:GridView ID="OrderItemGrid" . .
and I'm not sure what to do in the code-behind.
Could you show me the code on how to do this?
I'm sure it will help other people too. I always learn best by examples.

[pravin_sethia]

hi,
My BasketItem displays the custom field till last moment before checkout and is also present in the database. However, once the checkout response comes as true, the customfields are not getting copied in the table OrderItems.

Please let me know if I am missing some setting. I am working on the version 7.0.3

Thanks

[Logan Rhodehamel]

In the ViewOrder.aspx, I need to add it to the grid : <asp:GridView ID="OrderItemGrid" . .
and I'm not sure what to do in the code-behind.
Could you show me the code on how to do this?
I'm sure it will help other people too. I always learn best by examples.

If you need to get data that is too much for the <%# Eval() %> type of databinding, another valid option is to databind to a method and pass in the data item. So something like this in the ascx file:

<%#GetMyCustomField(Container.DataItem)%>

And something like this in the codebehind file:

Code: Select all

    protected string GetMyCustomField(object dataItem)
    {
        Order order = (Order)dataItem;
        return order.CustomFields["YourFieldName"];
    }

[Mike718NY]

Logan, can you take a look at this custom fields issue which
I continued here : viewtopic.php?f=42&t=12684

[Mike718NY]

I get the error "The name 'order' does not exist in the current context"
when trying to add custom fields to OrderItems:

OrderItem myItem = order.Items[0];
myItem.CustomFields["YourFieldName"] = "your field value";
myItem.Save();

[Mike718NY]

Hey Joe . . .
We need you to make a tutorial on how to do this (because I have no clue) like you did on
"How to add a Product Custom Field", which really helped me a lot. You are the master.