Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-10-10
6
Medium Priority
?
360 Views
Last Modified: 2010-08-05
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
Comment
Question by:davidawhite
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:pmascari
ID: 20053686
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 1000 total points
ID: 20053877
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
 

Author Comment

by:davidawhite
ID: 20058660
pmascarl
When I try that I generate an error "Invalid CFML construct" Can I use that within the <cffile> tag?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 52

Expert Comment

by:_agx_
ID: 20058714
>  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
 

Author Comment

by:davidawhite
ID: 20058764
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
 
LVL 52

Expert Comment

by:_agx_
ID: 20058819
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month20 days, 16 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question