Creating Query of Assigned Donors for Specific Solicitors

Rob Crenshaw

I'm wondering if anyone knows of a good method of crafting a query in RE to create a list of donors that are assigned to a group of specific solicitors?


I realized after our recent conversion to NXT that we have a boatload of defunct solicitors that are still flagged as active, and I'm hoping to do a global change to switch the solicitor type on their previously assigned donors from whatever they were to "former" before I uncheck the active solicitor flag. But it looks like query only allows you to search by one assigned solicitor at a time!


The only solution I can think of is to export ALL donors with an assigned solicitor, the solicitor ID, and relationship Import ID, and then change the solicitor type that way... but that feels really messy and prone to creating problems. Does anyone know of a better way?

Alan French

If you're using Specific Record to identify the solicitors then it looks like you have to add them one at a time, but if you've got a list of the names (and you've never had more than one solicitor with the same name) then I think you could paste up to 500 of those into the criteria "Assigned Canvasser name one of" in one go.

Larry Wheeler

I may be misunderstanding your exact needs.  If not, I would attempt to do the following:

  

If the number of solicitors which are known current solicitors is small enough to create a query by manually entering their id's (copy and paste const/specific record ID is one of...),  you could merge two queries -- ALL constituents which are solicitors SUB your known current solicitors. 

You would have a static query of non-current solicitors.  You can then gather their assigned donors using a constituent query with Assigned Solicitors = choose the created static query.


 

Elaine Tucker

I just did this this week   I did two exports, one from Admin-Import-create import file - Solicitors.  This gets you the Solicitor Relationship Import ID and the Solicitor Relationship LINK.

The LINK is important!  that file gives you the following fields:



I then exported out ALL constituents who had a solicitor - that gives you the following

Constituent ID    Sort Name    Last Name    First Name    Assigned Solicitor Name    Assigned Solicitor Date From    Assigned Solicitor Date To    Assigned Solicitor Type    Assigned Solicitor Specific Record    Assigned Solicitor Import ID    Assigned Solicitor Campaign ID


Link the two worksheets using the ASRIMPID, be sure to include the ASRLink and change the Solicitor Type to "former" 

Import back in.  


I found it really easy - hardest part was exporting and then linking the two files.  I did this in Access, but excell would probably work to using lookup

 

Joel Raasch

I do something similar to what Elaine Tucker shared. Basically export one or more solicitor's Assigned Solicitor relationships data from RE as csv so as to get relationship import IDs, etc. Then in csv output file, change the Assigned Solicitor relationship type from, say, "Primary" to "Past Primary" in all the rows of the csv, as appropriate, and in the blank "Date To" column insert, say, yesterday's date. Then import that modified data back into RE. Kind of putzy but not too tough once you get the hang of it.


Early on, years ago, I believe I tried Global Change but had problems.