Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / HR Training Module Tab...
Microsoft Dynamics GP (Archived)

HR Training Module Tables for Reporting

(0) ShareShare
ReportReport
Posted on by BethH 140

How do you link the HR2TRA03 table (to obtain the Class Name) to the HR2TRA01, CLM00300 and the UPR00100 tables and not have duplicate trainings print for each staff person?  We have a report that works correctly using these tables, but when I add the HR2TRA03 table it creates duplicates.  Thanks for the help - Beth  

*This post is locked for comments

  • BethH Profile Picture
    BethH 140 on at
    RE: HR Training Module Tables for Reporting

    Leslie,

    This works GREAT...Thanks so much!

    Beth

  • Verified answer
    L Vail Profile Picture
    L Vail 65,271 on at
    RE: HR Training Module Tables for Reporting

    Great,

    I made a mistake on the format for the dates, use 101 instead of 103. 103 turns it into dd/mm/yyyy, 101 turns it into mm/dd/yyyy.

    Kind regards,

    Leslie

  • BethH Profile Picture
    BethH 140 on at
    RE: HR Training Module Tables for Reporting

    Thanks so much Leslie....will give this a try!  

    Beth

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: HR Training Module Tables for Reporting

    Beth

    If you want the date to appear dd/mm/yyyy, change the select for the completion date to:

          , CONVERT(VARCHAR,clm00300.completion_date, 103) as Completion_Date

    Kind regards,

    Leslie

  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at
    RE: HR Training Module Tables for Reporting

    Hi Beth,

    You need table HR2TRA01 to get the Course Name.

    Here's what mine looks like:

    SELECT clm00300.employid

      ,  rtrim(LASTNAME) + ', ' + rtrim(FRSTNAME) +' '+ rtrim(MIDLNAME) As 'Staff_Name'

          , upr00100.deprtmnt as Dept

          , hr2tra03.courseid_i as Course_ID

          , hr2tra01.coursename_i as Course_Name

          , hr2tra03.classname_i as Class_Name

          , clm00300.completion_date as Completion_Date

    FROM   clm00300

          INNER JOIN upr00100

                  ON clm00300.employid = upr00100.employid

          INNER JOIN hr2tra03

                  ON clm00300.clm_course_id = hr2tra03.courseid_i

                     AND clm00300.clm_class_id = hr2tra03.iclassid_i

          INNER JOIN hr2tra01

                  ON hr2tra03.courseid_i = hr2tra01.courseid_i  

    Kind regards,

    Leslie

  • BethH Profile Picture
    BethH 140 on at
    RE: HR Training Module Tables for Reporting

    Leslie,

    Staff ID, Staff Name, Department, Course ID, Course Name, Class Name, Complete Date

    Thanks - Beth

  • Verified answer
    L Vail Profile Picture
    L Vail 65,271 on at
    RE: HR Training Module Tables for Reporting

    Here's what I used.

    The employee ID will be entered once for each class they competed. What did you want your report to look like?

    SELECT clm00300.employid

          , upr00100.lastname

          , hr2tra03.classname_i

    FROM   clm00300

          INNER JOIN upr00100

                  ON clm00300.employid = upr00100.employid

          INNER JOIN hr2tra03

                  ON clm00300.clm_course_id = hr2tra03.courseid_i

                     AND clm00300.clm_class_id = hr2tra03.iclassid_i  

    Kind regards,

    Leslie

  • Naga Kiran Profile Picture
    Naga Kiran on at
    RE: HR Training Module Tables for Reporting

    Bethh, Could you post the SQL View to see if I can fix it to avoid the duplicate records.

  • BethH Profile Picture
    BethH 140 on at
    RE: HR Training Module Tables for Reporting

    I have tried to add a screen shot and it won't allow me to add one in the text box and I don't see an attachment button.  I am trying to post the SQL View I am using.

  • Naga Kiran Profile Picture
    Naga Kiran on at
    RE: HR Training Module Tables for Reporting

    Which reporting tool you are using and how you related the above mentioned tables. If you have created a SQL based relation it will be easy to any one to answer if you post the query. if the relation established in a designer, a screen shot of that will help too.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans