We have added a custom data field for use in Ticket Orders.
The problem is that it only saves 12 characters to the T_Order table.
I ran a SQL Profiler on the order save and found the following:
UPDATE T_ORDER SET order_no = 1847029, transaction_no = 634807, custom_1 = 'Collect Conn' WHERE order_no = 1847029
The code is truncating my custom data field (custom_1). It allows 255 characters in the t_order table, and so I don't know why it is doing this. It doesn't seem to be part of a Stored procedure and therefore I can't find where to look at the code.
Thanks,
- Blair
Hi Blair
Does this custom field use a dropdown to select the value instead of typing it in? If so, this is what is causing the issue.
If you are using a dropdown, I am guessing that you have stored the values in t_kwcoded_values? To get around the truncation I use the id from t_kwcoded_values as the value to be stored in t_order and description is what is displayed. In the order you see the description, but it does mean linking back to t_kwcoded_values if I want to output the description elsewhere.
Hope that helps.
Sandra
Hi Sandra,
That works to store the id value.
But we are using it as an Element in a Header (Ticket Design) and I don' t see how I can link back to t_kwcoded_values as properties for that element (Custom Data 1) only allows me to link to the custom_1 column in t_order.
Or, is there something I am missing?
The simplest solution would be to add a CASE statement to the Mask section for the Element on the Ticket Header. Below is what I have used, 360 is the id in t_kwcoded_values that represents Printed and Sent and 361 is Paid and anything else is Pending (ie if the field is empty)
CASE ( Val WHEN '360' THEN 'Printed and Sent' WHEN '361' THEN 'Paid' ELSE 'Pending')
Another option is to make a User Defined Ticket Element by adding some code to LP_TICKET_ELEMENT. The code below worked when I quickly tested it and represents User Defined Element 4
If @ude_no = 4 and @customer_no > 0
select @ude_value = ISNULL(a.key_value, '') from t_kwcoded_values a
join t_order b on a.id=b.custom_1
where b.customer_no = @customer_no
and b.order_no = @order_no
Of course try all of these in Test first before you implement them in Live. My preference is for the CASE statement. You will need to make sure that any existing data in custom_1 has been updated to the id and doesn’t have the truncated text in there. If you use the 2nd option any text in that field will cause an error.
Hope that helps
S.
I utilized LP_TICKET_ELEMENT as it was more dynamic (the end user could add more values and not have to understand CASE statements).
Ironic, because that is where I started my odyssey, but I thought there was a simpler way (shouldn't have listened to that Hermes fellow!).
By the by, I did use your CASE statement to solve another problem we were having with GA seating ticket text. The hard coded text wasn't showing up on the Print at Home, because there was no 'value' for the particular Element ID. So I added a CASE statement, 'When Not Null then 'General' ......
So thanks twice.
--> Did you know that you have a bit of a Fan Club here amongst the Tessi users in Vancouver, after your stage performance in San Antonio!
That’s great that both options ended up being useful!
A Fan Club of my own! *blush*
In the words of Janis … Peace, Love and Tessitura … Man