Highlighted

Querying the Right Title per Corresponding Year

New Here ,
Sep 14, 2018

Copy link to clipboard

Copied

Hi,

I'm having trouble figuring the logic on how I can output this information.

Basically, I'm querying a yearly report from my database where some donors post as "Anonymous" per year. Problem is, once they give "Anonymously" at any given year, their name stops showing up, and appears as "Anonymous" for the following years, including the previous years as well.

The list should only list "Anonymous" for the current year that it is pulling the information from, if they have given anonymously for that year. Otherwise, it should just show their name.

I am not sure if I need to set the years as a loop and then set an "<cfif>/<cfelse>" statement. Any help would be appreciated.

This is my code where it outputs the information:

<td><cfif SalAnnRep_Ovr IS NOT "">#SalAnnRep_Ovr#<cfelse>#SalAnnRep#</cfif></td>

The data that I am pulling from my DB:

<cfquery datasource="gnConstituents" name="GetCon">

SELECT IDCon, Ca_First, Ca_Middle, Ca_Last, Ca_Suffix, isSchoolChampion, <cfif FYoffset LTE 5>FY#FYoffset#_CY AS CY<cfelse>0 AS CY</cfif>, SalAnnRep, SalAnnRep_Ovr

FROM Constituents

WHERE RecordType = 'Ind'

</cfquery>

I am also retrieving the year through roman numeral.

<cfquery datasource="prep" name="GetAGC">

SELECT *

FROM romans

WHERE RomNum = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.AGC#">

ORDER BY RomNum DESC

</cfquery>

Views

162

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Querying the Right Title per Corresponding Year

New Here ,
Sep 14, 2018

Copy link to clipboard

Copied

Hi,

I'm having trouble figuring the logic on how I can output this information.

Basically, I'm querying a yearly report from my database where some donors post as "Anonymous" per year. Problem is, once they give "Anonymously" at any given year, their name stops showing up, and appears as "Anonymous" for the following years, including the previous years as well.

The list should only list "Anonymous" for the current year that it is pulling the information from, if they have given anonymously for that year. Otherwise, it should just show their name.

I am not sure if I need to set the years as a loop and then set an "<cfif>/<cfelse>" statement. Any help would be appreciated.

This is my code where it outputs the information:

<td><cfif SalAnnRep_Ovr IS NOT "">#SalAnnRep_Ovr#<cfelse>#SalAnnRep#</cfif></td>

The data that I am pulling from my DB:

<cfquery datasource="gnConstituents" name="GetCon">

SELECT IDCon, Ca_First, Ca_Middle, Ca_Last, Ca_Suffix, isSchoolChampion, <cfif FYoffset LTE 5>FY#FYoffset#_CY AS CY<cfelse>0 AS CY</cfif>, SalAnnRep, SalAnnRep_Ovr

FROM Constituents

WHERE RecordType = 'Ind'

</cfquery>

I am also retrieving the year through roman numeral.

<cfquery datasource="prep" name="GetAGC">

SELECT *

FROM romans

WHERE RomNum = <cfqueryparam cfsqltype="cf_sql_integer" value="#Form.AGC#">

ORDER BY RomNum DESC

</cfquery>

Views

163

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 14, 2018 0
Adobe Community Professional ,
Sep 16, 2018

Copy link to clipboard

Copied

Could you tell us which columns you wish to output? We don't know what the names stand for.

To debug, you could place the following after the queries

<cfdump var="#GetCon#">

<cfdump var="#GetAGC#">

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 16, 2018 0
IvanGn LATEST
New Here ,
Sep 17, 2018

Copy link to clipboard

Copied

Thank you for the suggestion on <cfdump>!

I'm always learning through this forum and did not know how I could see the query results!QueryResults.PNG

Ideally, I want the ROMTEXT to output the right year that corresponds whether or not the Donor gave Anonymously or not.

I believe the logic would be if the ROMTEXT/NUM corresponds to the same current year, it would show Anonymous or the Full Name in the report.

The column that I want to output is SalAnnRep. If they have given Anonymously, it would show on the result in SalAnnRep_Ovr.

The name ties back to the year they were given 'Anonymously' or not.


I hope this answered your question.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2018 0