• Replies 17 replies
  • Subscribers 338 subscribers
  • Views 413 views
  • Users 0 members are here
Related

Contribution Role

Jan LaRocque
$organization

I see there is a Role in the Contribution records now, but I cannot find where that is stored.   I would especially like to use the Cr role to pull up Soft Credit contributions.   Where is that coming from?  

Jan LaRocque

Houston Symphony

  • $organization

    Really?   No one know where this comes from?   Where this code is stored?

    Jan LaRocque

    Houston Symphony

  • $organization in reply to Jan LaRocque

    The data you see in the Contributions tab appears to be generated by the sproc AP_CONTRIBUTIONS, which has this parameter:

    @included_roles varchar(10) = 'O,I,C' -- O - Owner, I - Initiator, C - Creditee

    So, you won't find the literal "O", "Cr", etc., in T_CONTRIBUTION. Rather, I suspect the client displays one or the other based on the output of the above sproc.

  • $organization in reply to Jan LaRocque

    Hi Jan,

    There is a column for role in VS_CONTRIBUTION_WITH_INITIATOR - does that help?

  • $organization in reply to Chris Jensen

    Oh, that's too bad that I can't just pull the "Cr" role.  That would make my life so much easier.

    Thank you for your reply, Chris.

    Jan LaRocque

  • $organization in reply to Sheela Sur-Luk

    Thanks, Sheela.   Yes, it does help.

    Jan LaRocque

  • $organization in reply to Jan LaRocque

    So, there is no table to tell you what the numbers 2, 5, 7 in T_ORDER or to define O, Cr, etc?   I'd like to know what they mean.  I figured them out quite by accident.

    Jan

  • $organization in reply to Jan LaRocque

    I figured them out for the ticket history screen by literally scrolling through the procedure that updates the ticket history.  I am not sure how these translate for contributions/creditees, but one would assume that it would be similar, no?

    1. Owner (O)
    2. Initiator (I)
    3. Owner/Initiator (OI)
    4. Recipient (R)
    5. Owner/Recipient (OR)
    6. Initiator/Recipient (IR)
    7. Owner/Initiator/Recipient (OIR)

    Also, I am not sure that that data is actually stored anywhere on the T_ORDER table (at least, I cannot find it there), though that information IS displayed on the customer screen on the Orders Tab, so there is a process using it somewhere.  It IS on embedded into the T_TICKET_HISTORY table as the Role column, which makes sense.  The Orders table does not need it directly, I suppose, since the customer number lives on the order itself.

  • $organization in reply to John A. Moskal II

    John, I can't thank you enough for this information.   Sure would be nice if there were a table with these descriptions in it, wouldn't it.   (Hint, hint, Tessitura).

    This page will be printed and prominently displayed at my desk.

    Thanks,

    Jan

  • $organization
    Documentation provides the following
    Transaction Roles and Creditees
    Contributions and ticket orders have an owner and an initiator. The owner is the constituent to which the contribution or order belongs and the initiator is a field to record the ID of the constituent who initiated the transaction but doesn't own the contribution or order. This is intended primarily for use with groups, households, and organizations, so that you can save the contribution or order to the group while also tracking which individual in the group initiated the transaction. For example, when the wife in a household calls to make a contribution, the household record can be set as the owner of the contribution and the wife can be recorded as the initiator so you can know which individual in the household is making contribution decisions. Or when a teacher places a ticket order for a school group, the school can be set as the owner of the order so that you know which schools are sending groups and the teacher can be set as the initiator so that you can track which teacher is leading the group.
    Ticket orders have a third role in which constituents can be recorded, recipient. Recipients are set at the sub line item level to track the user of individual tickets in an order. This can be helpful in situations such as when friends share a set of subscription seats from a single order, when you receive the names of the individuals in a group order, recording the individual recipients of comp tickets provided to a corporate sponsor, or to record the children taking classes in an education order owned by the household.
    Additionally, if a constituent is the creditee on a contribution, Cr will be listed as the role for that contribution.
    When building criteria sets for lists or extraction set segments, role criteria should generally be used with ticket history or contribution history criteria. There are three standard role criteria available:
    Ticket History Const Role – The role for a ticket history entry. There are values for the three possible roles a constituent can have in an order (owner, initiator, and recipient), and four additional values for all the possible role combinations if the constituent holds multiple roles in the order. When using this criterion, if you want to find all constituents who hold a particular role, select that single role value and the combination values that include that role. For example, if you want to find all constituents who are the owner for a ticket purchase, select the Owner, Owner/Initiator, Owner/Recipient, and Owner/Initiator/Recipient values.
    Package History Const Role – The role for a package history entry. There are values for the three possible roles a constituent can have in an order (owner, initiator, and recipient), and four additional values for all the possible role combinations if the constituent holds multiple roles in the order. When using this criterion, if you want to find all constituents who hold a particular role, select that single role value and the combination values that include that roll. For example, if you want to find all constituents who are the owner for a ticket purchase, select the Owner, Owner/Initiator, Owner/Recipient, and Owner/Initiator/Recipient values.
    Contribution Constituent Role – The role for a contribution history entry. There are values for owner, initiator, a combination of the two roles (for when a constituent holds multiple roles in the contribution). When using this criterion, if you want to find all constituents who hold a particular role, select that single role value and the combination value. For example, if you want to find all constituents who are the owner for a contribution, select the Owner and Owner/Initiator values. There is also a Creditee value, for when the constituent is a creditee on a soft credited contribution.
    Note: If you do not use the Contribution Constituent Role criterion in conjunction with other contribution criterion, constituents with qualifying soft credit contributions will be included in the results automatically. If you want to exclude soft credits, use the Contribution Constituent Role criterion and do not select the Creditee value.
    Note: When using role criteria in conjunction with corresponding transaction history criteria, be sure to use the IN operator.
     
     
  • $organization in reply to Leslie McKinley

    I know this part.   What I could not find was a translation for the numbering system;  1 = Owner; 2 = Initiator;, etc.   Or that they can be combined into multiple codes  7 = OIR.   

    Jan

  • $organization in reply to John A. Moskal II
    John A. Moskal II said:
    I am not sure how these translate for contributions/creditees

    Those values don't translate directly to SCs, which are stored in a separate table, i.e. T_CREDITEE, which is usually joined to T_CONTRIBUTION something like:

    select top 10 c.* 
	,cr.creditee_no
