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

Programmatically change display= "value" and href link in cfgrid

I have set up a cfgrid using a cfselect for determining the table to use in the cfgrid.  The client ( a coworker) would like his users to be able to chose to display some or all of the cfgridcolumns.  Also depending on which table the user chooses, the href link needs to be changed such as from href="reptiles.cfm" to href="birds.cfm".  Currently the display value is hardcoded and the href is hardcoded for testing.

Attached is the cfc code and the the form code on the cfm page. I am a beginner at using cfgrid and cfc s with a component.  Thank you for your assistance.

<cfcomponent>
 
<!--- Set the datsources --->
 <cfset THIS.ds="CABR">
 <!---Get list of datatables--->
    <cffunction name="getTableList" output="no"
    		access="remote"
            returntype="query"
            hint="Get Table names for AJAX Select ">
            <!---Define variables--->               
         <cfset var returnTables=""> 
         <!---Get tableNames--->
         <cfquery  name="returnTables"  datasource="#THIS.ds#">  
    SELECT aDataTableName, DataTableName
    FROM DataTableNames
    ORDER BY DataTableName
    </cfquery> 
     <cfreturn returnTables>
     </cffunction>   
 
	<!---Get list of datatables--->
    <cffunction name="getSecTableList" output="no"
    		access="remote"
            returntype="array"
            hint="Get Table names for AJAX Select ">
            <!---Define variables--->         
         <cfset var rstableNames="">        
         <cfset var returnTables=ArrayNew(2)>          
         <cfset var i=0>
         <!---Get tableNames--->
         <cfquery  name="rstableNames"  datasource="#THIS.ds#">  
    SELECT aDataTableName, DataTableName
    FROM DataTableNames
    ORDER BY DataTableName
    </cfquery> 
    <cfloop query="rstableNames">
    	<cfset returnTables[rstableNames.currentRow] [1]=rstableNames.aDataTableName>
    	<cfset returnTables[rstableNames.currentRow] [2]=rstableNames.DataTableName>
     </cfloop>
     <cfreturn returnSecTables>
     </cffunction>   
    
    <!---use cfdump for debugging purposes
    <cfdump var=#getTableList#>
    <cfabort>--->
  <!---Get table name for title--->  
    <cffunction name="getDivTitle" returntype="string" access="remote">
    	<cfargument name="theTitle">
        <cfreturn "<h3>Table Name: #arguments.theTitle#</h3>">
        </cffunction>
        <!---Get table name for title--->  
    <!---<cffunction name="getNewPg" returntype="string" access="remote">
    	<cfargument name="theNewPg">
        <cfif ARGUMENTS.theNewPg LIKE %amphibians%>
        	<cfset var newPg = "Amphibians.cfm" >
        <cfelseif ARGUMENTS.theNewPg LIKE %birds%>
        	<cfset var newPg = "Birds.cfm" >
        <cfelseif ARGUMENTS.theNewPg LIKE %fish%>
        	<cfset var newPg = "Fish.cfm" >
        <cfelseif ARGUMENTS.theNewPg LIKE %mammals%>
        	<cfset var newPg = "Mammals.cfm" >
        <cfelseif ARGUMENTS.theNewPg LIKE %plants%>
        	<cfset var newPg = "Plants.cfm" >
        <cfelseif ARGUMENTS.theNewPg LIKE %reptile%>
        	<cfset var newPg = "Reptiles.cfm" >
            <cfelse>
            <cfset var newPg = "Amphibians.cfm" >
            </cfif>
        <cfreturn  "#variables.NewPg#">
        </cffunction>
--->
 
 <!---   Get displayfield for column display  --->
    <cffunction name="getDisplay" returntype="string" access="remote">
    	<cfargument name="theDisplay">
        <cfreturn "#arguments.theDisplay#">
        </cffunction>
        
