3 Related Select using Coldfusion

109 Views Asked by At

Hi I've been following this post (Dynamic Dependent / Triple Related selects in ColdFusion, third not holding first select), but cannot locate the error I am facing. My CFC is

<cfcomponent>
    <cffunction name="getLevel" access="remote" returntype="Any">
        <cfquery name="lstLevel" datasource="#request.dsn#">
            SELECT Level_ID, Level_Name
            FROM SUS_INDX_LEVEL
            ORDER BY Level_ID
        </cfquery>
        <cfreturn lstLevel>
    </cffunction>
    
        <cffunction name="getrole" access="remote" returntype="Any">
        <cfquery name="lstrole" datasource="#request.dsn#">
            SELECT role_ID, role_Name
            FROM SUS_INDX_ROLE
            ORDER BY role_ID
        </cfquery>
        <cfreturn lstrole>
    </cffunction>

    <cffunction name="getSubRoles" access="remote" returnType="any">
    <cfargument name="SUS_INDX_ROLE" type="any" required="true">
        <cfif ARGUMENTS.SUS_INDX_ROLE EQ "">
        <cfelse>
            <cfquery name="LstSubRoles" datasource="#request.dsn#">
                SELECT Sub_Role_Id, Role_id, Sub_Role_Name
                FROM SUS_INDX_SUB_ROLE
                WHERE Role_id = #ARGUMENTS.SUS_INDX_ROLE#
                ORDER BY Role_id
            </cfquery>
        </cfif>
        <cfreturn LstSubRoles>
    </cffunction>
    
    <cffunction name="getPractice" access="remote" returnType="any">
    <cfargument name="SUS_INDX_SUB_ROLE" type="any" required="true">
    <cfargument name="SUS_INDX_Level" type="any" required="true" >
        <cfif ARGUMENTS.SUS_INDX_SUB_ROLE EQ "">
        <cfelse>
            <cfquery name="lstPractice" datasource="#request.dsn#">
                SELECT Objectives,
                    Objective_ID
                FROM SUS_INDX_OBJECTIVES
                WHERE Sub_Role_ID = #ARGUMENTS.SUS_INDX_SUB_ROLE# 
                    and Level_ID=#ARGUMENTS.SUS_INDX_LEVEL#
                    and DEPT_ID=#SESSION.User.DEPT#
                ORDER BY Objective_ID
            </cfquery>
        </cfif>
        <cfreturn lstPractice>
    </cffunction>
</cfcomponent>

MY CFM

<cfajaxproxy cfc="related">
<cfFORM method="post" name="infoform"
                    action="actGettingStarted.cfm" 
                    onSubmit="return ValidateForm()">
<table>
<tbody>
    <tr>
        <td>Level</td>
            <td>
                <cfselect name="selLevel" bind="cfc:related.getLevel()"
                display="level_name" value="level_Id" bindonload="true" />
            </td>
        <td> Roles</td>
            <td>
                <cfselect name="selRoles" bind="cfc:related.getrole()"
                display="Role_Name" value="Role_Id" bindonload="true" />
            </td>
        <td>Sub Roles</td>
            <td>
                <cfselect name="SelSubRole" bind="cfc:related.getSubRoles({selRoles})"
                display="Sub_Role_name" value="Sub_role_Id" bindOnLoad="false"/>
            </td>
        <td>Practice</td>
            <td>
                <cfselect name="selPractice" bind="cfc:related.getPractice({SelSubRole},{selLevel})"
                display="Objectives" value="Objective_ID" bindOnLoad="false" />
            </td> 
    </tr>
</tbody>
</table>
</cfform>

The problem arises in the 4th Function of the cfc that when I pass the 2nd argument.

**<cfargument name="SUS_INDX_LEVEL" type="any" required="true" >**

I get an error http: Error invoking CFC /test/testGreen/related.cfc : Variable lstPractice is undefined. I want to pass 3 parameters to the WHERE clause of the 4th function sub_role_id, level_id and dept_id. The sub_role_id is passed, the dept_id is passed but when I pass the level_id from the first function I get the mentioned error.

Help is requested

0

There are 0 best solutions below