from T_CONTRIBUTION c
left join T_CREDITEE cr
	on c.ref_no = cr.ref_no
  • $organization in reply to Jan LaRocque
    Jan LaRocque said:
    What I could not find was a translation for the numbering system; 

    From VS_ELEMENTS_TICKET_HISTORY:

    CASE T.Role
WHEN 1 THEN 'O'
	WHEN 2 THEN 'I'
	WHEN 3 THEN 'OI'
	WHEN 4 THEN 'R'
	WHEN 5 THEN 'OR'
	WHEN 6 THEN 'IR'
	WHEN 7 THEN 'OIR'
END AS role_desc,
  • $organization in reply to Chris Jensen

    Which is to say that it's a binary switch:

    1 = 'O'

    10 (2) = 'I'

    100 (4) = 'R'

    And the rest is addition...

  • $organization in reply to Gawain Lavers

    Look at you, sneaking math into this.  I love it!

  • $organization

    Another resource where those ticket role IDs and definitions are explicitly spelled out is the Tessitura Table Structures document: https://www.tessituranetwork.com/en/Files/Docs/SystemAdmin/Tessitura-Table-Structures-V14

    This doc doesn't cover every single table, but it does include a breakdown of column names, datatypes, and primary/foreign keys for most of the heavy hitters you'll regularly deal with. I've found it helpful as an alternative to diving into SSMS for quick structural questions.