Table: ItemFileLinks

Stores relationships between Vault Items and their associated files, including file attachments.

Columns

Column	Data Type	Can Import	Allows Null	Default	Description
🔑ParentItemNumber	nvarchar(128)	✅ Required	❌		Number of the parent Item. References Items→ItemNumber.
🔑ParentVersion	int	✅	❌	1	Exact version of the Vault Item. References Items→Version.
🔑TargetFilePath	nvarchar(256)	✅ Required	❌		Vault path of the associated file or attachment. References Files→FilePath.
🔑TargetVersion	int	✅	❌	1	Exact version of the linked file. References Files→Version.
🔑 LinkType	varchar(25)	✅ Required	✅		Possible File Link Types: • Primary — Link to main CAD file. • PrimarySubcomponent — Link to a virtual component or model state in the primary linked file. • Secondary — Additional CAD files are linked as a secondary references. • SecondarySubcomponent — Item assigned to a virtual component or model state in the secondary linked file. • Tertiary — Used for design document files. • StandardComponent • StandardSubcomponent — Used when Assign Item or Update Item is configured to create StandardComponent links for virtual components or model states. • Attachment • PinnedAttachment — Attached file locked to a specific version (Item always points to this version).
_OriginalBCPOverflow	xml	⚪ auto (only available when importing from a source Vault BCP package)	✅		Stores additional XML data from the original BCP file that is not captured by other columns (e.g. itemToComp node with IsManual= attribute, as well as <component= node with all its attributes - allows multiple components with different UIDs for sub-components or model-states).

Remarks

Each record defines a relationship between a Vault Item and a File, identified by the combination of ParentItemNumber (with ParentVersion), TargetFilePath (with TargetVersion) and its LinkType.
These columns together form the primary key of this table.

In Vault, a single parent-Item version cannot reference multiple versions of the same file (except for subcomponent links).
In addition, associations to the same file are possible using different LinkTypes.

Examples

Retrieving all primary- and secondary- file links of a specific Item version

SELECT *
FROM ItemFileLinks
WHERE ParentItemNumber = '100005'
  AND ParentVersion = 3
WHERE LinkType LIKE 'Primary%'
   OR LinkType LIKE 'Secondary%'

ParentItemNumber  ParentVersion  TargetTargetFilePath                        TargetVersion  LinkType
----------------  -------------  ------------------------------------------  -------------  -------------
100005            1              $/Designs/Padlock/Pad Lock.iam              3              Primary
100005            1              $/Designs/Padlock/Pad Lock.iam              1              Secondary

Updating all missing LinkTypes based on the linked file’s extension

-- Set first of multiple model links as 'Primary' and all additional model files as 'Secondary'
UPDATE ItemFileLinks
SET LinkType = CASE 
                  WHEN ROW_NUMBER() OVER (PARTITION BY ParentItemNumber, ParentVersion ORDER BY TargetFilePath) = 1
                  THEN 'Primary' ELSE 'Secondary'
               END
FROM ItemFileLinks
WHERE LinkType IS NULL
  AND Lower(TargetFilePath) LIKE '%.ipt'
   OR Lower(TargetFilePath) LIKE '%.iam';

-- Set all linked design documents as 'Tertiary'
UPDATE ItemFileLinks
SET LinkType = 'Tertiary'
WHERE LinkType IS NULL
  AND Lower(TargetFilePath) LIKE '%.idw'
   OR Lower(TargetFilePath) LIKE '%.ipn'
   OR Lower(TargetFilePath) LIKE '%.dwg';