<!---Get list of datatables--->
	<cffunction name="list"
    returntype="query" 
    hint="Short list of species">
   
	 <!---Define variables---> 
    <cfset var taxalst="">
    <cfquery datasource="#THIS.ds#"
    name= "taxa">
    SELECT CommonName, SCINAME, Order_Name, Family_Name, ABUNDANCE_TYPE, RESIDENCY_TYPE, Nativity, STATUS_TYPE
    FROM #Arguments.tableName#
    ORDER BY CommonName
    </cfquery>
    
    use cfdump for debugging purposes
    <cfdump var=#taxalst#>
    <cfabort>
     
    <cfreturn taxa>
    </cffunction>
    
    
    <!--- Browse taxa --->
 <cffunction name="browse"
              access="remote" 
              returntype="struct"
              hint="Browse a species list">
     <cfargument name="page" 
              type="numeric" 
              required="yes"
              default="1">
     <cfargument name="pageSize" 
              type="numeric" 
              required="yes"
              default="11">      
   <cfargument name="gridsortcolumn"
              type="string"
              required="no"
              default="">
   <cfargument name="cfgridsortdirection"
              type="string"
              required="no"
              default="">
   <cfargument name="aDataTableName" 
   			type="string" 
            required="true"
            default="CABRamphibians"
            hint="query table name">
  <!---  Local variables --->   
	<cfset var result = structNew()>
   <cfset var qrytaxa="">   
     <cfquery datasource="#THIS.ds#" name="qrytaxa" result="result">
    SELECT accepted_TSN, Residency_type, nativity, Abundance_type, SciName, CommonName, Order_Name, Family_Name,  STATUS_TYPE
    <cfif ARGUMENTS.aDataTableName NEQ "">
    FROM #ARGUMENTS.aDataTableName#
    <cfelse>
     FROM  CABRamphibians
    </cfif>
    WHERE 0=0
   <cfif ARGUMENTS.gridsortcolumn NEQ ""
         and ARGUMENTS.cfgridsortdirection NEQ "">
         ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.cfgridsortdirection#
    </cfif>
    </cfquery>
    <cfif qrytaxa.recordcount lt ARGUMENTS.pagesize>
	<cfset ARGUMENTS.pagesize = qrytaxa.recordcount>
	</cfif>
 
<cfset result = QueryConvertForGrid(qrytaxa,
    	ARGUMENTS.page,    	
	    ARGUMENTS.pageSize)>
        <cfreturn result>
     
    </cffunction>  
<!---    http://objectmix.com/cold-fusion/638290-re-empty-rows-cfgrid.html for setting page size thanks--->
</cfcomponent>
 
<!---CODE from .cfm page -->
	<cfform name="listdatatables" preservedata="yes">
   <!---See page 365 vol 1 Forta's ColdFusion8--->
    
    <cfinvoke component="taxa"
          method="getTableList"
          returnvariable="tableNames">
    Select Data Table: &nbsp;&nbsp; 
        <cfselect name="selDataTableName"
                        bind="cfc:taxa.getTableList()"
                        display="DataTableName"
                        value="aDataTableName"
                        bindonload="true" 
                        selected="CABRamphibians" />       
    
   </td>
     <td  valign="top">
             
    Select minimum or maximum fields view:&nbsp;&nbsp;
        <select name="displayFields" onChange="afield='[document.listdatatables.displayFields.selectedIndex].value';">
            <option value="yes">All Fields</option>
            <option value="no">Minimum Fields</option>
        </select>
<!---   <input name="submitform" type="submit" value="Submit">
--->	
    </td></tr>
       
    </table>
 
    <cfset theField = '#form.displayFields#'>   
  
