Insert custom table fields in Order Notification Email?

[ZLA]

I need a way to show data from a custom table in the customer order notification email. I think I have a way but it's a major hack and I need to know if there's a simpler way and/or if my way will work. Please note that I do not have the API source code.

I added a new table called ShipIntervals which cross references Vendors and Ship Methods to determine how long shipping will take for a particular vendor's items. For example, it lets me show that Vendor A takes 2 to 3 weeks for Standard Shipping but Vendor B takes 1 week for Express Shipping.

I created a function in a new class that takes the VendorId and ShipMethodId and returns the ship interval. That lets me display the information on the checkout and View Order pages. But now I need to add it to the Customer Order Notification Email. Can I access my function via nVelocity?

If not, here's my hack:

• Create SQL trigger(s) on ac_OrderShipments to insert / update a record in ac_OrderItemInputs named SG_ShipInterval.
• Modify order notification email template to hide this input field from normal item inputs.
• Add code to email template to display this field where desired.
• Modify all item display code (ProductHelper.cs, OrderItemDetail.ascx, etc.) to hide this order item input.

Note that by using an SG_ prefix I can easily add future information and have them automatically suppressed in all item display code.

Are there issues or pitfalls with my approach? Thank you for any assistance you can provide. -- ZLA

[mazhar]

You can not create custom NVelocity parameters to hold custom data with automatic notifications. If you want create custom NVelocity parameters then you would need to proceed with manual Email triggers as was the case with vendor invoices we discussed.

Yes your described solution may work but it would take a significant amount of customization just for show/hide purposes. It would be better to locate some place within standard code to accommodate this information.

The tip is to make use of BasketItem' class LineMessage property. Each basket item contains a property of string type LineMessage that can contain some string information. Once order is placed and basket items are converted into order items this information is still available with OrderItem's class as LineMessage.

So in OnePageCheckout's CheckingOut method which is called just before completing checkout you can iterate over all products calculate your ship interval place it in BasketItem.LineMessage. Now once order is placed and system executes NVelocity code to trigger Email you can access this information using $orderItem.LineMessage.

[ZLA]

The tip is to make use of BasketItem' class LineMessage property. Each basket item contains a property of string type LineMessage that can contain some string information. Once order is placed and basket items are converted into order items this information is still available with OrderItem's class as LineMessage.

Thanks for the infor Mazhar. What is LineMessage used for in AC7? The only references for it I found were in the AC 5.5 help information and as part of GoogleCheckout. I don't want to use this if it will conflict with other aspects of the system. Are there any other undocumented features that are useful for customization?

Since I've already located all the code locations I'd need to adjust in implementing required field and regular expression validation, it wouldn't be that difficult to add more show / hide code. But if I went with your solution, where else (besides CheckingOut) would I have to change it? I haven't explored the checking out process a lot but the line message still needs to be added if the merchant, adds new items to an order, changes the shipping method after the fact (can they?), etc. Furthermore, the line message doesn't appear by default. I still have to add it to all the same places as I would have to modify the show/hide code. [line message is probably better than my function since it won't change after the order is placed if the interval information is changed]

I might go with your solution but still use a trigger on ac_BasketShipments, ac_OrderShipments, ac_BasketItems, and ac_OrderItems to ensure the value is correctly updated no matter how it occurs. I would only need to code the triggers in those places. Can you tell me how their data is manipulated by AC? Are the shipment records created before the Items records or vice-versa?

I hope you don't mind the questions being in bold to make it easier for you to answer all of them.

[mazhar]

What is LineMessage used for in AC7?
Line message can contain some message or information you want to keep with item. Currently in Able CertiTax tax provider and GoogleCheckout are making use of this. In previous version Able code was not making use of this property. So it would be better that you proceed with your solution.

Can you tell me how their data is manipulated by AC? Are the shipment records created before the Items records or vice-versa?

You can give a try to find out the order but keep in mind that when ever basket is recalculated then exiting shipping items or rates could be dropped and new can be created.

[ZLA]

