Full Audit Log

Hi Richard,

That would be awesome, but unfortunately they didn't design the system to make it easy to get history. The auditlog is really intended to be exactly that - and audit log of what was changed by whom. Also, it is stored as xml in a CLOB field, which pretty much sucks.

Probably your best bet is to try the EmployeeTurnover table. There is more to this than just terminations, but you should be able to to filter it down to what you need.

Good Luck,
Kelly

Kelly,

Thanks for the information.  I will look into the EmployeeTurnover table.  Regarding the CLOB field, can you tell me what table this is stored in?

Really confused why Infor would make it hard to get to this most basic of information.

Richard

Hi Richard.

I've had the same thought many times - it makes sense if you think of it as an audit table rather than an "hr history" table. Unfortunately, they didn't really build complete HR History tables, so the audit log is what we've got other than my final comment below.

The location of the AuditLog xml field depends on whether your application was installed with the "split LOB" option - meaning that the Large Object (LOB) fields are separated from the other fields, which improves some maintenance performance (dbrebuilds, etc). If you do NOT have the split LOB option, then the auditlog will be in the business class table as "AuditLog". If you query the business class table and do NOT see that field, then you do have a split LOB setup.

If you DO have split LOB, then the LOB fields (including Audit Log) will be in a tag table that is named S${prefix}, where {prefix} is the table prefix for the business class table. For example, for the Employee business class the prefix is "EMPL", so the LOB table is S$EMPL. These tables don't have employee number in them, you will have to join this to EMPLOYEE where S$EMPL.UniqueID = EMPLOYEE.UniqueID to see who it really belongs to. The prefix for WorkAssignment is EMPWA and the join would be S$EMPWA.UniqueID = WorkAssignment.UniqueID.

By the way, there are also effective dated "snapshot" tables for the business classes as well, and they use the S_{prefix} naming (i.e. S_EMPL and S_EMPWA) and are joined the same way. These do show the business class field values by effective date, which is very handy. However, the DO NOT include the reason code or action reason, which really limits their usefulness. That's why the Turnover tables may be better for you.

I hope this is helpful. Good Luck!
Kelly

Name UserField_Storage
Stamp 1441088000
ClassicName USER-FUSFS
SqlName UserField_Storage
Prefix USFS
don't know if this helps or hurts....but for what you paid.....a bargain!

Module la
RpgName USERFIELD_STORAGE
Translatable false
splitLobStorage false
landmarkTextSearch false
Space msf
DBType MSF2008
DBName msfdb
Schema ltmtest
SqlMapping landmark
Charset ISO-8859-1
TableSpace DATA
IndexSpace INDX
LobSpace DATA
Field 1 Alpha 200 1 UserField_Storage.ClassName
Field 2 UniqueID 36 1 UserField_Storage.UniqueID
Field 3 Alpha 200 1 UserField_Storage.FieldName
Field 4 Numeric 5 1 Type
Field 5 Numeric 6 1 Size
Field 6 Numeric 2 1 Decsize
Field 7 Alpha 2000 1 Alpha
Field 8 BinaryObject 1 1 Blob
Field 9 Text 1 1 Clob
Set 0 SymbolicKey
Primary True
SetField 0 UserField_Storage.ClassName
SetField 1 UserField_Storage.UniqueID
SetField 2 UserField_Storage.FieldName
Relation 0 UserField_Storage
RelatesTo UserField_Storage.SymbolicKey(= UserField_Storage)
Type OneToOne
DeleteRule DeleteRestrict

Hi Steve Finger,

I'm confused about your post. Userfield_Storage is not really related to the AuditLog discussion, and that particular table doesn't have related S$ and S_ tables (at least on our system). Is there something specific you wanted to mention about this table in regards to the AuditLog discussion?

Kelly

Hi Steve Finger,

Now that I looked closer at your post, it appears that maybe you were demonstrating a utility that lists the info for a table. Is that true? If so, I'd love to know what it is or where you got that info dump.

Thanks

i was breezing thru the LTM "biz class" descriptions...this was the only occurrence of "CLOB" out of 261,000 lines of description.... so..i threw it out there. the question was "where is it". i thought maybe that was the answer.

the mysteries of the configuration console are immense. being an old S3 codger who is learning LMK as best as an old dog can...when you add a custom field to a form/"biz class", I THINK that what happens is that it is added to a special table in LMK and attached to the appropriate thingy. THis may all be a fairy tale....i don't really know, but that's what it looks like. that being said, note that i have no idea what a CLOB is or where it comes from - custom field or "regular data item".
as in infor employee, we have access to many secrets in the world. that being said, i don't know if this is a "internal only" utility...but i can ask. you may have to be signed on to the LMK server to access the utility that generates this listing....which would leavea out the cloud folks

Ahhh - I see. "CLOB" is an advanced data type in SQL DBs. It just stands for "Character Large Object". It appears different ways in different DB types - in DB2 the field type is "DBCLOB". However, this is different from what the application designer probably calls the field type. When doing queries and lookups, I rely heavily on SQL rather than application queries, so my reference is coming from a SQL perspective. The way it looks in Config Console is completely different and often doesn't really seem to relate to what happens on the DB.

I do have command-line access to our LM servers, so I'd love to try the util if you can share it.

This is a bit off-topic, but since you brought it up... custom userfield definitions (related bus class, name, type, size, etc) are stored in the USERFIELD table and the data values (record-specific values) are stored in the USERFIELD_STORAGE table. These tables then have a relationship to every other table in the system in order to relate the userfield to the relevant business class. The application joins the two tables and displays the custom userfields as if they are actually part of the "main" business class, but they are really in a tag table.

The tool he used is $LAENVDIR/bin/displaydict.

kwane...you old dog.

yes...my boss says: the utility is in the manual and no reason not to say that's what i used. like most lawon utilities, just typing in the command by itself spawns the syntax

it is located in bin where all the rest of the utliities live....

Ahhh - very helpful! I wish I would have had that several years ago. I use dbdoc all the time and always wished there was something similar...

Thanks much!

How can I determine which "S$" table is associated with what other table?

For example, if I know the name of a Landmark table how do I determine its prefix?

Conversely, if I know the prefix (from the S$ table), how do I determine its related table?

It's all based on the primary table prefix.  There really isn't an easy way to go from the S$ table backwards to the primary table.  

There are several ways to find the primary table prefix:
- from the command line:  displaydict prodline primarytablename

If the indexes all start with the prefix, I should be able to use system tables in SQLServer to work my way backwards to the table the index is assigned to! Thanks!!

I would like to thank all of the people that contributed to this thread. Unfortunately, at my client, none of this seemed to help. We wound up finding a table called WORKASSIGNMENTEXPORT. It had the data we were looking for, so this is what we have used to retrieve the termination reason code.

Hello All, I am trying to get the FTE data from the CLOB field in the S$EMPWA table but having trouble of extracting it. I can get effective date but not FTE, can anyone please assist with it?
Thanks in advance,
HD