Body
The AttributeValues Table contains the custom fields information that is associated with a work item. For example, it would indicate that ticket 12345 has “Blue” selected for the Eye Color attribute. This may require joining from AttributeValues to AttributeChoices on choice ID to get the choice name.
Query Examples
You may use the following examples as a starter template.
Please note that the example does not cover every possible column to select, join, filter, or order by. You may need to modify this query to suit your needs. Refer to the related Getting Started Guide for Database Queries article for how to identify your @BEIDInt or @BEID values (tenant ID).
Text-Based Attributes
These would be attributes like textboxes, text areas, date pickers and date/time pickers.
Select
Top 10
av.AV_ID,
av.AV_ATTID,
av.AV_ItemComponentID,
cmpnt.CMPNT_Name,
av.AV_ItemID,
av.AV_ValueText,
av.AV_ValueInt,
av.AV_ValueDecimal,
av.AV_ValueDateTime
/* Add or change selected columns as necessary. */
From
dbo.AttributeValues av With (NOLOCK)
Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
Where
att.Att_BEID = @BEID
/* To filter for all saved values for a specific attribute ID, use the below line. */
And av.AV_ATTID = ATTRIBUTE_ID_HERE
/* To filter to all saved value for a specific item, use the below lines.
Refer to the Components table for valid values. For example, use 9 for tickets.
Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
-- And av.AV_ItemComponentID = COMPONENT_ID_HERE
-- And av.AV_ItemID = ITEM_ID_HERE
Single-Choice Attributes
These would be attributes like dropdowns (including yes/no), radio button lists and color fields.
Select
Top 10
av.AV_ID,
av.AV_ATTID,
av.AV_ItemComponentID,
cmpnt.CMPNT_Name,
av.AV_ItemID,
av.AV_Value,
av.AV_ValueText,
attc.Attc_Name
/* Add or change selected columns as necessary. */
From
dbo.AttributeValues av With (NOLOCK)
Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
Left Outer Join dbo.AttributeChoices attc With (NOLOCK) On av.AV_ATTID = attc.ATTC_ATTID And av.AV_Value = attc.ATTC_ID
Where
att.Att_BEID = @BEID
/* To filter for all saved values for a specific attribute ID, use the below line. */
And av.AV_ATTID = ATTRIBUTE_ID_HERE
/* To filter to all saved value for a specific item, use the below lines.
Refer to the Components table for valid values. For example, use 9 for tickets.
Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
-- And av.AV_ItemComponentID = COMPONENT_ID_HERE
-- And av.AV_ItemID = ITEM_ID_HERE
Multiple-Choice Attributes
These would be attributes like multiselects and checkbox lists. Note that these fields will have a row for each saved value selected for the item ID/attribute ID combination. For example, if attribute 789 is a multi-select and has two choices saved on ticket 12345, there will be two rows (one for each selected choice) in AttributeValues.
Select
Top 10
av.AV_ID,
av.AV_ATTID,
av.AV_ItemComponentID,
cmpnt.CMPNT_Name,
av.AV_ItemID,
av.AV_Value,
av.AV_ValueText,
attc.Attc_Name
/* Add or change selected columns as necessary. */
From
dbo.AttributeValues av With (NOLOCK)
Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
Left Outer Join dbo.AttributeChoices attc With (NOLOCK) On av.AV_ATTID = attc.ATTC_ATTID And av.AV_Value = attc.ATTC_ID
Where
att.Att_BEID = @BEID
/* To filter for all saved values for a specific attribute ID, use the below line. */
And av.AV_ATTID = ATTRIBUTE_ID_HERE
/* To filter to all saved value for a specific item, use the below lines.
Refer to the Components table for valid values. For example, use 9 for tickets.
Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
-- And av.AV_ItemComponentID = COMPONENT_ID_HERE
-- And av.AV_ItemID = ITEM_ID_HERE
Person Attributes
Select
Top 10
av.AV_ID,
av.AV_ATTID,
av.AV_ItemComponentID,
cmpnt.CMPNT_Name,
av.AV_ItemID,
av.AV_Value,
av.AV_ValueText,
av.AV_RelatedItemGuid,
u.[GUID],
u.User_FullName,
u.User_AlertEmail
/* Add or change selected columns as necessary. */
From
dbo.AttributeValues av With (NOLOCK)
Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
Left Outer Join dbo.Users u With (NOLOCK) On av.AV_RelatedItemGuid = u.[GUID]
Where
att.Att_BEID = @BEID
And av.AV_RelatedItemComponentID = 31
/* To filter for all saved values for a specific attribute ID, use the below line. */
And av.AV_ATTID = ATTRIBUTE_ID_HERE
/* To filter to all saved value for a specific item, use the below lines.
Refer to the Components table for valid values. For example, use 9 for tickets.
Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
-- And av.AV_ItemComponentID = 31
-- And av.AV_ItemID = ITEM_ID_HERE
Asset Attributes
Select
Top 10
av.AV_ID,
av.AV_ATTID,
av.AV_ItemComponentID,
cmpnt.CMPNT_Name,
av.AV_ItemID,
av.AV_Value,
av.AV_ValueText,
av.AV_RelatedItemID,
a.AS_ID,
a.AS_Name,
a.AS_AppID,
a.AS_SerialNumber,
a.AS_Tag
/* Add or change selected columns as necessary. */
From
dbo.AttributeValues av With (NOLOCK)
Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
Left Outer Join dbo.Assets a With (NOLOCK) On av.AV_RelatedItemID = a.AS_ID
Where
att.Att_BEID = @BEID
And av.AV_RelatedItemComponentID = 27
/* To filter for all saved values for a specific attribute ID, use the below line. */
And av.AV_ATTID = ATTRIBUTE_ID_HERE
/* To filter to all saved value for a specific item, use the below lines.
Refer to the Components table for valid values. For example, use 9 for tickets.
Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
-- And av.AV_ItemComponentID = 27
-- And av.AV_ItemID = ITEM_ID_HERE
Configuration Item Attributes
Select
Top 10
av.AV_ID,
av.AV_ATTID,
av.AV_ItemComponentID,
cmpnt.CMPNT_Name,
av.AV_ItemID,
av.AV_Value,
av.AV_ValueText,
av.AV_RelatedItemID,
ci.CI_ID,
ci.CI_Name,
ci.CI_AppID,
ci.CI_Description,
ci.CI_ExternalID
/* Add or change selected columns as necessary. */
From
dbo.AttributeValues av With (NOLOCK)
Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
Left Outer Join dbo.ConfigurationItems ci With (NOLOCK) On av.AV_RelatedItemID = ci.CI_ID
Where
att.Att_BEID = @BEID
And av.AV_RelatedItemComponentID = 63
/* To filter for all saved values for a specific attribute ID, use the below line. */
And av.AV_ATTID = ATTRIBUTE_ID_HERE
/* To filter to all saved value for a specific item, use the below lines.
Refer to the Components table for valid values. For example, use 9 for tickets.
Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
-- And av.AV_ItemComponentID = 63
-- And av.AV_ItemID = ITEM_ID_HERE
Location Attributes
Select
Top 10
av.AV_ID,
av.AV_ATTID,
av.AV_ItemComponentID,
cmpnt.CMPNT_Name,
av.AV_ItemID,
av.AV_Value,
av.AV_ValueText,
av.AV_RelatedItemID,
l.Loc_ID,
l.Loc_Name,
l.Loc_Address,
l.Loc_City,
l.Loc_State,
l.Loc_Zip,
l.Loc_Country
/* Add or change selected columns as necessary. */
From
dbo.AttributeValues av With (NOLOCK)
Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
Left Outer Join dbo.Locations l With (NOLOCK) On av.AV_RelatedItemID = l.Loc_ID
Where
att.Att_BEID = @BEID
And av.AV_RelatedItemComponentID = 71
/* To filter for all saved values for a specific attribute ID, use the below line. */
And av.AV_ATTID = ATTRIBUTE_ID_HERE
/* To filter to all saved value for a specific item, use the below lines.
Refer to the Components table for valid values. For example, use 9 for tickets.
Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
-- And av.AV_ItemComponentID = 71
-- And av.AV_ItemID = ITEM_ID_HERE
Location and Room Attributes
Select
Top 10
av.AV_ID,
av.AV_ATTID,
av.AV_ItemComponentID,
cmpnt.CMPNT_Name,
av.AV_ItemID,
av.AV_Value,
av.AV_ValueText,
av.AV_RelatedItemID,
lr.LR_ID,
lr.LR_Name,
lr.LR_Description,
lr.LR_LocationID,
lr.LR_Floor,
lr.LR_Capacity
/* Add or change selected columns as necessary. */
From
dbo.AttributeValues av With (NOLOCK)
Inner Join dbo.Attributes att With (NOLOCK) On av.AV_ATTID = att.Att_ID
Inner Join dbo.Components cmpnt With (NOLOCK) On av.AV_ItemComponentID = cmpnt.CMPNT_ID
Left Outer Join dbo.LocationRooms lr With (NOLOCK) On av.AV_RelatedItemID = lr.LR_ID
Where
att.Att_BEID = @BEID
And av.AV_RelatedItemComponentID = 80
/* To filter for all saved values for a specific attribute ID, use the below line. */
And av.AV_ATTID = ATTRIBUTE_ID_HERE
/* To filter to all saved value for a specific item, use the below lines.
Refer to the Components table for valid values. For example, use 9 for tickets.
Use AV_ItemID to match the specific item's ID, such as a ticket ID. */
-- And av.AV_ItemComponentID = 80
-- And av.AV_ItemID = ITEM_ID_HERE
Joining Single Choice Custom Attributes to a Ticket Query
When starting a query with other types of base data tables, such as issues or projects, refer to the Components table for the appropriate AttributeValues.AV_ItemComponentID value to use in joins.
Select
tck.Tck_ID,
tck.Tck_AppID,
tck.Tck_Title,
tck.Tck_Description,
tck.Tck_TypeID,
tck.Tck_AccountID,
tck.Tck_StatusID,
tck.Tck_FormID,
tck.Tck_ServiceID,
tck.Tck_ServiceOfferingID,
/* Requestor UID */
tck.Tck_ContactUID,
tck.Tck_CreatedByUID,
tck.Tck_CreatedDate,
tck.Tck_LastModifiedByUID,
tck.Tck_LastModifiedDate,
tck.Tck_ClosedByUID,
tck.Tck_ClosedDate,
/* Custom Attribute 1 information */
av1.AV_ValueText As Attribute1Value,
/* Custom Attribute 2 information */
av2.AV_Value As Attribute2ChoiceID,
attc2.ATTC_Name As Attribute2ChoiceName
/* Add or change selected columns as necessary. */
From
dbo.Tickets tck With (NOLOCK)
/* Example Text-Based Custom Attribute 1 (text textbox) Join */
Left Outer Join dbo.AttributeValues av1 With (NOLOCK) On
av1.AV_ItemComponentID = 9
and av1.AV_ItemID = tck.Tck_ID
and av1.AV_ATTID = ATTRIBUTE_1_ID_HERE
/* Example Single Choice Custom Attribute 2 (dropdown) Join */
Left Outer Join dbo.AttributeValues av2 With (NOLOCK) On
av2.AV_ItemComponentID = 9
and av2.AV_ItemID = tck.Tck_ID
and av2.AV_ATTID = ATTRIBUTE_2_ID_HERE
Left Outer Join dbo.AttributeChoices attc2 With (NOLOCK) On
av.AV_ATTID = attc2.ATTC_ATTID
And av.AV_Value = attc2.ATTC_ID
Where
tck.Tck_BEIDInt = @BEIDInt
And tck.Tck_ID = TICKET_ID_HERE