10 Replies Latest reply on Feb 18, 2008 12:45 PM by wkolcz

    Letters above names in output

    wkolcz
      I have a list of users ordered by last name. The request has come that the client would like a capital letter (for the last name) above each users grouped by the user's first letter of the last name.

      I.E.

      A
      Alexander, Steve
      Anserson, James

      B
      Baily, Jenny
      Bonnet, Mike

      Can someone give me a hint on how to do this using the last name to determine if there is a letter. Thanks.
        • 1. Re: Letters above names in output
          Dan Bracuk Level 5
          The easiest way is to include the first letter as a field in your query. If your db has string functions, that's the best way. If it doesn't, select some constant as a dummy field and then loop through the query to change that value afterwards.

          Once you have the first letter in your query, you can use the group attribute of cfoutput.
          • 2. Re: Letters above names in output
            wkolcz Level 1
            Here is my Query (Oracle).

            I have no place to grab a single letter.

            SELECT DISTINCT
            ind.individual_id,
            mail_name,
            first_name,
            middle_name,
            last_name,
            name_suffix,
            degrees,
            clinical_specialty service_name,
            <!---prov.planning_specialty service_name,--->
            facdept.um_department,
            fellow_name_flag
            FROM MS.INDIVIDUAL ind,
            MS.provider prov,
            pcd.clinical_faculty clinfac,
            pcd.view_guide_faculty_department facdept
            where ind.individual_id = prov.individual_id
            AND ind.individual_id = clinfac.individual_id
            AND clinfac.individual_id = facdept.individual_id
            AND show_on_pcd_flag = 'YES'
            AND facdept.um_department='#searchphys_department#'
            AND clinical_specialty is not null
            UNION
            SELECT DISTINCT
            ind.individual_id,
            mail_name,
            first_name,
            middle_name,
            last_name,
            name_suffix,
            degrees,
            prov.planning_specialty service_name,
            <!---prov.planning_specialty service_name,--->
            facdept.um_department,
            fellow_name_flag
            FROM MS.INDIVIDUAL ind,
            MS.provider prov,
            pcd.clinical_faculty clinfac,
            pcd.view_guide_faculty_department facdept
            where ind.individual_id = prov.individual_id
            AND ind.individual_id = clinfac.individual_id
            AND clinfac.individual_id = facdept.individual_id
            AND show_on_pcd_flag = 'YES'
            AND facdept.um_department='#searchphys_department#'
            AND clinical_specialty is null
            • 3. Re: Letters above names in output
              paross1 Level 2
              Something like this?

              SELECT first_letter,
              individual_id,
              mail_name,
              first_name,
              middle_name,
              last_name,
              name_suffix,
              degrees,
              clinical_specialty service_name,
              um_department,
              fellow_name_flag
              FROM
              (SELECT DISTINCT
              UPPER(SUBSTR(last_name, 1, 1)) AS first_letter,
              ind.individual_id,
              mail_name,
              first_name,
              middle_name,
              last_name,
              name_suffix,
              degrees,
              clinical_specialty service_name,
              facdept.um_department,
              fellow_name_flag
              FROM MS.INDIVIDUAL ind,
              MS.provider prov,
              pcd.clinical_faculty clinfac,
              pcd.view_guide_faculty_department facdept
              where ind.individual_id = prov.individual_id
              AND ind.individual_id = clinfac.individual_id
              AND clinfac.individual_id = facdept.individual_id
              AND show_on_pcd_flag = 'YES'
              AND facdept.um_department='#searchphys_department#'
              AND clinical_specialty is not null
              UNION
              SELECT DISTINCT
              UPPER(SUBSTR(last_name, 1, 1)) AS first_letter,
              ind.individual_id,
              mail_name,
              first_name,
              middle_name,
              last_name,
              name_suffix,
              degrees,
              prov.planning_specialty service_name,
              facdept.um_department,
              fellow_name_flag
              FROM MS.INDIVIDUAL ind,
              MS.provider prov,
              pcd.clinical_faculty clinfac,
              pcd.view_guide_faculty_department facdept
              where ind.individual_id = prov.individual_id
              AND ind.individual_id = clinfac.individual_id
              AND clinfac.individual_id = facdept.individual_id
              AND show_on_pcd_flag = 'YES'
              AND facdept.um_department='#searchphys_department#'
              AND clinical_specialty is null)
              ORDER BY first_letter, last_name, first_name, middle_name

              Phil
              • 4. Re: Letters above names in output
                wkolcz Level 1
                Is it possible to reselect the last_name and Alias it after I, somehow, trim the column value down to the first letter? Does anyone know how to do that?
                • 5. Re: Letters above names in output
                  Dan Bracuk Level 5
                  Add substr(last_name, 1, 1) first_letter to your query.

                  By the way, the rest of your query does not meet the requirements you stated in your original post.
                  • 6. Re: Letters above names in output
                    wkolcz Level 1
                    Thanks for the insight on the trimming. I had found that same solution and it appears to work fine. I am having trouble with the grouping and displaying, but hopefully can clear that up shortly.

                    What about the query doesn't match my requirements?
                    • 7. Re: Letters above names in output
                      wkolcz Level 1
                      Stuck.

                      Ok, I have the first_letter correct and the values of last_name and first_name

                      How can I list them to have the letter above and the names below?

                      The way I did this before is not working correctly. Thanks!
                      • 8. Letters above names in output
                        paross1 Level 2
                        Something like this:

                        <table>
                        <cfoutput query="your_query" group="first_letter>
                        <tr>#first_letter#</tr>
                        <cfoutput>
                        <tr>#last_name#, #first_name#</tr>
                        </cfoutput>
                        </cfoutput>
                        </table>

                        Phil
                        • 9. Re: Letters above names in output
                          wkolcz Level 1
                          Ok, the loop seems to work, but I am grabbing many of the single letters so it loops over the alphabet about 2 dozen times. Any way to stop that?
                          • 10. Re: Letters above names in output
                            wkolcz Level 1
                            Nevermind. Fixed it.