<cfoutput>Show results:  #variables.aField# </cfoutput>
    <br />
    <hr />
    <br />
   <cfdiv bind="cfc:taxa.getDivTitle({selDataTableName})"></cfdiv>
 >
   <cfdiv bind="cfc:taxa.getDisplay({displayFields})"></cfdiv>
   
       <br/>
 
     <cfinvoke component="taxa"
          method="browse"
          returnvariable="speciesResult">
          <cfinvokeargument name="adatatablename" value="CABRplants">
     </cfinvoke>
       
       <cfoutput>
        <cfgrid name="taxaGrid"
        format="html"
        pagesize="15"
        striperows="yes"
        stripeRowColor="##f5f0dd"
         colheaderalign=" CENTER"
         selectmode="single" 
        bind="cfc:taxa.browse({cfgridpage},
                                {cfgridpagesize},
                                {cfgridsortcolumn},
                                {cfgridsortdirection},
                                {selDataTableName})">
       <div id="biogeneText">
       <cfgridcolumn  name="accepted_TSN"
        		header="TSN"
                headerbold="Yes" 
                headeralign="CENTER"
                width="45"
        		display= "yes"
                href="Reptile.cfm" hrefkey="accepted_TSN" target="_blank">
                </div>
         <cfgridcolumn  name="SciName"
        		header="Scientific Name"
                headerbold="Yes"
                headeralign="CENTER" 
                width="150" >
        <cfgridcolumn  name="CommonName"
        		header="Common Name"
                headerbold="Yes"
                headeralign="CENTER" 
                width="150">
        <cfgridcolumn  name="Order_Name"
        		header="Order"
                headerbold="Yes"
                headeralign="CENTER"                 
                display="#variables.theFields#"
                width="80">
        <cfgridcolumn  name="Family_Name"
        		header="Family"
                headerbold="Yes"
                headeralign="center"  
                display="yes"
                width="100">
        <cfgridcolumn  name="STATUS_TYPE"
        		header="Status"
                headerbold="Yes" 
                headeralign="CENTER"
                display="true"
                width="95">  
        <cfgridcolumn  name="Abundance_type"
        		header="Abundance"
                headerbold="Yes"
                headeralign="CENTER" 
                width="70">
        <cfgridcolumn  name="residency_type"
        		header="Residence"
                headerbold="Yes" 
                headeralign="CENTER" 
                width="70">
        <cfgridcolumn  name="nativity"
        		header="Nativity"
                headerbold="Yes"
                headeralign="CENTER"  
                width="60" >
        </cfgrid>
     </cfoutput>  
 </cfform>

Open in new window

0
W_Hendon
Asked:
W_Hendon
  • 3
1 Solution
 
W_HendonAuthor Commented:
Since being made aware that the user may resize the columns the part of the question about displaying are not displaying may be disregarded.  Thanks.
0
 
azadisaryevCommented:
check out this post by Ray Camden about custom cfgrid renderers:
http://www.coldfusionjedi.com/index.cfm/2007/8/20/Custom-grid-renderers-with-CFGRID

if the link a grid column is tied to the name of the table the data is returned from, you can easily re-render the grid after it loads to add/change the link based on the table being displayed...

hth
0
 
W_HendonAuthor Commented:
Thank you very much.   I am working on this now.
0
 
W_HendonAuthor Commented:
I did not understand the renderers well enough to get them to work.

The solution used was to change the query in the cfc  browse function.  See the code below.
For a limited time the solution may be viewed at:
http://science.nature.nps.gov/im/monitor/PhilippiSpecies/taxaFetch.cfm

The owner is changing the database and some of the code and will be removing it soon from this site.
<cfcomponent>
 
<!--- Set the datsources --->
 <cfset THIS.ds="CABR">
 <!---Get list of datatables--->
    <cffunction name="getTableList" output="no"
    		access="remote"
            returntype="query"
            hint="Get Table names for AJAX Select ">
            <!---Define variables--->               
         <cfset var returnTables=""> 
         <!---Get tableNames--->
         <cfquery  name="returnTables"  datasource="#THIS.ds#">  
    SELECT aDataTableName, DataTableName
    FROM DataTableNames
    ORDER BY DataTableName
    </cfquery> 
     <cfreturn returnTables>
     </cffunction>   
 
	<!---Get list of datatables--->
    <cffunction name="getSecTableList" output="no"
    		access="remote"
            returntype="array"
            hint="Get Table names for AJAX Select ">
            <!---Define variables--->         
         <cfset var rstableNames="">        
         <cfset var returnTables=ArrayNew(2)>          
         <cfset var i=0>
         <!---Get tableNames--->
         <cfquery  name="rstableNames"  datasource="#THIS.ds#">  
    SELECT aDataTableName, DataTableName
    FROM DataTableNames
    ORDER BY DataTableName
    </cfquery> 
    <cfloop query="rstableNames">
    	<cfset returnTables[rstableNames.currentRow] [1]=rstableNames.aDataTableName>
    	<cfset returnTables[rstableNames.currentRow] [2]=rstableNames.DataTableName>
     </cfloop>
     <cfreturn returnSecTables>
     </cffunction>   
    
    <!---use cfdump for debugging purposes
    <cfdump var=#getTableList#>
    <cfabort>--->
  <!---Get table name for title--->  
    <cffunction name="getDivTitle" returntype="string" access="remote">
    	<cfargument name="theTitle">
        <cfreturn "<h3>Taxonomic Group: #arguments.theTitle#</h3>">
        </cffunction>
        
 
 <!---   Get displayfield for column display  --->
    <cffunction name="getDisplay" returntype="string" access="remote">
    	<cfargument name="theDisplay">
        <cfreturn "#arguments.theDisplay#">
        </cffunction>
        
