Jump to content

Recommended Posts

Posted

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.

Posted

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?

  • 4 months later...
Posted

@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

Posted

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

Posted

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!

Posted

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.

  • Like 1
  • Thanks 1

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...