Grain: TYPE_REFERENCES — one row per valid code value, keyed on ENTITY + PROPERTY + VALUE.
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
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 |
| 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;
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.
