• Replies 4 replies
  • Subscribers 337 subscribers
  • Views 199 views
  • Users 0 members are here
Related

Output elements for custom contribution fields?

Anne M Robichaux
$organization

Hello,

We use two of the custom fields in contributions to record membership card names. I'm trying to pull these into output elements and I'm stuck. I have a view set up as

[dbo].[LV_MEMBERSHIP_CARD] AS
select T_CONTRIBUTION.customer_no, custom_1, custom_2, expr_dt
from T_CONTRIBUTION
join TX_CUST_MEMBERSHIP
on T_CONTRIBUTION.customer_no=TX_CUST_MEMBERSHIP.customer_no

That I'm pointing my elements to, but I'm getting multiple lines per constituent and only when I use a filter on the expiration date do I even get the custom fields to fill in. My list has date parameters, but when I run it with this output set I'm getting every membership on the record. 

Has anyone else set something like this and have any advice?

Thanks!
Anne Robichaux
The Historic New Orleans Collection

  • $organization

    You can do something like this and it will tie to the Membership Totals by Level and Type report, but in looking at the details of the data I think there may be some issues.

    Select  cust_memb_no , memb_level  , memb_amt , expr_dt ,cust_type, CM.customer_no , sort_name  
    from TX_CUST_MEMBERSHIP as CM
    inner join T_CUSTOMER as C on CM.customer_no = c.customer_no
    where expr_dt between @rStartDate  and @rEndDate
    and current_status not in (8,9)
    and memb_org_no = @MemOrg
        and expr_dt = (Select max(expr_dt) From [dbo].tx_cust_membership s
                    Where s.customer_no = c.customer_no and s.memb_org_no = @MemOrg
                    and s.expr_dt between @rStartDate  and @rEndDate
                    and s.current_status not in (9,8))

    In theory this should return the same results but doesn't .

    Select * from
    (Select *, ROW_NUMBER () over (partition by Customer_no, Memb_level
    order by Expr_Dt desc, cust_memb_no desc ) as SeqNo
    from TX_CUST_MEMBERSHIP where
    current_status not in (8,9)
    and expr_dt between @rStartDate  and @rEndDate
    and memb_org_no = @MemOrg
    ) As M
    where SeqNo = 1

    One of the differences is that in the first query the same constituent shows up multiple times with the same membership level.

  • $organization in reply to Ronald Radford

    Thanks, Ronald! It seems like this one might be a bit out of my skill level and will call for a consulting request.

  • $organization

    Hi Anne! 

    There is a way to do this with some standard functionality. There are three standard output set element groups (TR_QUERY_ELEMENT_GROUP) that can include the custom fields: Contribution, Contribution - First, and Contribution - Most Recent. "Contribution" returns all contributions for a constituent unless you use a filter; the other two return only 1 row per constituent.

    To demonstrate, let's say you want to add an element for custom field 1 to the Contribution - Most Recent group. You would need to add a new row to TR_QUERY_ELEMENT with the following settings: 

    • Description: Custom 1 (or you can change this to match the name of the field) 
    • Group Id: Contribution - Most Recent
    • Data Select: !.custom_1
    • Single Row: Checked

    The Single Row checkbox should be checked for any elements in Contribution - First or Contribution - Most Recent, and it should be unchecked for elements in Contribution.

    Hope that helps! 

  • $organization in reply to Michael Flaherty-Wilcox

    Thank you, Michael! I thought there must be a way to do it that I was just missing.