How do you query against an HR11 User Field

Can anyone assist with the most efficient approach to writing a query to determine the user field values associated with an HR11 record?

I am trying to determine which employees have certain values assigned in various user fields. I know that the table for user fields is HRUSERFLDS, but I'm uncertain of the relationship between it and the EMPLOYEE table/records.

(I'm attempting to do this as a SQL query, not through Add-Ins)

Thanks in advance.

Find more posts tagged with

Infor Lawson Human Resources Group - Discussion

Comments

eokoth

You want to use the table HREMPUSF that will link to the employee table.

unknown

You should be careful. Not having an thorough understanding of what or how files relate to one another could produce significant inaccuracies in any query or report. I suggest you review Infor file documentation and/or open an incident with Infor to obtain the proper file and relationships.

unknown

Thank you eokoth. That is the table I was looking for. :-)

PLM for Process - EMEA User Group - Roadmap Discussion-20241113_043900-Meeting Recording.zip

unknown

This will return the employee#, name, and the value for a designated user key:

select emp.employee,

trim(emp.last_name) || ', ' || trim(emp.first_name) || ' ' || emp.middle_init full_name,

hruf.a_field

from dev.employee emp

inner join dev.hrempusf hruf on (emp.company = hruf.company) and (emp.employee = hruf.employee)

where 1=1

and hruf.field_key=99 -- change to match the field key your looking for

unknown

Kelvin,

Thanks for your concern. Good advice. However, I do in fact understand the data and am knowledgeable of both the risk & reward of the queries and data in question...I simply couldn't remember the table name I was looking for, and knew that I'd get a quick answer by posing the question to the community.

Jim,

Thank you. Your suggestion is very similar to what I used, but since we more often know the field name description as opposed to the actual field key assignment, this was my approach:

SELECT E.COMPANY,E.EMPLOYEE,EU.FIELD_KEY,EU.A_FIELD,UF.FIELD_NAME

FROM dev.dbo.EMPLOYEE E

JOIN HREMPUSF EU ON E.COMPANY=EU.COMPANY AND E.EMPLOYEE=EU.EMPLOYEE

JOIN HRUSERFLDS UF ON EU.FIELD_KEY=UF.FIELD_KEY

WHERE E.COMPANY = 5000 AND UF.FIELD_NAME LIKE '%WELLNESS%'

ORDER BY A_FIELD DESC

Copyright © 2025 Infor. All rights reserved.