Proposal System ID

Alec McKay 2

I am attempting to export out proposal information (via a Constituent Export) and gift information (via a Gift Export) and have a unique identifier that links proposal information to gift information where gifts are linked. I am creating a complex report to analzye solicitor's pipelines which needs both gift and proposal information.


On the proposal side, I have exported the proposal system record ID and Import ID (which have the same last four digits). On the gift side I have exported the Linked Proposals System Record ID. I would assume these would be the same identifiers but they do not match up. 


Anyone have a different method of exporting a unique identifier for linked gifts and proposals or insight into why these are different? 

Steven Cianciarulo

Alec McKay:

I am attempting to export out proposal information (via a Constituent Export) and gift information (via a Gift Export) and have a unique identifier that links proposal information to gift information where gifts are linked. I am creating a complex report to analzye solicitor's pipelines which needs both gift and proposal information.


On the proposal side, I have exported the proposal system record ID and Import ID (which have the same last four digits). On the gift side I have exported the Linked Proposals System Record ID. I would assume these would be the same identifiers but they do not match up. 


Anyone have a different method of exporting a unique identifier for linked gifts and proposals or insight into why these are different? 

I have attempted the same thing and never managed to get gifts and proposals to appear on the same report. The closest I got was working in Crystal with a direct ODBC connection into RE, but it was over my head how to get it all working. A lot of my job would be much, much easier if proposals had their own query/export type, but unfortunately I don't see this ever happening.

John Heizer

Alec McKay:

I am attempting to export out proposal information (via a Constituent Export) and gift information (via a Gift Export) and have a unique identifier that links proposal information to gift information where gifts are linked. I am creating a complex report to analzye solicitor's pipelines which needs both gift and proposal information.


On the proposal side, I have exported the proposal system record ID and Import ID (which have the same last four digits). On the gift side I have exported the Linked Proposals System Record ID. I would assume these would be the same identifiers but they do not match up. 


Anyone have a different method of exporting a unique identifier for linked gifts and proposals or insight into why these are different? 

You'll probably have to include ALL Gifts and ALL Proposals in the Constituent Export and then use CR to filter down to the Gifts and Proposals you want in the Report.  It's cumbersome and the Export can take a very long time, but that's one way that you can be certain to get all the data you need and preserve the links.

Joshua Bekerman bCLO bCRE

I think this or something similar (not sure if it was for proposals but it should still apply here) has been asked before and someone mentioned that they created a workaround for it.  If I remember correctly they would enter a proposal attribute that contained the Gift ID and they would export that and use it to link to the gift.  It's a little more data entry work but seems resasonable if it lets you report as needed.


 

Jen Claudy

