Consecutive giving year query - doesn't work for fiscal years

Fernando Leite

Hello everyone, my name is Fernando and I'm a Software Engineer student who started working for my University a month ago (the reason I say this is because my knowledge with VBA is not that great so I might sound stupid for some experienced people haha) Anyways, I've been assigned this project where I need to find an easy way to create a Consecutive Giving Year report that contains the following fields: Const. Last name- Const full name - Solicitor full name - consecutive years - TY call/visit (this one is saying if the action of calling or visiting the constituent has been completed or not) My idea was to create 2 queries, one for the consecutive years that included all necessary fields and one for the actions. I would use the specific fields I need from each query and put them together with VBA. But recently I encountered a problem: the consecutive years field is sometimes wrong because it uses calendar year and not fiscal year like my boss wants. I know that when you do a report on Consecutive Years you can get the correct number that uses fiscal year as the criteria but in that case I don't have the fields I need and also I can't use the number in VBA (or maybe I can and I don't have the knowledge) If anyone has any idea on how to solve this problem, please help. I've been stuck for a while trying to solve this.

Helen Wieger

Fernando Leite:
Hello everyone, my name is Fernando and I'm a Software Engineer student who started working for my University a month ago (the reason I say this is because my knowledge with VBA is not that great so I might sound stupid for some experienced people haha) Anyways, I've been assigned this project where I need to find an easy way to create a Consecutive Giving Year report that contains the following fields: Const. Last name- Const full name - Solicitor full name - consecutive years - TY call/visit (this one is saying if the action of calling or visiting the constituent has been completed or not) My idea was to create 2 queries, one for the consecutive years that included all necessary fields and one for the actions. I would use the specific fields I need from each query and put them together with VBA. But recently I encountered a problem: the consecutive years field is sometimes wrong because it uses calendar year and not fiscal year like my boss wants. I know that when you do a report on Consecutive Years you can get the correct number that uses fiscal year as the criteria but in that case I don't have the fields I need and also I can't use the number in VBA (or maybe I can and I don't have the knowledge) If anyone has any idea on how to solve this problem, please help. I've been stuck for a while trying to solve this.

Hello Fernando.

I'm not particularly familiar with VBAs either, and I wouldn't use a VBA for this report. Instead I would export the information, then create a custom report in Crystal Reports.

If you can use Crystal, use a Constituent Export to get the data needed. You can narrow the group(s) to export using Query. Unfortunately neither query nor export has "consecutive years by FY" as an option, so I would export gift information and use a formula in Crystal Reports to calculate the # of consecutive years.

If you don't use Crystal Reports, the only way I know of to export consecutive years by FY is to export the consecutive years report as a CSV (see Solution BB19053). It's a pretty messy CSV file, but you could use VBA to pull information from that export and from a CSV or Excel Constituent Export.

Fernando Leite

Helen Wieger:

Hello Fernando.

I'm not particularly familiar with VBAs either, and I wouldn't use a VBA for this report. Instead I would export the information, then create a custom report in Crystal Reports.

If you can use Crystal, use a Constituent Export to get the data needed. You can narrow the group(s) to export using Query. Unfortunately neither query nor export has "consecutive years by FY" as an option, so I would export gift information and use a formula in Crystal Reports to calculate the # of consecutive years.

If you don't use Crystal Reports, the only way I know of to export consecutive years by FY is to export the consecutive years report as a CSV (see Solution BB19053). It's a pretty messy CSV file, but you could use VBA to pull information from that export and from a CSV or Excel Constituent Export.

Hello Helen, thank you for your answer. I'm not familiar with Crystal Reports but I do have that tool available. The only problem is that I don't think I will be able to do that TY call/visit with it I've been thinking about it and I thought of something: wouldn't I be able to get the consecutive giving information using each Constituent there? Because I can get a constituent record, go to Summaries -> Gift Summary, specify it to be fiscal year and I can get the number. Clearly the data is available on the database, I just need to figure out a way to use that in VBA, because that's the only part I'm missing. So maybe this will be a child object for a constituent that I can access and refer to. Another solution I was thinking could work is to create a consecutive giving year report, export to excel, and use those number from excel in my program. However, my knowledge in VBA is really not that great so I would have to research about it before I start doing it, and I want to make sure I'm going for the right direction. Also, I'm not sure how to make paragraphs on this forum. My text always looks very organized before I post it, but when I do it's all together.

Helen Wieger

Fernando Leite:
Hello Helen, thank you for your answer. I'm not familiar with Crystal Reports but I do have that tool available. The only problem is that I don't think I will be able to do that TY call/visit with it I've been thinking about it and I thought of something: wouldn't I be able to get the consecutive giving information using each Constituent there? Because I can get a constituent record, go to Summaries -> Gift Summary, specify it to be fiscal year and I can get the number. Clearly the data is available on the database, I just need to figure out a way to use that in VBA, because that's the only part I'm missing. So maybe this will be a child object for a constituent that I can access and refer to. Another solution I was thinking could work is to create a consecutive giving year report, export to excel, and use those number from excel in my program. However, my knowledge in VBA is really not that great so I would have to research about it before I start doing it, and I want to make sure I'm going for the right direction. Also, I'm not sure how to make paragraphs on this forum. My text always looks very organized before I post it, but when I do it's all together.

[quote user="Fernando Leite"] I've been thinking about it and I thought of something: wouldn't I be able to get the consecutive giving information using each Constituent there? Because I can get a constituent record, go to Summaries -> Gift Summary, specify it to be fiscal year and I can get the number.

....

Another solution I was thinking could work is to create a consecutive giving year report, export to excel, and use those number from excel in my program. However, my knowledge in VBA is really not that great so I would have to research about it before I start doing it, and I want to make sure I'm going for the right direction.

Also, I'm not sure how to make paragraphs on this forum. My text always looks very organized before I post it, but when I do it's all together.[/quote]

Fernando, a couple responses:

I don't think that the consecutive years is a number actually stored on the constituent record. My understanding of the Summaries in RE is that they are essentially mini-reports.

I would go with exporting the consecutive giving year report to excel.

As for formatting in this forum, I use basic html.