Membership Lookup Tables
B
Benefit Type
Table Name: benefit_type
Used by several fields in the Membership Benefits lookup table, to describe and track the 'inventory' and value of a particular Benefit Type.
The following describes the information that is available in this lookup table, and the data fields which hold a copy of that information, or which use it in calculating other information. The complete table layout is available via the hyperlinks below.
Type (Value and Code) - These are defined by your institution as the name of the Benefit Type. This information is used by the Membership Benefit field, Benefit Type (table_ben).
Benefit Value - This field is used by the Value of a Single Benefit field in the Membership Benefits lookup table (table_vben).
Benefits Available - This field is used to track the 'starting inventory' of the Benefits of this type that may be made available to any Membership.
Used - This field is updated by the system as Benefit Level data rows are created that specify this Benefit Type. This allows you to track the number of Benefits of this type that have been assigned to any constituents holding any Membership.
Generate Quid Flag - This flag is used to signal the system that the creation of a Benefit Level row that specifies this Benefit Type should (or should not) also automatically generate an entry in the Quid Pro Quo lookup table, using the same Code, Name, and Amount as those shown in the Benefits Type lookup table. This flag is copied to the Membership Benefit Quid field (table_quid).
One Time - This flag is used to signal the system that the Benefit Type is given only one time.
This lookup table conforms to the Standard Lookup Table Layout with some additional columns.
M
Membership Benefits
Table Name: membership_benefits
Each entry (row) in the Membership Benefits lookup table is used to define a 'Benefit package' that may be offered to constituents on the basis of a particular Membership. When a Membership row is created for a constituent, the system will locate all of the Membership Benefit lookup table rows that are associated with that Membership Type and prompt the operator for the creation of Benefit Level data rows, that will also belong to the constituent. Each Benefit Level data row will be based on information from one of the associated Membership Benefit lookup table rows.
In addition to the standard fields that are part of every lookup table, the following information is available for use in the Membership Benefits lookup rows. The complete table layout is available via the hyperlinks below.
Type (Value and Code) - These are defined by your institution as the name of the Membership Benefit Type.
Benefit Type - This field is driven by the Benefit Type lookup table (table_code) to specify which Benefit Type is being used in this Membership Benefit Package.
Membership Type - This field is used to track the Membership Type to which this Membership Benefit package will be linked. When a Membership of this type is created for a constituent, all Membership Benefits which specify the same Membership Type will be invoked. This field is driven by the Membership Type lookup table.
Value of a Single Benefit - This field is the monetary value of a single instance of the Benefit that is used in this Membership Benefit package. This information is automatically taken from the corresponding field (table_rate) in the Benefit Type lookup table.
Number of Benefits - This field is a count of the instances of this Benefit that are expected for this Membership Benefit package. This information is transferred to the insert form for a Benefit Level data row, but the operator may override this number when special circumstances require.
Total Value of the Benefits - This field is the total monetary value of this Membership Benefit package. This is calculated by the system as the Value of a Single Benefit multiplied by the Number of Benefits.
Benefit - This field is used to indicate whether this Membership Benefit is Standard (S) or Optional (O).
Quid
This field is used to indicate whether the system should or should not
automatically generate a Quid Pro Quo row when a Membership Benefit of
this type is used in the creation of a Benefit Level data row for a constituent.
Solicitation (Code and Value) - These fields are used to track the code and name of the Solicitation (if any) with which this Membership Benefit is associated.
One Time - This field is used to signal to the system that the Membership Benefit package is to be given only one time.
When a Membership Benefit entry (the table_code) is selected for the Membership Benefit field in a Benefit Level data row (benlmemben), the following lookup fields are also copied to the Benefit Level data row.
|
Membership Publication lookup table columns |
Copied to Benefit Level data table columns |
Notes |
|---|---|---|
|
table_pub |
benltype |
|
|
table_vben |
benlvalue |
|
|
table_nben |
benlnumber |
This data is defaulted into the data entry form but it may be overridden during data entry. |
|
table_tben |
benltvalue |
This data is defaulted into the data entry form but if the benlnumber data is overridden, this field will also be re-calculated. |
|
table_quid |
benlquid |
|
This lookup table conforms to the Standard Lookup Table Layout with several additional columns.
Membership Classification
Table Name: membership_classification
This lookup table is used by the Membership Classification field in the Membership Type table to categorize a Membership.
Required Entries
| Code | Value |
|---|---|
|
life |
Life |
|
single |
Single |
|
family |
Family |
|
joint |
Joint |
|
jtlife |
Joint Life |
|
jtfam |
Joint Family |
This lookup table conforms to the Standard Lookup Table Layout.
Membership Discount
Table Name: membership_discount
If a transaction is entered in the Membership system and if an entry from the Membership Discount lookup table is chosen for the transaction, the Code (table_code) from the lookup table is copied to the Membership Discount Code (giftdiscod or duesdiscod) and the Amount (table_amt1) from the lookup table is copied to the transaction's Discount Amount (giftdiscnt or duesdiscnt).
When this information is available, the system will include this amount along with the transaction amount in calculating the 'Best Fit' Membership.
This lookup table conforms to the Standard Lookup Table Layout.
Membership Publications
Table Name: membership_publications
Each entry (row) in the Membership Publications lookup table defines a Subscription 'package' that may be offered to constituents on the basis of a particular Membership. When a Membership row is created for a constituent, the system will locate all of the Membership Publication lookup table rows that are associated with that Membership Type and prompt the operator for the creation of Subscription Level data rows, also belonging to the constituent. Each Subscription Level data row will be based on information from one of the associated Membership Publication lookup table rows.
In addition to the standard fields that are part of every lookup table, the following information is available for use in the Membership Publication lookup rows. The complete table layout is available via the hyperlinks below.
Type (Value and Code) - These are defined by your institution as the name of the Membership Publication Type.
Publication Name - This field is driven by the Publication Name lookup table (table_code) to specify which Publication is being used in this Membership Publication Package.
Membership Type - This field is used to track the Membership Type to which this Membership Publication package will be linked. When a Membership of this type is created for a constituent, all Membership Publications which specify the same Membership Type will be invoked. This field is driven by the Membership Type lookup table.
Value of a Single Subscription - This field is the monetary value of a single instance of the Publication that is used in this Membership Publication package. This information is automatically taken from the corresponding field (table_rate) in the Publication Name lookup table.
Number of Subscriptions - This field is a count of the Subscriptions that are expected for this Membership Publication package. This information is transferred to the insert form for a Subscription Level data row, but the operator may override this number when special circumstances require.
Total Value of the Subscriptions - This field is the total monetary value of this Membership Publication package. This is calculated by the system as the Value of a Single Subscription multiplied by the Number of Subscriptions.
Standard/Optional - This field is used to indicate whether this Membership Subscription is Standard (S) or Optional (O).
Solicitation (Code and Value) - These fields are used to track the code and name of the Solicitation (if any) with which this Membership Subscription is associated.
One Time - This field is used to signal to the system that the Membership Publication package is given only one time.
When a Membership Publication entry (the table_code) is selected for the Membership Publication field in a Subscription Level data row (sublmempub), the following lookup fields are also copied to the Subscription Level data row.
|
Membership Publication lookup table columns: |
Copied to Subscription Level data table columns: |
Notes: |
|---|---|---|
|
table_pub |
subltype |
|
|
table_vsub |
sublvalue |
|
|
table_nsub |
sublnumber |
This data is defaulted into the data entry form but it may be overridden during data entry. |
|
table_tsub |
subltvalue |
This data is defaulted into the data entry form but if the sublnumber data is overridden, this field will also be re-calculated. |
This lookup table conforms to the Standard Lookup Table Layout with several additional columns.
Membership Status
Table Name: membership_status
Used by the Status field in the Membership table to describe the current status of the Membership in relationship to the constituent's Membership history.
During the Automated Membership Process, the system will default to a Status that is in keeping with the Membership Level of the Membership Type, as compared to existing Memberships for that constituent, as part of the Best Fit process. The operator may override that default for the Status and choose another Status entry, if desired.
Required Entries
Because the system uses these codes in the Automated Membership Process, editing the codes for the following entries may disable some of the software functionality. If additions are made to this lookup table, it must be done with the understanding that non-standard entries will not be recognized for any automatic process.
| Code | Value |
|---|---|
|
act |
Active |
|
dwng |
Downgrade |
|
iact |
Inactive |
|
lap |
Lapsed |
|
new |
New |
|
renew |
Renewed |
|
rj |
Re-join |
|
rjd |
Re-join Downgrade |
|
rju |
Re-join Upgrade |
|
upg |
Upgrade |
This lookup table conforms to the Standard Lookup Table Layout.
Membership Type
Table Name: membership_type
The Membership Type lookup table is used to define the nature of each Membership that is offered by the institution. This includes such information as the qualifying donation or dues amounts, the duration and timing of the Membership, the structure and hierarchy, the number of Membership Cards, the existence of standard or optional Subscriptions or Subscriptions, etc. The details are listed below.
Type (Value and Code) - These are defined by your institution as the name and code for this Membership.
Structure - This field may be used to 'flag' Membership Types as Primary or Secondary. Your institution might choose to use these designations (for example) to differentiate between national memberships and regional, chapter or departmental memberships. Such designations do not affect the Automated Membership functionality.
Maximum Amount - If the Membership fee for this Membership is associated with a range of possible Gift or Dues amounts, this field may be used to track the maximum amount of that fee.
Minimum Amount - If the Membership fee for this Membership is associated with a range of possible Gift or Dues amounts, this field may be used to track the minimum amount of that fee.
Deductible - This may be used to designate the portion of the Membership fee that may be considered deductible.
Classification - This field (which is driven by the Membership Classification lookup table) is used to categorize the Membership Type in a standardized way.
Duration - This field may be used to track the number of months that a Membership of this type is valid.
Lapse Period - This field may be used to track the number of months past the expiration date for the Membership that the institution will continue to grant the Subscriptions for a Membership of this type.
Rejoin - This field may be used to track the number of months during which this Membership can be renewed, and after which the constituent must re-join a Membership of this type. This field will default to the Duration plus the Lapse Period, but you may overwrite that data to define this time period as desired.
Renewal Set-Up - This field may be used to track the 'rules' for determining the Renewal date for a Membership of this type. The choices are:
- RF - Rolling Membership, First of Month
- RE - Rolling Membership, End of Month
- RS - Rolling Membership, Same Day
- RB - Rolling Membership, End of Month Backward
- RW - Rolling Membership, End of Month Forward
- CF - Calendar Year Membership, First of Year
- CE - Calendar Year Membership, End of Year
- FE - Fiscal Year Membership, End of Year
The default for this field is RF.
Renewal Day - If the Renewal Set-Up indicates that this is an RF, RB, or RW Membership, this field may be used to indicate the day of the month on which the Renewal Date should 'roll forward' to the first of the next month, the end of the prior month, or the end of the following month. Some examples are shown in the chart below, based on a Membership with a Duration of twelve months.
|
Set-Up |
Renewal Day |
Effective Date of Transaction (membrnewdt) |
Expiration Date |
|---|---|---|---|
|
RF |
20 |
10/22/2014 |
11/01/2015 |
|
RF |
20 |
10/19/2014 |
10/01/2015 |
|
RB |
20 |
10/22/2014 |
10/31/2015 |
|
RB |
20 |
10/19/2014 |
09/30/2015 |
|
RW |
20 |
10/22/2014 |
11/30/2015 |
|
RW |
20 |
10/19/2014 |
10/31/2015 |
Membership Cards - This field may be used to track the number of Membership Cards that a Membership of this type offers.
Level - This field may be used to assign a unique, numeric value that may be used to rank or create a hierarchy of Membership Types at the institution.
Established - This field is used to track the date on which this Membership Type was established at the Institution.
Closed Date - This field is used to track the date on which this Membership Type was closed at the Institution.
Include Match - This field is used to signal the system that when considering this Membership Type in the calculation of the 'Best Fit' Membership in the Automated Membership Process, that matching gift monies that are linked to the transaction should or should not be included.
Activation -This field is used to indicate the timing of the activation of a Membership of this type. The choices are:
- 1 - Membership is activated when the Pledge is effective.
- 2 - Membership is activated when the first Pledge Payment is received.
- 3 - Membership is activated when the final Pledge Payment is received.
Beginning Fiscal Year -This field may be used to track the timing of the fiscal year for this Membership; in what month does the fiscal year begin?
Lifetime - This field is used to signal to the system that this Membership Type is a lifetime Membership.
When a Membership data row is created for a constituent, much of the information is taken from the Membership Type lookup entry that is chosen either by the Automated 'Best Fit' Membership process, or by data entry override.
This lookup table does not conform to the Standard Lookup Table Layout. See Membership Type Layout.
P
Publication Name
Table Name: publication_name
Used by several fields in the Membership Publication lookup table, to describe and track the 'inventory' and value of a particular Publication.
The following describes the information that is available in this lookup table, and the data fields which hold a copy of that information, or which use it in calculating other information. The complete table layout is available via the hyperlinks below.
Type (Value and Code) - These are defined by your institution as the name of the Publication.
Publication Value - This field is used to track the monetary value of a single Subscription or an issue. This data is used by the Single Value field in the Membership Publication lookup table.
Total Number of Subscriptions Available - This field is used to track the institution's 'starting inventory' of this Publication which may be made available to any Membership Type.
Total Number of Subscriptions Used - This field is updated by the system as Subscription Level data rows are created that specify this Publication.
One Time - This field is used to signal to the system that this Publication is to be given only one time.
This lookup table conforms to the Standard Lookup Table Layout with a few additional columns.