Using query and export to pull open proposals

Tracy Forkin · October 2016

Has anyone figured out a way around the duplicates when pulling the proposal solicitor? I need to pull open proposals (which I need to use query so I do not get closed ones) and include the solicitor(s) so, of course, I am getting duplicates. I was going to use the proposal import id to link the solicitor list from export with the query results but query does not offer the proposal import id as an option. What am I missing?

James Andrews · October 2016

Tracy Forkin:

Has anyone figured out a way around the duplicates when pulling the proposal solicitor? I need to pull open proposals (which I need to use query so I do not get closed ones) and include the solicitor(s) so, of course, I am getting duplicates. I was going to use the proposal import id to link the solicitor list from export with the query results but query does not offer the proposal import id as an option. What am I missing?

You're not missing anything. If our department is any indication, usage of the proposal records has skyrocketed in the last few years. People have been bugging blackbaud to make Proposal a top-level record for years now, but nothing doing.

So what you're missing is the knowledge of how to deal with it outside of RE. The easiest tool for this is Access -- you just go ahead and export all the proposals for the constituents, and you learn how to query for just the ones you want, and how to report on that information.

Tom Klimchak · October 2016

We also feel your pain with multiple solicitors on proposals... We "sort of" fixed it with our own solution: We made a rule that said every proposal can only have ONE solicitor... However, if they want to link a second solicitor they have an attribute called "Second Gift Officer" on the proposal record. That way you can export out your primary gift officer (solicitor field) as well as your secondary person.

Another option: If two solicitors worked together to secure a $50,000 gift, you could have each solicitor put in a $25k proposal... and then link both to the same gift. Raiser's Edge allows one gift to be recognized by multiple proposals (as long as you don't exceed the total gift amount). Again, by keeping each proposal tied to only ONE solicitor you can generall avoid duplicates.

Tracy Forkin:

Has anyone figured out a way around the duplicates when pulling the proposal solicitor? I need to pull open proposals (which I need to use query so I do not get closed ones) and include the solicitor(s) so, of course, I am getting duplicates. I was going to use the proposal import id to link the solicitor list from export with the query results but query does not offer the proposal import id as an option. What am I missing?

James Andrews · October 2016

Tom Klimchak:

We also feel your pain with multiple solicitors on proposals... We "sort of" fixed it with our own solution: We made a rule that said every proposal can only have ONE solicitor... However, if they want to link a second solicitor they have an attribute called "Second Gift Officer" on the proposal record. That way you can export out your primary gift officer (solicitor field) as well as your secondary person.

Tom, that's funny -- we went about it the opposite way. Every proposal can have multiple solicitors, but we have an attribute called "Proposal Primary" that is limited to one per record, and holds the solicitor's name and Constituent ID, like this: Andrews, James~~12345. That allows us to extract the ID easily and link it to other tables, and group/sort in reports.

Tracy Forkin · October 2016

Tracy Forkin:

Has anyone figured out a way around the duplicates when pulling the proposal solicitor? I need to pull open proposals (which I need to use query so I do not get closed ones) and include the solicitor(s) so, of course, I am getting duplicates. I was going to use the proposal import id to link the solicitor list from export with the query results but query does not offer the proposal import id as an option. What am I missing?

Thanks to everyone for you responses. By Friday afternoon, I gave up and exported the data and cleaned it up in Excel. Thank goodness for conditional formatting! We use the proposal module heavily for solicitations, ratings, classification (major gift v. annual fund), etc. It is only growing. I thought I had it figured out when using the proposal import id came to mind...but guess what...export gives you the option to select the proposal import id but query does not. This module and events (we use that a lot, also) really need to attention.

Brinda Goswami · October 2016

Tom Klimchak:

We also feel your pain with multiple solicitors on proposals... We "sort of" fixed it with our own solution: We made a rule that said every proposal can only have ONE solicitor... However, if they want to link a second solicitor they have an attribute called "Second Gift Officer" on the proposal record. That way you can export out your primary gift officer (solicitor field) as well as your secondary person.

Another option: If two solicitors worked together to secure a $50,000 gift, you could have each solicitor put in a $25k proposal... and then link both to the same gift. Raiser's Edge allows one gift to be recognized by multiple proposals (as long as you don't exceed the total gift amount). Again, by keeping each proposal tied to only ONE solicitor you can generall avoid duplicates.

