Ann Posted April 23, 2021 Share Posted April 23, 2021 Afternoon All Hope you are safe and well. I may be missing something very simple, I am trying to create a report using 3 tables however I am unable to link the third table. I am currently using the 'Projects' table and the 'Project Tasks' table linking with a Left Join and by the Project ID. However I need to be able to include in the report the project managers name so was trying to link the table 'h_sys_accounts' but keep getting error messages. Please would it be possible for some help? Thank you. Kind Regards Ann Link to comment Share on other sites More sharing options...
Victor Posted April 23, 2021 Share Posted April 23, 2021 @Ann 37 minutes ago, Ann said: I am currently using the 'Projects' table and the 'Project Tasks' table linking with a Left Join and by the Project ID. Why LEFT JOIN and not (normal) JOIN? 37 minutes ago, Ann said: so was trying to link the table 'h_sys_accounts' but keep getting error messages. Can you please post a screenshot of how is this configured in your report? Link to comment Share on other sites More sharing options...
Ann Posted April 26, 2021 Author Share Posted April 26, 2021 Afternoon @Victor Hope you are well and had a lovely weekend. I have tried the report with a (normal) JOIN between the tables but am still unable to link the h_sys_accounts table. It may be something very simple that I am missing but I am unable to find the same column in h_sys_accounts to what is in Projects and Project Tasks. We have a several reports that we really need the project managers name to display for. Please find attached a screen shot of how I have the report set up. Thank you. Kind Regards Ann Link to comment Share on other sites More sharing options...
Ann Posted April 30, 2021 Author Share Posted April 30, 2021 Afternoon @Victor Hope you are well. Please would it be possible for an update on the above query? Sorry this is something that is causing quite an over head for us at the moment. I have tried to join the tables with a normal join and I have tried to link just the Projects and h_sys_accounts table but don't know which column to use. Kind Regards Ann Link to comment Share on other sites More sharing options...
Steve Giller Posted April 30, 2021 Share Posted April 30, 2021 @Ann You might need to engage with Expert Services for this one. As far as I'm aware, the Project Manager isn't recorded in the Projects table, so you'd need to extract the PM from the Project Members table (although looking at that data you can have more than one member with the PM role per project) and then use that information to link to the Users table to gather the User's name. As you can see this will take a deeper understanding of the tables and relationships than I have, so if there is an urgency to creating this report I'd have to suggest Expert Services. If the report is a "Nice to have" then I'm sure the answers will be forthcoming as other commitments allow. Link to comment Share on other sites More sharing options...
Victor Posted May 4, 2021 Share Posted May 4, 2021 As @Steve Gillersaid, you would need to join 2 more tables to get the name of the project manager. However, I can see this becoming a bit more complex now, what data are you looking for, what is the report for? I can then have a look and see how a report can be build. Link to comment Share on other sites More sharing options...
Ann Posted May 11, 2021 Author Share Posted May 11, 2021 Afternoon @Victor and @Steve Giller Thank you for the above help. We currently have a report that brings back a list of tasks but we need to include the project manager of that project. Even if it is just a report that pulls back the Project manager for each project I can then use a VLOOKUP in excel but currently just can not find a way to extract the project name AND the project manager name. Thank you. Kind Regards Ann Link to comment Share on other sites More sharing options...
Victor Posted May 11, 2021 Share Posted May 11, 2021 Afternoon @Ann 59 minutes ago, Ann said: We currently have a report that brings back a list of tasks What criteria you have for this list, what filters? Link to comment Share on other sites More sharing options...
Ann Posted May 11, 2021 Author Share Posted May 11, 2021 @VictorWe have a report that is perfect for tasking, we just need to add the project manager for each project onto the report. Link to comment Share on other sites More sharing options...
Victor Posted May 11, 2021 Share Posted May 11, 2021 @Ann that's fantastic, happy to hear the report is suited for the tasking. What criteria you have for this report, what filters? Link to comment Share on other sites More sharing options...
Nikolaj Posted June 17, 2021 Share Posted June 17, 2021 Hi @Ann and @Victor we are doing it like this... maybe it helps.. best regards Nikolaj Link to comment Share on other sites More sharing options...
Ann Posted June 18, 2021 Author Share Posted June 18, 2021 Thank you @Nikolaj, that is fantastic. I will give this a try. 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now