AlexOnTheHill Posted March 20, 2023 Posted March 20, 2023 I am looking into a problem where our user sync has been unable to map users organisation details fully. I can see the reason is because a number of new divisions have been created internally which do not yet exist within Hornbill. In order to identify each of these missing divisions it would be really useful if I could run a report that shows each user who is missing theirs. Our structure is Company - Department - Division Affected users are just showing Company - Department I've been trying a few things but not having much success. Any help is appreciated.
Steve Giller Posted March 24, 2023 Posted March 24, 2023 You might be able to adapt Neil's solution to a similar question for this.
AlexOnTheHill Posted March 28, 2023 Author Posted March 28, 2023 I've had a go at modifying this one but wasn't successful. I don't think I'd have any difficulty reporting on users without any roles as roles are a field I can point to directly. The problem I have is that I want to say "show me the users who don't have an organisation type 4" Perhaps this is better submitted as a database direct query?
yelyah.nodrog Posted August 24, 2023 Posted August 24, 2023 @AlexOnTheHill Would love to know the answer to this as well as we have a bunch of departments that haven't been input into HB so the users don't have them set. I cannot seem to get it to report on Type '2' (Department) where its NULL/Empty on the reports side or Database Direct Did you get anywhere with this? H
AlexOnTheHill Posted August 24, 2023 Author Posted August 24, 2023 I didn't make any headway with this one sadly. I had hoped there would be something in h_sys_accounts but there isn't. The groups are under h_sys_account_groups and the group types are under h_sys_groups so you'd need to link each of these. I'm terrible at SQL but I was able to create a quick search on database direct: SELECT * FROM h_sys_account_groups JOIN h_sys_groups on h_group_id = h_id WHERE h_user_id = '[enter ID here]' AND h_type = '2' That shows me if someone has got a department, now I think we just need to find the inverse of that
yelyah.nodrog Posted August 25, 2023 Posted August 25, 2023 Yes, we are stuck on the inverse of that aswell, I think its becasue all of the Org stuff is held in that field so the only time we get a result on null is if they have no cost centre, directorate or Department etc Thankyou though! H
Met Posted August 25, 2023 Posted August 25, 2023 Hi @yelyah.nodrog @AlexOnTheHill Try the following SELECT DISTINCT h_user_id FROM h_sys_account_groups WHERE h_user_id NOT IN ( SELECT h_user_id FROM h_sys_account_groups JOIN h_sys_groups on h_group_id = h_id WHERE h_type = '<ID>' ) The second SELECT statement is yours and returns a list of users who do belong to a specific organisation/team etc. The first SELECT statement then returns a list of users who do not appear in the output of the second statement. 1 1
AlexOnTheHill Posted August 25, 2023 Author Posted August 25, 2023 That's it! Brilliant! Exactly what was needed! What you've done there makes sense too. Thank you for the explanation. 1
yelyah.nodrog Posted August 31, 2023 Posted August 31, 2023 Thankyou so much, I just need to join h_sys_accounts and I can search by only active users aswell this is amazing!
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