Lookup Values

Grain: TYPE_REFERENCES — one row per valid code value, keyed on ENTITY + PROPERTY + VALUE.

Many columns in the data model store numeric codes rather than text labels. TYPE_REFERENCES is the decode key — it maps each code to its display label.

Decode pattern

SELECT
    g.GIFT_KEY,
    g.AMOUNT,
    ref.DESCRIPTION AS GIFT_TYPE_LABEL
FROM [database].[schema].GIFTS g
LEFT JOIN [database].[schema].TYPE_REFERENCES ref
    ON ref.ENTITY    = 'Gift'
    AND ref.PROPERTY = 'GiftType'
    AND ref.VALUE    = g.GIFT_TYPE::VARCHAR
WHERE g.GIFT_IS_DELETED = FALSE

Join on all three columns — ENTITY, PROPERTY, and VALUE (cast to VARCHAR) — to decode any numeric code field. Always use a LEFT JOIN to retain rows where the code is null or not yet in the reference table. See Query overview for more examples.

Most commonly needed lookups

Gift — GiftType

The payment method recorded on the gift transaction.

Code	Label
0	Cash
1	Check
2	Credit
3	Electronic Funds Transfer
4	Non-cash
5	Stock
6	Other
7	Reversing Transaction
8	Cryptocoin
9	Qualified Charitable Distribution
10	Pledge
11	PayPal

Gift — TributeType

Code	Label
1	In Honor Of
2	In Memory Of

RecurringGift — Status

Code	Label
0	Past Due
1	Up-to-date
2	Cancelled
3	Fulfilled

RecurringGift — Frequency

Code	Label
0	Once
1	Weekly
2	Bimonthly
3	Monthly
4	Quarterly
5	Semiannually
6	Annually
7	Biennially
8	Daily
10	Custom

Pledge — Status

Code	Label
0	Past Due
1	Up-to-date
2	Cancelled
3	Fulfilled
4	Write-off

Pledge — Frequency

Code	Label
0	Once
1	Weekly
2	Bimonthly
3	Monthly
4	Quarterly
5	Semiannually
6	Annually
7	Biennially
8	Daily
10	Custom

Task — TaskStatus

Code	Label
0	Not Started
1	In Progress
2	Blocked
3	Completed
4	Dismissed
5	Voided

GiftAskStatus — GiftAskStatusType

Code	Label
0	Active
1	Declined
2	Closed

Contact — BaseContactType

Code	Label
0	Household
1	Foundation
2	Organization

All entities in TYPE_REFERENCES

Run this query to pull all current lookup values from your environment:

SELECT ENTITY, PROPERTY, VALUE::NUMBER AS CODE, DESCRIPTION AS DISPLAY_NAME
FROM [database].[schema].TYPE_REFERENCES
ORDER BY ENTITY, PROPERTY, CODE;

Entities present in this table: Contact, ContactActivity, ContactAddress, ContactMethod, ContactWealth, CustomField, CustomFieldGroup, Email, Gift, GiftAskStatus, Membership, Pledge, PlannedGift, RecurringGift, Segment, SentEmail, SentEmailEvent, Task, Tribute.

Data freshness: These tables sync continuously from Virtuous CRM. Typical lag is under 4 hours. To check when a record was last updated, inspect the SF__ROW_SYNCED_DATE_TIME_UTC column on any table.

Overview

Reference

Decode pattern

Most commonly needed lookups

Gift — GiftType

Gift — TributeType

RecurringGift — Status

RecurringGift — Frequency

Pledge — Status

Pledge — Frequency

Task — TaskStatus

GiftAskStatus — GiftAskStatusType

Contact — BaseContactType

All entities in TYPE_REFERENCES

​Decode pattern

​Most commonly needed lookups

​Gift — GiftType

​Gift — TributeType

​RecurringGift — Status

​RecurringGift — Frequency

​Pledge — Status

​Pledge — Frequency

​Task — TaskStatus

​GiftAskStatus — GiftAskStatusType

​Contact — BaseContactType

​All entities in TYPE_REFERENCES

Decode pattern

Most commonly needed lookups

Gift — GiftType

Gift — TributeType

RecurringGift — Status

RecurringGift — Frequency

Pledge — Status

Pledge — Frequency

Task — TaskStatus

GiftAskStatus — GiftAskStatusType

Contact — BaseContactType

All entities in TYPE_REFERENCES