Determining if a Gift if Soft Credit in a Query

Douglas Pruiett 3

We want to design a query in The Raiser's Edge that returns gifts that meet certain criteria. We find that the query returns both hard gifts and the "soft credit" gift associated (when there is a soft credit like with a spouse). We want to exclude any soft credits. What should we use in the criteria to exclude soft credits?


P.S. We are actually taking the SQL code created by the RE query, modifying it and using it in an external process.  We are finding that soft credits as well as hard are appearing.  We need to suppress the soft credits.

JoAnn Strommen

What criteria do you have in your query? Constituent or gift query?  Usually a query will only pull SC unless you've set it to do so.  When in the query, go to the Tools menu > Query Options > Gift Processing tab and see what is checked for Credit Soft Credits to:

Is this set for Both?  Change it to donor.

 

Douglas Pruiett 3

JoAnn Strommen:

What criteria do you have in your query? Constituent or gift query?  Usually a query will only pull SC unless you've set it to do so.  When in the query, go to the Tools menu > Query Options > Gift Processing tab and see what is checked for Credit Soft Credits to:

Is this set for Both?  Change it to donor.

 

JoAnn:

We actually took the SQL from a gift query and modified it some and are using it in an external process.  The query is (it might make you say Yikes!):


SELECT        DISTINCT

            [AppealAttributes].[TEXT] as [HonoreeEmail],

            [Gift].[DTE],

            [Gift].[Amount],

            [Appeal].[ID],

            [Appeal].[APPEAL_ID],

            [Appeal].[DESCRIPTION],

            [GiftAttributes].[TEXT] AS [Gift Attributes Comment],

                      dbo.Query_ConstitName(4,[records].[LAST_NAME],[SearchName].[FIRST_NAME],

                [records].[MIDDLE_NAME],[records].[ORG_NAME],[RECORDS].[KEY_INDICATOR],[records].[CONSTITUENT_ID]) as [Name],

            [Address].[ADDRESS_BLOCK],

            [Address].[CITY],

            [Address].[STATE],

            [Address].[POST_CODE],

            [QConstitPhones].[NUM] AS 'Email Address',

            --

            -- Connect for Import

            --

            [Gift].[ID] as [GiftID],

            [flag].[ATTRIBUTETYPESID] as [GiftAttributesTypeID],

            [flag].[TABLEENTRIESID],

            [flag].[NotificationFlag],

            [Gift].[POST_STATUS]



--

-- The Gift

--

FROM        [Gift]

INNER JOIN    [GiftSplit] ON [GiftSplit].GiftId = [Gift].[ID]

INNER JOIN    [Fund] ON [Fund].[ID] = [GiftSplit].[FundID]

--

-- The Honoree

--

INNER JOIN    [Appeal] ON [Appeal].[ID] = [GiftSplit].[AppealID]

LEFT JOIN    [AppealAttributes] ON [AppealAttributes].[PARENTID] = [Appeal].[ID] AND [AppealAttributes].[ATTRIBUTETYPESID] = 263

LEFT JOIN    [GiftAttributes] ON [GiftAttributes].[PARENTID] = [Gift].[ID] AND [GiftAttributes].[ATTRIBUTETYPESID] = 205

--

-- The Donor

--

INNER JOIN    [Records] ON [Records].[ID] = [Gift].[CONSTIT_ID]

INNER JOIN    [SearchName] ON [SearchName].[RECORDS_ID] = [Records].[ID]

INNER JOIN    [Constit_Gifts] ON [Constit_Gifts].[GIFT_ID] = [GiftSplit].[GiftID]

INNER JOIN    [Constit_Address] ON [Constit_Address].[CONSTIT_ID] = [Constit_Gifts].[CONSTIT_ID] AND [Constit_Address].[PREFERRED] = - 1

INNER JOIN    [Address] ON [Address].[ID] = [Constit_Address].[ADDRESS_ID]

INNER JOIN    [CAPreferred] ON [CAPreferred].[CONSTIT_ID] = [Gift].[CONSTIT_ID]

LEFT JOIN        [QConstitPhones] ON [QConstitPhones].[CONSTITADDRESSID] = [CAPreferred].[ID] AND [QConstitPhones].[PHONETYPEID] = 17859

--

-- The Flag

--

INNER JOIN    (

            

                SELECT        [GiftAttributes].[PARENTID],

                            [GiftAttributes].[ATTRIBUTETYPESID],

                            [TABLEENTRIES].[TABLEENTRIESID],

                            [TABLEENTRIES].[LONGDESCRIPTION] as [NotificationFlag]


                FROM        [GiftAttributes]


                INNER JOIN    [TABLEENTRIES]

                            ON    [GiftAttributes].[TABLEENTRIESID] = [TABLEENTRIES].[TABLEENTRIESID]


                WHERE        [GiftAttributes].[ATTRIBUTETYPESID] = 267

            

            ) as [flag]

            on    [flag].[PARENTID] = [Gift].[ID] and [flag].[NotificationFlag] = 'No' --?? Yes ??


WHERE        [Fund].[FUND_ID] = 'F9LMCO'

AND            [Gift].[POST_STATUS] = 1

AND        [Appeal].[APPEAL_ID] = @AppealID