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

Any SQL or other way to mass update a constituent with relationships?

Luis Castro
$organization

Hello, looking for a way other orgs may be going about mass updating/populating relationships for a constituent record. We have several of these situations for a board member sends us a list of their known associates and it would be great to be able to plug something in that does most of the work for us. Another scenario would be when/if we receive a list of employees for a particular company. How is everyone currently handling those situations, is the only route to go going manually for each one? Or is there a sql procedure/code someone likes to use? 

Thanks for any and all thoughts!

  • $organization

    I'm interested in hearing from the hive mind, and you might want to post this in the DBA group or raise in in tomorrow's meetup.

    You can update T_AFFILIATION like 

    BEGIN TRANSACTION

INSERT into T_AFFILIATION (affiliation_no ,individual_customer_no, group_customer_no, affiliation_type_id, is_allowed_to_transact, is_included_in_search_results, inactive)
VALUES 
(12346, XXXXXX, YYYYYY, 10007, 'N', 'N', 'N'), 
(12347, XXXXXX, YYYYYY, 10007, 'N', 'N', 'N')

UPDATE T_NEXT_ID
SET next_id = (select MAX(affiliation_no)+1 from T_AFFILIATION)
WHERE type = 'RP'

ROLLBACK TRANSACTION -- COMMIT TRANSACTION when satisfied

    Where group_customer_no is the org. 

    T_ASSOCIATION is similar but different.

    T_NEXT_ID is assuming v15.X 

    NB/

    1. Add in your favourite change catching SQL
    2. Do it in TEST SSMS first
    3. try to add an affiliation & association in the client after (if it kicks up it could be the T_NEXT_ID)
  • $organization in reply to Heath Wilder

    I'd recommend using the stored procedure dbo.AP_GET_NEXTID_function: although most are not, the point of the "next_id" columns is that they might be shared amongst multiple tables, so a max(id) of one table might not give you (or properly set) the actual next id.  As a bonus, if you use it your code will be v16 safe, as it becomes a wrapper for the sequences that replace T_NEXT_ID,

  • $organization in reply to Gawain Lavers

    Oh yes of course.  Affiliations and Associations share a relationship ID (in VS_RELATIONSHIP)

    (I really shouldn't be coding during a fire evacuation)

  • $organization

    I wrote a custom proc to create associations en masse. (Affiliations are more straightforward because you only need to create one side, but the proc could be tweaked to do that as well.) I'm happy to share it, just email me directly at catherine@artsphilly.org.

    It's a pretty generalized script so that I can use it in a variety of circumstances, but the idea is that you populate a staging table with one direction of the association, e.g. the board member's constituent ID, their associate's constituent ID, and the association type, and then the script does the rest. You could certainly tweak the script to work for more specific scenarios for whatever you're looking for.