6 Replies Latest reply on May 9, 2008 1:19 PM by Newsgroup_User

    Coldfusion Structures Null Values

    apocalipsis19 Level 1
      Hi community!

      I am writing a report that breaks school down to Grade-Level - Ethnicity - Gender counts for students with more than 3 unlawful absences. I write a query, then I turned that query into a structure in the form:

      <cfset StudentListStruct=session.DBAccess.QueryToStruct(QuerySet=getTruancyCalculations, Grouping="GRADE_LEVEL,ETHNICITY,GENDER")/>

      QueryToStruct is a custom function that we created that turns any resultset in a structure. I am grouping my counts by Grade Level, Ethnicity then Gender. When I dump my structure I can see that for some grade levels one of the elements in my ethnicities is empty, null or whatever you want to call it.

      This is what I do to print:

      <cfif StructKeyExists(StudentListStruct[getUniqueGradeLevels.GRADE_LEVEL[y]],getUniqueEthniciti es.ETHNICITY[z])AND StructKeyExists(StudentListStruct,getUniqueGradeLevels.GRADE_LEVEL[y] AND getUniqueEthnicities.ETHNICITY[z] neq '')>
      #StudentListStruct[getUniqueGradeLevels.GRADE_LEVEL[y]][getUniqueEthnicities.ETHNICITY[z] ][getUniqueGenders.GENDER[looper]]#
      <cfelse>
      0
      </cfif>

      That code is enclosed 3 loops, one loop that loops throught he grade levels, one loop that loops through the ethnicities and one loop that loops through the genders. I am getting all zeros because my first condition is never being accomplished. I don't want that. I attached the code at the end of this message.

      Any thoughts?

      Thanks!

        • 1. Re: Coldfusion Structures Null Values
          Dan Bracuk Level 5
          I only read part of your code but my intitial thought is that you are overengineering something simple. If you want information about students with more than 3 absences, why not write a query that selects that data and then present it?
          • 2. Re: Coldfusion Structures Null Values
            apocalipsis19 Level 1
            Well, the school wants to show that data in a report broken down by grade level, ethnicity and gender.

            Any thoughts?
            • 3. Re: Coldfusion Structures Null Values
              -==cfSearching==- Level 4
              apocalipsis19 wrote:
              > Any thoughts?

              I agree. This seems like something that could be achieved much more easily done inside a sql query. I think you are over-complicating the code. Perhaps you are unfamiliar with sql, or are uncertain how to write a query that would produce those results. If you could post the structure of the pertinent tables, column names and relationships, others would be better able to assist you.
              • 4. Re: Coldfusion Structures Null Values
                Dan Bracuk Level 5
                quote:

                Originally posted by: apocalipsis19
                Well, the school wants to show that data in a report broken down by grade level, ethnicity and gender.

                Any thoughts?

                start with

                select studentid, sex, race, grade, count(race) absents
                from some tables
                where your conditions are met
                group by studentid, sex, race, grade
                having count(race) >= 3
                • 5. Re: Coldfusion Structures Null Values
                  apocalipsis19 Level 1
                  Thanks Dan! I worked my problem around. The problem is that I was dumping my structure. I forgot that the dump function automatically sorts the structure in a way that makes sense to the human eye to see on the screen. So I thought that I could manipulate my elements just the way I was looking at them. I was wrong. I sorted my structure and sub-structures using:

                  ListSort(StructKeyList(StudentListStruct)

                  This way I got a sorted list of elements. I was getting an exception because obviously I was referencing the wrong position of my data because my data was not organized as I thought it was. Once I sorted my data my problems disappeared. I just looped through my lists and verified the existence of the element then printed on screen otherwise prints zero.

                  My problem was not writing a query that group my counts in the form of GRADE LEVEL - ETHNICITY - GENDER, the problem was to layout those elements in an HTML table for printing. I thought it was more intelligent to use the power of structures here. So to print my elements I wrote:

                  <cfif StructKeyExists(StudentListStruct, x) AND StructKeyExists(StudentListStruct[x], y) AND StructKeyExists(StudentListStruct[x][y], z)>
                  #StudentListStruct[x][y][z].TOTALSTUDENTS#
                  <cfelse>
                  0
                  </cfif>

                  Where X represent an array of grade levels, Y represents an array of ethnicities and Z represents a list with two elements F and M for female and male.
                  Thanks to those who replied and helped! I really appreciate it!

                  Apocalipsis!

                  • 6. Re: Coldfusion Structures Null Values
                    Level 7
                    apocalipsis19 wrote:
                    > ListSort(StructKeyList(StudentListStruct)

                    If you care to, you can replace that with the single structSort(...)
                    function. Which returns a list of keys sorted by the desired structure
                    element.