FundSvcs Community

Expand all | Collapse all

Raiser's Edge Pledge Table(s)

  • 1.  Raiser's Edge Pledge Table(s)

    Posted 05-17-2021 03:14 PM
    Happy Monday,

    I have a bit of a quandary I'm hoping an Raiser's Edge expert might be able to assist with. We acquired a local hospital system about two years ago, and we haven't yet integrated all of their legacy Raiser's Edge data into Advance although we no longer have access to RE as a program (I have all the data backed up). Unfortunately I need to run some queries on the data, and I cannot for the life of me locate the right table(s) for pledge balance information. I can find the gift table, and there seems to be some transactions labeled "Pledge", but they also seem to have gift characteristics associated with them and I don't see a balance field.

    Is there a wise soul out there who can point me to the right table to query open pledge balances? Thank you.

    -jsg

    ------------------------------
    Josh Greenbaum
    Executive Director, Advancement Information Svcs
    Emory University
    josh.greenbaum@emory.edu
    ------------------------------


  • 2.  RE: Raiser's Edge Pledge Table(s)

    Posted 05-18-2021 12:28 AM
    Hi Josh - Unfortunately, there isn't a single table that contains pledge balance.  It's a combination of GIFT, Installment_Payment and then calculations to subtract any write-offs.  Here's a bit of SQL that should do it (where you'll want to substitute [RE7] with the name of your restored database).

    Let me know if I can help.  (For better or worse, I have the intricacies of RE's labyrinth tables committed to memory. :-)

    Nadine
    www.betterverseconsulting.com
    nadine@betterverseconsulting.com

    select R.CONSTITUENT_ID
    ,R.LAST_NAME
    ,R.FIRST_NAME
    ,R.ORG_NAME
    ,PB.PLEDGE_AMOUNT
    ,PB.PLEDGE_AMOUNT_PAID
    ,PB.PLEDGE_BALANCE

    from [RE7].[dbo].[RECORDS] R

    left join

    (SELECT G.ID --calculates balance owed on pledges
    ,G.CONSTIT_ID
    ,CASE
    WHEN G.TYPE NOT IN (8,27)
    THEN 0
    ELSE MAX(G.Amount)
    END AS PLEDGE_AMOUNT
    ,CASE
    WHEN G.TYPE NOT IN (8,27)
    THEN 0
    ELSE COALESCE(SUM(IP.Amount),0)
    END AS PLEDGE_AMOUNT_PAID
    ,CASE
    WHEN G.TYPE NOT IN (8,27)
    THEN 0
    ELSE MAX(G.Amount) - COALESCE(SUM(IP.Amount),0)
    END AS PLEDGE_BALANCE
    from [RE7].[dbo].GIFT G 
    LEFT JOIN
    (select
    Installment_Payment.PaymentId AS PaymentId,
    Installment_Payment.PledgeId AS PledgeId,
    Installment_Payment.Amount AS Amount,
    Write_Offs.ID AS ID
    from [RE7].[dbo].InstallmentPayment Installment_Payment,
    [RE7].[dbo].GIFT Write_Offs
    where Write_Offs.TYPE = 21 and Installment_Payment.PaymentId = Write_Offs.ID) IP
    ON G.ID = IP.PledgeId
    GROUP BY G.ID,G.DTE,G.CONSTIT_ID,G.TYPE) PB

    on R.ID = PB.CONSTIT_ID
    where PB.PLEDGE_BALANCE >0

    ------------------------------
    Nadine Francis
    Principal
    Betterverse Consulting
    nadine@betterverseconsulting.com
    ------------------------------