<!---Get list of datatables--->
	<cffunction name="list"
    returntype="query" 
    hint="Short list of species">
   
	 <!---Define variables---> 
    <cfset var taxalst="">
    <cfquery datasource="#THIS.ds#"
    name= "taxa">
    SELECT accepted_TSN, CommonName, SCINAME, Order_Name, Family_Name, ABUNDANCE_TYPE, RESIDENCY_TYPE, Nativity, STATUS_TYPE
    FROM #Arguments.tableName#
    ORDER BY CommonName
    </cfquery>
    
    use cfdump for debugging purposes
    <cfdump var=#taxalst#>
    <cfabort>
     
    <cfreturn taxa>
    </cffunction>
    
    
    <!--- Browse taxa --->
 <cffunction name="browse"
              access="remote" 
              returntype="struct"
              hint="Browse a species list">
     <cfargument name="page" 
              type="numeric" 
              required="yes"
              default="1">
     <cfargument name="pageSize" 
              type="numeric" 
              required="yes"
              default="11">      
   <cfargument name="gridsortcolumn"
              type="string"
              required="no"
              default="">
   <cfargument name="cfgridsortdirection"
              type="string"
              required="no"
              default="">
   <cfargument name="aDataTableName" 
   			type="string" 
            required="true"
            default="CABRamphibians"
            hint="query table name">
            
   	<cfargument name="Order_Name" type="any" required="false" default="">
  <!---  Local variables --->   
	<cfset var result = structNew()>
   <cfset var qrytaxa="">
  <!--- http://www.anujgakhar.com/2008/08/14/crud-with-cfgrid-html-format-part-2/--->
   <cfset var ps = pageSize>
    <cfset var totalPages = "" />   
     <cfquery datasource="#THIS.ds#" name="qrytaxa" result="result">
    SELECT '#replacenocase(ARGUMENTS.aDataTableName,'CABR','','ALL')#' & '.cfm?varId=' & accepted_TSN as linkText,
    	accepted_TSN, Residency_type, nativity, Abundance_type, SciName, CommonName, Order_Name, Family_Name,  STATUS_TYPE
    <cfif ARGUMENTS.aDataTableName NEQ "">
    FROM #ARGUMENTS.aDataTableName#
    <cfelse>
     FROM  CABRamphibians
    </cfif>
	 <cfif len(arguments.Order_Name)>
	WHERE lower Order_Name like <cfqueryparam value="%#lcase(arguments.Order_Name)#%" cfsqltype="cf_sql_varchar">
	<cfelse> 
    WHERE 0=0
	 </cfif> 
   <cfif ARGUMENTS.gridsortcolumn NEQ ""
         and ARGUMENTS.cfgridsortdirection NEQ "">
         ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.cfgridsortdirection#
    </cfif>
    </cfquery>
    <cfset totalPages = ps  * page>	
    <cfif qrytaxa.recordcount lt totalPages>
	<cfset ps = qrytaxa.recordcount -(totalPages - ps)>
	</cfif>
 
<cfset result = QueryConvertForGrid(qrytaxa,
    	ARGUMENTS.page,    	
	    ps)>
        <cfreturn result>
     
    </cffunction>  
<!---    http://objectmix.com/cold-fusion/638290-re-empty-rows-cfgrid.html for setting page size thanks--->
</cfcomponent>

Open in new window

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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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