I did what Josh mentioned at a previous job.  Or something similar.  I created a Proposal Attribute that was a Proposal ID...I think I used the ConstID plus "01" and then went sequentially for however many proposals they had (although the order wasn't really important, just that they are unique IDs).  I don't think I got to the point of entering that PropID on the relevant Gift Records, but I may have.  I tend to use MS Access over Crystal Reports, but I believe it's similar in theory, and this provided a way (beyond the text field for Proposal Name) to keep some semblance of organization to trying to report on proposals.

David Ritchie

Hi Alec,

To overcome these shortcomings with comparing data across multiple areas, we pull our data directly from RE using SQL. I am not sure if that is an area for consideration for you, but that would certainly allow you to connect all the data points you listed in a report. We populate our own custom ODS on a nightly basis, and that provides the data for us to build reporting as you are describing. We have multiple visualizations that show our staff and how they are performing this year, pipelines, etc.


You really do need to be careful of how you Join this data together. It is very easy to cause duplicate counting of data points depending on what data you join together and the rules that are applied within your RE for data entry.


Good luck with the project!

Cheers,

David

James Andrews

Alec McKay:

I am attempting to export out proposal information (via a Constituent Export) and gift information (via a Gift Export) and have a unique identifier that links proposal information to gift information where gifts are linked. I am creating a complex report to analzye solicitor's pipelines which needs both gift and proposal information.


On the proposal side, I have exported the proposal system record ID and Import ID (which have the same last four digits). On the gift side I have exported the Linked Proposals System Record ID. I would assume these would be the same identifiers but they do not match up. 


Anyone have a different method of exporting a unique identifier for linked gifts and proposals or insight into why these are different? 

I don't know what version you're on but we had this exact problem (we're on 7.92), and when I opened a case for it, she couldn't replicate it in 7.96, so as usual the answer was "upgrade and the problem will be solved."


The issue is that gifts and proposals are a many to many relationship, linked in the database via the table GiftProposal. For some reason the "Linked Proposals System Record ID" that exports is not the actual proposal ID, it's the ID from the row in the GiftProposal table that links the two.  If you can get access to the back end of the DB and pull that GiftProposal table into Access or whatever you're using for reporting, it'll work.

John Heizer

James Andrews:

 

Alec McKay:

I am attempting to export out proposal information (via a Constituent Export) and gift information (via a Gift Export) and have a unique identifier that links proposal information to gift information where gifts are linked. I am creating a complex report to analzye solicitor's pipelines which needs both gift and proposal information.


On the proposal side, I have exported the proposal system record ID and Import ID (which have the same last four digits). On the gift side I have exported the Linked Proposals System Record ID. I would assume these would be the same identifiers but they do not match up. 


Anyone have a different method of exporting a unique identifier for linked gifts and proposals or insight into why these are different? 

I don't know what version you're on but we had this exact problem (we're on 7.92), and when I opened a case for it, she couldn't replicate it in 7.96, so as usual the answer was "upgrade and the problem will be solved."


The issue is that gifts and proposals are a many to many relationship, linked in the database via the table GiftProposal. For some reason the "Linked Proposals System Record ID" that exports is not the actual proposal ID, it's the ID from the row in the GiftProposal table that links the two.  If you can get access to the back end of the DB and pull that GiftProposal table into Access or whatever you're using for reporting, it'll work.

 

I'm baffled why they couldn't replicate the problem in 7.96 unless 7.96 mysteriously removes the many-to-many relationship and required junction table.

James Andrews

John Heizer:

James Andrews:

 

Alec McKay:

I am attempting to export out proposal information (via a Constituent Export) and gift information (via a Gift Export) and have a unique identifier that links proposal information to gift information where gifts are linked. I am creating a complex report to analzye solicitor's pipelines which needs both gift and proposal information.


On the proposal side, I have exported the proposal system record ID and Import ID (which have the same last four digits). On the gift side I have exported the Linked Proposals System Record ID. I would assume these would be the same identifiers but they do not match up. 


Anyone have a different method of exporting a unique identifier for linked gifts and proposals or insight into why these are different? 

I don't know what version you're on but we had this exact problem (we're on 7.92), and when I opened a case for it, she couldn't replicate it in 7.96, so as usual the answer was "upgrade and the problem will be solved."


The issue is that gifts and proposals are a many to many relationship, linked in the database via the table GiftProposal. For some reason the "Linked Proposals System Record ID" that exports is not the actual proposal ID, it's the ID from the row in the GiftProposal table that links the two.  If you can get access to the back end of the DB and pull that GiftProposal table into Access or whatever you're using for reporting, it'll work.

 

I'm baffled why they couldn't replicate the problem in 7.96 unless 7.96 mysteriously removes the many-to-many relationship and required junction table.

 

Right? I don't know why they even include it as an option in Export, as if it's useful to anyone to have a link to a table you can't otherwise access. It's useless.

Alec McKay 2

James Andrews:

Alec McKay:

I am attempting to export out proposal information (via a Constituent Export) and gift information (via a Gift Export) and have a unique identifier that links proposal information to gift information where gifts are linked. I am creating a complex report to analzye solicitor's pipelines which needs both gift and proposal information.


On the proposal side, I have exported the proposal system record ID and Import ID (which have the same last four digits). On the gift side I have exported the Linked Proposals System Record ID. I would assume these would be the same identifiers but they do not match up. 


Anyone have a different method of exporting a unique identifier for linked gifts and proposals or insight into why these are different? 

I don't know what version you're on but we had this exact problem (we're on 7.92), and when I opened a case for it, she couldn't replicate it in 7.96, so as usual the answer was "upgrade and the problem will be solved."


The issue is that gifts and proposals are a many to many relationship, linked in the database via the table GiftProposal. For some reason the "Linked Proposals System Record ID" that exports is not the actual proposal ID, it's the ID from the row in the GiftProposal table that links the two.  If you can get access to the back end of the DB and pull that GiftProposal table into Access or whatever you're using for reporting, it'll work.

 

Thanks all for your answers and for the explanation of why the IDs are different. I was able to match it up in my Access reporting database based on Constituent ID and Proposal Name. Should be fine unless a constituent has duplicate proposals with the same name. We are running 7.93, I'm curious if this will change once we get to 7.96.