Scripting

So, I have a Custom Ticket Rule that takes data that is in Ticket fields and writes them to custom fields in the USER database (The user is decided by 1 of the fields in the ticket)

All of this works perfectly...

What I want to try to do.. is instead of using 1 of the custom fields in the USER, I would like to use a NON custom field "WORK_PHONE" instead...

Here is what  the code looks like that writes to the USER custom fields from the ticket... and below that is the code I tried for the WORK_PHONE field it said it updated 1 record etc.. but I could not find what was updated... :(

ORIGINAL WORKING CODE TO WRITE TICKET FIELD DATA TO CUSTOM FILEDS IN USER RECORD:

insert into USER_FIELD_VALUE (FIELD_ID, USER_ID, FIELD_VALUE)

select  5 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE39 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Direct Phone Number

union all select  3 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE3  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Phone Extension

union all select  8 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE4  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # CDK User ID

union all select  9 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE6  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # CDK Yooz ID

union all select 10 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE8  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # CDK CASHIER ID

union all select 11 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE9  FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # CRM ID (VinSolutions\Promax)

union all select 13 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE11 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Garber App ID

union all select 14 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE40 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # RW/Facebook App User ID

union all select 15 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE13 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Decision Lender (TCI) ID

union all select 16 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE15 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # RouteOne ID

union all select 17 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE17 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Dealer Track ID

union all select 18 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE19 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # GM Global Connect ID

union all select 19 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE21 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Honda ID

union all select 20 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE23 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Porsche ID

union all select 21 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE25 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Audi ID

union all select 22 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE27 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Acura ID

union all select 23 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE29 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Volvo ID

union all select 24 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE31 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Shaw Retail ID

union all select 25 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE33 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Imetrik ID

union all select 26 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE37 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # RDN ID

union all select 27 FIELD_ID, u.ID USER_ID, HD_TICKET.CUSTOM_FIELD_VALUE35 FIELD_VALUE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # ACI ID

on duplicate key update FIELD_VALUE = values(FIELD_VALUE)

This is what I tried:

insert into USER (WORK_PHONE)

select HD_TICKET.CUSTOM_FIELD_VALUE3  WORK_PHONE from HD_TICKET join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME where HD_TICKET.ID = <TICKET_IDS> # Phone Extension

I think I am missing something that ties the USER.WORK_PHONE to the correct user ID

Any suggestions would be appreciated

thanks

Jason