• Replies 2 replies
  • Subscribers 413 subscribers
  • Views 113 views
  • Users 0 members are here
Related

Constituent Headers

Michael Magnes
$organization

Hi everyone, 

We're trying to update our constituent headers 12 month giving to include pledges and pledge payments. Does anyone have experience with updating this? Is this possible?

Thanks!

Parents
  • $organization

    Hey Michael,

    Theoretically, in order to grab pledges and pledge payments, you'd probably want to be referencing the VS_TRANSACTION view instead of the VS_CONTRIBUTION view which is the default setting of 12 Month Giving in TR_FLEX_HEADER_ELEMENT from the System Tables.

    You could use the following query:

    Select sum(trn_amt) From [dbo].VS_TRANSACTION WITH (NOLOCK) Where customer_no = @customer_no and (trn_dt between dateadd(mm, -12, getdate()) and getdate())

    And that should pull any gifts, pledges, and pledge payments during the last 12 months of time.

    Note, however, that it might also grab transactions which are ticket orders or other purchases; you might want to add another qualifying WHERE statement at the end of that code where you could isolate either the trn_type value (which you can reference in TR_TRANSACTION_TYPE in your System Tables) or the tckt_tran_flag value, which looks like it could be used to suppress any transactions where that value would be "Y". Not sure if that would omit contributions made in ticket transactions though.

    Sorry for not having a 100% answer to help here, but hopefully this is a start.

    Thank you,

    Brian

Reply
  • $organization

    Hey Michael,

    Theoretically, in order to grab pledges and pledge payments, you'd probably want to be referencing the VS_TRANSACTION view instead of the VS_CONTRIBUTION view which is the default setting of 12 Month Giving in TR_FLEX_HEADER_ELEMENT from the System Tables.

    You could use the following query:

    Select sum(trn_amt) From [dbo].VS_TRANSACTION WITH (NOLOCK) Where customer_no = @customer_no and (trn_dt between dateadd(mm, -12, getdate()) and getdate())

    And that should pull any gifts, pledges, and pledge payments during the last 12 months of time.

    Note, however, that it might also grab transactions which are ticket orders or other purchases; you might want to add another qualifying WHERE statement at the end of that code where you could isolate either the trn_type value (which you can reference in TR_TRANSACTION_TYPE in your System Tables) or the tckt_tran_flag value, which looks like it could be used to suppress any transactions where that value would be "Y". Not sure if that would omit contributions made in ticket transactions though.

    Sorry for not having a 100% answer to help here, but hopefully this is a start.

    Thank you,

    Brian

Children