/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Has anyone figured out how to fill information in one field based on what was entered into another field.

04/15/2019 668 views

e.g. If I enter an asset ID into a field is there anyway to pick up information like Model and default that into another field.


I did look at custom ticket rules but it would imply me doing an SQL search based on the value entered into the Asset ID field.


Any ideas would be welcome.  

3 Comments   [ + ] Show comments

Comments

  • Can you be more specific about what type of fields you want to update? Ticket fields? Other asset fields?
  • An example would be. I was looking at trying to include a vehicle workshop system. The assets are vehicle (ambulances) records. The workshop sheet (incident) which is used by the workshop. When they fill out the vehicle fleet number I would like to pull in the registration number for the vehicle.
    • This is possible, yes. Because of the way the SMA creates asset types in the database, there are some values that will be custom to your appliance, however. I can help you but will need the values for the asset type ID, and the field IDs associated with it.

      If you have a tool like MySQL Workbench available it is easy to get these values, if not, then the queries necessary can also be run as a report.
  • Thanks for that. I will look into it.

All Answers

1

Hi @,

I built a ticket queue for device issuance. When an employee borrows a device,
such as a beamer or laptop for an internal presentation - we open a ticket in this queue and choose that asset which he/she borrows.
When saving, a rule is activated that changes the description of the ticket - as well as reads information from the linked asset.
At this point the device is handed over and the employee electronically confirms in the ticket that he has received the device (simple checkbox).

In total there are 3 rules to be executed when creating a ticket. Here is one that inserts the information from the linked asset into custom fields.
Maybe you can customize the SQL update to fit you.


SQL query:


select HD_TICKET.*,

                        HD_STATUS.NAME AS STATUS_NAME,

                        HD_STATUS.ORDINAL as STATUS_ORDINAL,

                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,

                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,

                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,

                        STATE,

                        if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,

                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,

                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,

                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,

                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,

                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,

                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,

                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,

                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,

                        case upper(STATE)

                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)

                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)

                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,

                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,

                        U1.FULL_NAME as OWNER_FULLNAME,

                        U1.EMAIL as OWNER_EMAIL,

                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,

                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,

                        U2.FULL_NAME as SUBMITTER_FULLNAME,

                        U2.EMAIL as SUBMITTER_EMAIL,

                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,

                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,

                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,

                        Q.NAME as QUEUE_NAME

                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)

                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID

                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID

                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID

                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID

                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID

                        where HD_PRIORITY.ID = HD_PRIORITY_ID

                        and HD_STATUS.ID = HD_STATUS_ID

                        and HD_IMPACT.ID = HD_IMPACT_ID

                        and HD_CATEGORY.ID = HD_CATEGORY_ID

                        and ((  HD_STATUS.NAME = 'automatic') and HD_TICKET.HD_QUEUE_ID = 19 )



SQL update:


update HD_TICKET

JOIN HD_STATUS as T7

ON T7.HD_QUEUE_ID = HD_TICKET.HD_QUEUE_ID

LEFT JOIN ORG1.ASSET as A on A.ID = HD_TICKET.ASSET_ID

LEFT JOIN ORG1.ASSET_ASSOCIATION as AA on AA.ASSET_ID = HD_TICKET.ASSET_ID AND ASSET_FIELD_ID = 10139

    set

HD_TICKET.CUSTOM_FIELD_VALUE1 = (SELECT NAME FROM ORG1.ASSET_CLASS WHERE ASSET_CLASS.ID = A.ASSET_CLASS_ID),

HD_TICKET.CUSTOM_FIELD_VALUE12 = (SELECT NAME FROM ORG1.ASSET WHERE ID = AA.ASSOCIATED_ASSET_ID),

HD_TICKET.SUMMARY = CONCAT (HD_TICKET.CUSTOM_FIELD_VALUE1, ' - ', A.NAME, '

 // 

', ' 


Requested by: ', HD_TICKET.CUSTOM_FIELD_VALUE0),

HD_TICKET.HD_STATUS_ID = T7.ID, 

HD_TICKET.TIME_OPENED  = IF(T7.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), 

HD_TICKET.TIME_CLOSED  = IF(T7.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), 

HD_TICKET.TIME_STALLED = IF(T7.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), 

HD_TICKET.SATISFACTION_RATING = IF(T7.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING), 

HD_TICKET.SATISFACTION_COMMENT = IF(T7.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)

  where T7.NAME = 'Waiting for confirmation' and 

        (HD_TICKET.ID = (<TICKET_IDS>))


Answered 05/09/2019 by: svmay
Red Belt

  • this looks good. I will have to try it. Thanks
    • Keep me informed
      cheers

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