• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Can I use a cfif tag within the output attribute of a cffile tag?

I've created apage which generates a csv file from my database. The csv file is later used to print labels. Some records have mailing address some a physical address. I want to include a cfif int there to use the Mailing Address if there is one, if not use the physical. My cfif tag shows up in my text file so I'm guessing that i'm doing it wrong. Here's my code:

<cfquery datasource="mydata" name="list">
select Name, Address, City, State, Zip, MailAddress, MailCity, MailState, MailZip
from Members
where 0=0
order by Name
</cfquery>


    <cffile action="write"
             file="D:\mypath\all_records.csv"
             output="Name%Address%City%State%Zip"
             addnewline="yes">

    <cfoutput>
        <cfloop query="list">
   
        <cffile action="append"
                 file="D:\mypath\all_records.csv"
                  output="#TRIM(Name)# % <cfif MailAddress IS NOT "">#TRIM(MailAddress)#<cfelse>#TRIM(Address)#</cfif> % <cfif MailCity IS NOT "">#TRIM(MailCity)#<cfelse>#TRIM(City)#</cfif> & <cfif MailState IS NOT "">#TRIM(MailState)#<cfelse>#TRIM(State)#</cfif> & <cfif MailZip IS NOT "">#TRIM(MailZip)#<cfelse>#TRIM(Zip)#</cfif>"  
                 addnewline="yes">

        </cfloop>
    </cfoutput>

   <cflocation url="http://www.siteinquestion.com/edit/documents/all_records.csv">


So I haven't really used cfloops much. If I'm not allowed to do it something like this, could I output my query pick the right address then loop through that somehow?

Thanks for the help - Dave
0
davidawhite
Asked:
davidawhite
  • 3
  • 2
1 Solution
 
pmascariCommented:
Use the dynamic if, IIF, like so:

<cffile action="append"
                 file="D:\mypath\all_records.csv"
                  output="#TRIM(Name)# % #IIF(MailAddress IS NOT "",TRIM(MailAddress),TRIM(Address))#>
0
 
_agx_Commented:
Using cfif's like that make the code hard to read.  You could simplify things by using a CASE statement in your query to determine which value to return.  I don't know what database you're using but something like this should work in  MySQL.  

** Not tested **
SELECT      Name,
      CASE WHEN Trim(MailAddress) = '' THEN Address ELSE MailAddress END AS Address,
       CASE WHEN Trim(MailCity) = '' THEN City ELSE MailCity END AS City,
      CASE WHEN Trim(MailState) = '' THEN State ELSE MailState END AS State,
      CASE WHEN Trim(MailZip) = '' THEN Zip ELSE MailZip END AS Zip
FROM      Members
ORDER BY Name

Also, doing multiple "append" operations can be less efficient in some cases.  Appending the data to a variable inside your loop, and doing a single write at the end may be more efficient.

  <cfset newLine = chr(10) & chr(13)>
  <cfloop query="list">
      <cfset data = data & newLine & TRIM(Name) &" % "& TRIM(Address) ....>
  </cfloop>
 
  <cffile action="write"
      file="D:\mypath\all_records.csv" output="#data#">
0
 
davidawhiteAuthor Commented:
pmascarl
When I try that I generate an error "Invalid CFML construct" Can I use that within the <cffile> tag?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
_agx_Commented:
>  When I try that I generate an error "Invalid CFML construct"

Technically you can use IIF there, but likely you would also need to use DE().

<cffile ...   " ....  #IIF(MailAddress IS NOT "", DE(TRIM(MailAddress)), DE(TRIM(Address)))# ...." >

But IMO that makes the code even more unreadable.
0
 
davidawhiteAuthor Commented:
agx,
So I'd never seen IIf before, I looked into it and tried the DE() ended with a different error. Regardless, I tried your idea of doing it all in the query, makes sense I'm just new to sql (and coldfusion for that matter) it works fine only that I had to change the ="" to IS NULL, and everything works. Thanks for the help.
0
 
_agx_Commented:
Glad I could help.  

> it works fine only that I had to change the ="" to IS NULL

   Then there's an even shorter way you could write it.  Both would work, but for the curious
   when the values are NULL (not an empty string) you can use the COALESCE() function
   
    SELECT   Name,
                    COALESCE(MailAddress, Address) AS Address,
                    COALESCE(MailCity, City) AS City,
                    .....


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now