Thanks Mazhar. Though my client's plans might allow me to use Line Message, I will probably go with a custom OrderItemInput fields. But I'll probably replace my existing display code that uses the function to look up the data dynamically to pull the custom OrderItemInput field. (I'll do the lookup in the SQL triggers.) That way, future changes to VendorId, ShipMethodId or the ShipInterval won't affect what's already stored for the order.

[ZLA]

Hi Mazhar. Hope you don't mind another question about the Basket and Order Item Inputs.

For ac_OrderItemInputs, the order item inputs are identified by Name and Value and have a foreign key back to the ac_OrderItems.
For ac_BasketItemInputs, it looks like they are identified by Input Field Id and Input Value which a foreign key back to ac_InputFields which ties back to ac_ProductTemplates.

So a name/value record can be added directly to ac_OrderItemInputs but to add a similar record to ac_BasketItemInputs, there must be a product template record and an input field record.

Is that correct?

[mazhar]

Yep, its because order information remains isolated even some one makes changes product templates or fields.

[ZLA]

Hi Mazhar. It looks like the trigger approach as outlined may not work because of a coding semi-deficiency in core AC Code. My trigger tries to insert a record into OrderItemInputs when a record is added to OrderItems. When it does that, I get the following error:

Code: Select all

Error running SQL: INSERT INTO ac_OrderItemInputs (OrderItemId, Name, InputValue) VALUES (@OrderItemId, @Name, @InputValue); SELECT @@IDENTITY
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "ac_OrderItems_ac_OrderItemInputs_FK1". The conflict occurred in database "StorkGiftsDev", table "dbo.ac_OrderItems", column 'OrderItemId'.

My first thought is that this is because the OrderItems record isn't "committed" yet so the OrderItemInputs record isn't finding the correct OrderItemId. Even if this is the case, my trigger will still cause problems because of how the calling AC code is written using SELECT @@IDENTITY. Any trigger that causes an identity record to be inserted will through off the value returned by @@IDENTITY which is just the last identity value, not the last identity value for ac_OrderItems. A better choice would be SELECT SCOPE_IDENTITY() which would produce the correct value for AC code without being affected by most trigger code. (For those who want info on alternative IDENTITY functions, see this link: http://www.sqlteam.com/article/alternat ... erver-2000).

I wanted to mention this since you may want to modify your core code to be more customizable in a future release.

I think I can still do what I want with product templates and triggers. I just need to always include a standard template field for every product. That way it will always exist in BasketItemInputs and OrderItemInputs and I can just put a trigger in on those two tables and update the value. No need to insert records and create nasty identity side effects.

While I am glad that AC is customizable and lets us developers make changes, there are definitely areas where having generic Developer fields would be a huge help. Or even a Visibility flag on certain entities. I've seen posts about custom input fields due to the fact that inputs are either Customer or Merchant and visible by default. It would be great to have a third type for us developers or at least the Visibility flag. Please know this is intended as constructive criticism. I way prefer having a product that might delivers 90% of what I need but 100% with customization than one that delivers 99% of what I need but no way to get that last 1%.

Regards.

[ZLA]

Hi Mazhar. I've got the trigger code working for ac_BasketShipments. I don't think I need to add the trigger anywhere else but I wanted to run it by you or another developer. Can you confirm the following?

• Basket Item Inputs records are only inserted; they are never updated directly as far as the BasketItemId column is concerned.
• Order Items records and Order Item Inputs records are just inserted and transferred from Basket Items and Basket Input Items at Checkout; they are never updated, even by the admin.

Please let me know if my assumptions are incorrect or if there are any scenarios I should test out, particularly with regards to changing order information after checkout via admin.

Thank you again for all your help.

[rktect]

@ZLA you are correct that using SCOPE_IDENTITY() would fix this problem.

The problem is caused by triggers that perform an INSERT and create another identity value.

There is a way to get around this currently. The application is expecting a recordset containing one row, one column with the ID value of the new record.

So, to fix this, add this as the last statement in your trigger: SELECT TOP 1 id_column FROM inserted (where id_column is OrderItemId or whatever depending on which table you are working with)

So, now the statement "INSERT ...; SELECT @@IDENTITY" will perform the insert, fire your trigger (which selects the correct ID value as the last statement), then returns a second recordset @@IDENTITY (this recordset will be ignored by the application).

Note its typically bad practice to do SELECTs which return resultsets in a trigger, but in this case its the only way around this.

Hopefully this helps,
rktect

[ZLA]

rktect, that looks like a great hack. I needed to summarize it to see how it'd work. For future reference, you get the following:

-- INSERT OCCURS
-- recordset 1 - the trigger's SELECT statment returns the correct value (identity of primary table)
-- recordset 2 - the app's SELECT @@IDENTITY returns the incorrect value (identity of secondary table)

As long as the app looks for the value from the first recordset, it should work.

I'll keep that on file for next time I need to do something like that.