web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Check out the Community Spotlight Video Series
News and Announcements icon
Community site session details

Community site session details

Session Id :
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

I have the same question (0)
  • Naga Kiran Profile Picture
    Naga Kiran Microsoft Employee on at

    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.

  • BethH Profile Picture
    BethH 140 on at

    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 Microsoft Employee on at

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

  • Verified answer
    L Vail Profile Picture
    L Vail 65,271 on at

    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

  • BethH Profile Picture
    BethH 140 on at

    Leslie,

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

    Thanks - Beth

  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at

    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

  • L Vail Profile Picture
    L Vail 65,271 on at

    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

  • BethH Profile Picture
    BethH 140 on at

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

    Beth

  • Verified answer
    L Vail Profile Picture
    L Vail 65,271 on at

    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

    Leslie,

    This works GREAT...Thanks so much!

    Beth

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

News and Announcements

Check out the Community Spotlight Video Series

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans