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!
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/
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,
Oh yes of course. Affiliations and Associations share a relationship ID (in VS_RELATIONSHIP)
(I really shouldn't be coding during a fire evacuation)