Pulling data from PREmployeeAttribute in a GI

Hi @brandontfrank 

There is no straight answer to your question, and you will not find all of the attributes in the out-of-the-box DAD definition. There are two general considerations. I believe I had provided a thorough explanation on a different topic related to the attributes but I can’t find it so I will try to explain to the best of my knowledge. Others might have a better solution:

1. You will need to join your “EPEmployee” DAC to the GROUP OF ATTRIBUTE tables equal to the number of attributes that you want to retrieve. The reason is the attributes do not live in the associate record table. The attributes have their own dedicated table and are stored in rows and not in columns.
2. Depending on the type of attributes that you are trying to retrieve, your approach will be different. The easiest retrieval will be string attributes and the most difficult one is the selector type attributes.

The GROUP OF ATTRIBUTE tables (this is what I call them not an official name) are:

CSAttribute : Stores the Attributes and its key field is AttributeID. For Example, if you create an attributed of list type to have dropdown values and call it “EC” and “Eye Color” it will be stored in this table.

CSAttributeDetail: Stores the predefined values of the Attributes. It stores all predefined values other than “Text” and “Selector”. Its key fields are AttributeID and ValueID. If in our above example you enter “BK” “Black”, “BR” “Brown and “BL” “Blue” they will be stoted in this table.

Now Acumatica platform needs to know what master entities (i.e. Employees, Projects, ...) can use which one of these Attributes. When in the Employee Preferences (note that some of these links may be configured behind the scenes and you do not see it) we say Employee record can use Attribute “EC” the platform stores the link between the Attribute and the Employee entity in the CSAttributeGroup table. Its key fields are AttributeID, EntityClassID and EntityType. The EntityType for Employees will be stored as “PX.Objects.EP.EPEmployee” if we need to filter on it. Most probably you will not need this table in your joins.

Now that the platform knows each entity what kind of Attributes can have, we can proceed with assigning the Attributes Values to each individual master record. This mapping stored in CSAnswers and its key fields are RefNoteID and AttributeID. The AttributeID abviously will need to be joind with CSAttribute but the RefNoteID is the NoteID of the master record we are trying to assign an attribute to it. For example, in Employee case the LEFT (because some employees may do not have attribute and still, we want them in the report) JOIN is EpEmployee.NoteID = CSAnswers.RefNoteID.

The value you want to show in your inquiry for string fields is CSAnswers.Value. Assuming all your values are string field, you will need to repeat this process for each attribute. For dropdown and selector it is a little bit more complicated. for example for selector still you will need to get the CSAnswers.Value from here, depending on the primary key of selector table join with that table and return the desired value from that table. For dropdown, you wiull need to join the CSAnswers.Value = CSAttributeDetail.ValueID and return the CSAttributeDetail.Description.

to add an example let say we have another attribute called “SX” “Sex” that has a dropdown value of “W” “Woman”, “M’ “Man” and “X” “Unknown”

I am writing my query in SQL format but you can easily translate to GI

SELECT

EMP.EmployeeCD, ANS1.Value, ATD2.Description

FROM

EPEmployee EMP

LEFT JOIN CSAnswers ANS1 ON EMP.NoteID = ANS1.RefNoteID

LEFT JOIN CSAttribute ATR1 ON ANS1.AttributeID = ATR1.AttributeID AND ATR1.AttributeID = ‘EC’

LEFT JOIN CSAnswers ANS2 ON EMP.NoteID = ANS2.RefNoteID

LEFT JOIN CSAttributeDetail ATD2 ON ANS2.AttributeID = ATD2.AttributeID AND ANS2.Value = ATD2.ValueID AND ATD2.AttributeID = ‘SX’