Posted on by host

Membership V2 Data Requirements

Table/Field requirements for Membership V2 – Data conversion

Information on data conversion for Membership V2.

Table/Field notes on what is required when performing an import to Gestalt. Critical fields in orange!

Data required from Client

  • Membership Types and Fees
  • Renewal frequencies, i.e. do they renew everyone on a single day of the year, or is it every month, etc
  • Do they ‘slide’ membership dates to start on (say) the first of each month, no matter what date you join? What ‘days of grace’ do they allow for someone joining towards the end of the month, if any?
  • List of members with:
    • Membership type
    • Frequency
    • Initial membership starting date (earliest date) if available – we need to substitute a Date if this is not provided
    • Current membership period start date
    • Expiry date of current membership (we can calculate from start date + freq)
    • Paid up to date

Gestalt Tables

Freq

Mostly this table is already set up OK in the standard ‘Empty’ version of Gestalt

  • FreqCode
  • FreqDescr – optional
  • Cycle – 3 Fixed values: Periodic; One off; Life
  • Every – how often per period
  • Period – Fixed values: Year(s); Month(s); Week(s); Life
  • SqlPeriod – similar to Period, but SQL versions: yy; mm; wk; Li

For example, to define a quarterly frequency you would set Cycle=Periodic, Every=3, Period=Month(s), SqlPeriod=mm

MbrType

  • MbrTypeCode
  • MbrTypeDescr – to describe the code, if needed
  • CampaignId – defaults to 12345678-1234… if not provided
  • SourceId – defaults to 12345678-1234… if not provided
  • SpecificUseId – defaults to 12345678-1234… if not provided

MbrFee

A fee belongs to a membership type

  • MbrTypeId – Foreign Key
  • EffectiveFromDate – Date that the fee comes into effect
  • FreqId – Foreign Key
  • BaseMbrAmt – Base amount of membership
  • GstPc – GST percentage
  • AmtInclGst – Total membership amount

There are a number of other permutations, for setting days of grace, but in data conversion these are not important and can be set via the interface.

Every MbrType record must have at least one Fee defined, and it is common to have several fees defined if multiple frequencies exist for a membership type.

ContactMbr

This is the ‘header record’ for the membership, and ‘controls’ the renewal process. A member generally only has one of these records, but if they have multiple memberships (e.g. Liberal Party – you can be a member of more than one branch) then each membership gets one of these records.

  • ContactId – Foreign Key
  • MbrTypeId – Foreign Key
  • FreqId – Foreign Key
  • MbrshipStartDate – The date they started their membership – Substitute a date if not known eg 01/01/2010
  • RenewalDueDate – The next date for the membership transaction when it is renewed
  • FinancialUpToDate – Paid up to date. Date of the last fully paid membership transaction.
  • CampaignId – defaults to 12345678-1234… if not provided
  • SourceId – defaults to 12345678-1234… if not provided
  • SpecificUseId – defaults to 12345678-1234… if not provided

FinTrx

This is the individual transaction of each membership. e.g. If someone has been an annual member for 4 years, they will have 4 transaction records. Whereas someone who has been a member for 2 years but is paying monthly will have 24 transaction records.

  • ContactId – Foreign Key
  • MbrTypeId – Foreign Key
  • FreqId – Foreign Key
  • ContactMbrId – Foreign Key
  • FinTrxTypeCode – Always Mbr
  • FinTrxSubTypeCode – Membership Type, for information only
  • FinTrxDate – Start of the membership period
  • RenewalDueDate – End of the period + 1 day
  • AmtInclGst – Membership amount
  • RenewalStatus – leftover from Mbr V1. Not needed in V2
  • PaidAmt – Set to the same as the AmtInclGST if the membership is paid

Import Procedure

  • Check that all Frequencies you need exist (they will probably already be there)
  • Add in membership types and for each type at least one fee
    • If not known, set the effective from date to be a few years in the past, like 1/Jan/2010.
  • For each member:
    • Create a ContactMbr ‘header’ record
      • This is the record that controls the renewal, so the next renewal date and the financial up to dates are important.
    • For each ContactMbr record
      • Create FinTrx records for each membership, although often it is only the latest record that needs to be created (if they only give us the current member list)
      • If they have a complete history of all membership transactions add them to FinTrx (this is not so common)