Tracy Forkin:

Has anyone figured out a way around the duplicates when pulling the proposal solicitor? I need to pull open proposals (which I need to use query so I do not get closed ones) and include the solicitor(s) so, of course, I am getting duplicates. I was going to use the proposal import id to link the solicitor list from export with the query results but query does not offer the proposal import id as an option. What am I missing?

Glad to see this discussion. Some great suggestions. But how would you deal with proposals for multiple funds under same constituent. In that case you will need to create multiple proposals. Did you set-up some kind of rule for that?

Tracy Forkin · October 2016

Brinda Goswami:

Tom Klimchak:

We also feel your pain with multiple solicitors on proposals... We "sort of" fixed it with our own solution: We made a rule that said every proposal can only have ONE solicitor... However, if they want to link a second solicitor they have an attribute called "Second Gift Officer" on the proposal record. That way you can export out your primary gift officer (solicitor field) as well as your secondary person.

Another option: If two solicitors worked together to secure a $50,000 gift, you could have each solicitor put in a $25k proposal... and then link both to the same gift. Raiser's Edge allows one gift to be recognized by multiple proposals (as long as you don't exceed the total gift amount). Again, by keeping each proposal tied to only ONE solicitor you can generall avoid duplicates.

Tracy Forkin:

Has anyone figured out a way around the duplicates when pulling the proposal solicitor? I need to pull open proposals (which I need to use query so I do not get closed ones) and include the solicitor(s) so, of course, I am getting duplicates. I was going to use the proposal import id to link the solicitor list from export with the query results but query does not offer the proposal import id as an option. What am I missing?

Glad to see this discussion. Some great suggestions. But how would you deal with proposals for multiple funds under same constituent. In that case you will need to create multiple proposals. Did you set-up some kind of rule for that?

We have certain fields within a proposals that are required, funds are not one of them. Because a good number of ours are dual asks: annual gift and capital. This way, we can keep the details within the one proposal. We also link actions to the proposal. If we entered two proposals for every dual ask, it would be a lot more work.

Tammy Yugin · May 2017

Just wanted to bump this up to see if BB may have some updates or news of upcominf releases?

We run the constituent query based on the funder/proposal criteria. Then use concatenate and conditional formatting in Excel, to find duplicates for funder name and proposal name together, then manually move the canvassers for the duplicates are so they are all on one line, conctaenate those and remove the dupes

We are looking for all proposals in X status and want to incldue who's working on them.

Using Export doesn't work we as have fudners with more than one proposal.

Hope someone finds that useful. It would be ideal if the query function would let us have concatenated canvassers - I know I've seen this is in other areas.

Lauren Burgess · May 2017

Tracy Forkin:

Has anyone figured out a way around the duplicates when pulling the proposal solicitor? I need to pull open proposals (which I need to use query so I do not get closed ones) and include the solicitor(s) so, of course, I am getting duplicates. I was going to use the proposal import id to link the solicitor list from export with the query results but query does not offer the proposal import id as an option. What am I missing?

We had our Systems Team design a "report" outside of RE that's basically a giant spreadsheet. They included columns that let us easily sort/filter by Proposal Solicitor regardless of how many are assigned to a particular Proposal. It allows the option to just pull Development Officer X's active Proposals, or to pull all open Proposals on constituent A. We can also filter by any of the other fields on the General tab of the Proposal. It doesn't have much constituent info, however, other than name and ID.

Jason Rose 2 · November 2018

We are in a similar place and we would like to query of all our open proposals in rows, not columns.
I built a relationship query which does that but has lots of duplicates and I don’t want to go to excel if I don’t have to. I don’t see an export that links to relationships, does anyone have a suggestion?
What does your organization use for exporting proposals?

Debra Holcomb · November 2018

This are helpful tips. Thanks for sharing. I also had to add naming conventions to our proposals so I only pull the correct ones and can use proposals to track a pipeline. When a proposal is open, the name begins with 'Pending'. When a proposal is completed or closed, the name is appended and 'Pending' is replaced with 'YEAR Completed'. I know there has to be a better way, but this is the only way I could figure out how to filter in a constituent query to develop useful pipeline reports.

Using query and export to pull open proposals

Comments

Categories