• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Querying the Right Title per Corresponding Year

New Here ,
Sep 14, 2018 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

231

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
community guidelines
Community Expert ,
Sep 16, 2018 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#">

Votes

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
community guidelines
New Here ,
Sep 17, 2018 Sep 17, 2018

Copy link to clipboard

Copied

LATEST

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.

Votes

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
community guidelines
Resources
Documentation