SQL Script to Retrieve IDs of Values in Values List Fields

[Kb-Sync]

Applies To

Product(s): Archer
Version(s): 6.x
Primary Deployment: On Premises/AWS Hosted/AWS SaaS

---

Description

The below SQL Script can be used to retrieve data (e.g., Value IDs) about Values List fields and its associated Values.

Resolution

Execute the below script against the Instance Database:

SELECT fd.field_id, ft.field_name, sv.select_value_id, vlvt.select_value_name, 
fd.guid field_guid, 
sd.guid value_list__guid, 
sv.guid value_guid
FROM tblIVFieldDef fd
JOIN tblFieldTranslation ft ON ft.field_id = fd.field_id
AND ft.is_default = 1
JOIN tblXIVSelectDefFieldDef sdfd ON sdfd.field_id = fd.field_id
JOIN tblIVSelectDef sd ON sdfd.select_id = sd.select_id
JOIN tblIVSelectValue sv ON sv.select_id = sd.select_id
JOIN dbo.tblValueListValueTranslation vlvt ON vlvt.select_value_id = sv.select_value_id
AND vlvt.is_default = 1
ORDER BY fd.field_id, vlvt.select_value_name;