Derived field to pull specific field based on different field (for one to many)

I've been trying to create derived fields to pull a specific field based on an if statement. It seems to work but only if the field was the last "action", if it was further down, that field is not showing.

Example:

Employee is a BusinessClass
owned by hr

Derived Fields

TermReason is a DerivedField
type is Alpha size 50
for each EmployeeTransactionHistoryRel
if (each.EmployeeTransactionHistoryRel.InitiatingAction = "Terminate")
return each.EmployeeTransactionHistoryRel.ActionReason
else
return "N/A"

If the Last Transaction was Terminate, then the ActionReason pulls correctly, however if there had been different InitiatingActions since the field comes over blank.

Also tried the following with no luck:

Employee is a BusinessClass
owned by hr

Derived Fields

TermReason is a DerivedField
type is Alpha size 50
if (EmployeeTransactionHistoryRel.InitiatingAction = "Terminate")
return EmployeeTransactionHistoryRel.ActionReason
else
return "N/A"

Anyone else try to create a derived field from a one to many relation BC and have success?

Find more posts tagged with

Infor Lawson Technology Group - Discussion

Comments

cockrell

Hi,

This is what we use to pull the term reason into a derived field:

Employee is a BusinessClass

owned by hr

Derived Fields

TerminationReasonDescrDer is a DerivedField

type is Alpha size 99

default label is untranslatable:"Reason for Termination"

for each Employee.audit log records

if ((each.action type.Update

and each.subject = "HCMEmployeeActionRequestTerminate"))

return each.reason code

I hope this helps!

apiazza

I think Shanell has provided great code. You could also try replacing "each" in your IF and RETURN statements with "first" and "last" and see what results you get. With "each" you are potentially going to get multiple results (one for each entry) that your field can't handle.

Please share what ends up working for you!

Hannah Johnson

Thanks for the responses, sorry it's taken so long for me to respond.

We ended up creating a relation (one to many for the field) to the history table:

TermEmployeeTransactionHistoryRel

one-to-many relation to EmployeeTransactionHistory

Field Mapping uses ByEffectiveDate

related.HROrganization = HROrganization

related.Employee = Employee

Instance Selection

where (related.InitiatingAction = "Terminate")

And then pulled the derived field from there to pull the most recent term:

TermReason is a DerivedField

type is Alpha size 50

default label is "MostRecentTermReason"

TermReason = any TermEmployeeTransactionHistoryRel.ActionReason

Dr. Awesome

Yeah, a DerivedField will only return one value and you dealing with a one-to-many. You could only ever get the first, last or "any" value, but you only get one.

You may be ok with that as most employees are terminated only once probably, but just be aware of what really happening.

Glad you added that Rel rather than iterating with a for each, it's cleaner.

You can control the Sort Order on TermEmployeeTransactionHistoryRel to truly guarantee "MostRecentTerm". If it's descending order on date and you use "first" you know you'll get "the most recent" Term if there are >1. Same for ascending order and "last". Again, if there are only 1 ever, doesn't matter. But this gives you tighter control than "any".

Oh, and I don't think you need the "TermReason = ". Use return.

TermReason is a DerivedField

type is Alpha size 50

default label is "MostRecentTermReason"

return first TermEmployeeTransactionHistoryRel.ActionReason

Hope that made sense.

Side note, if you wanted more than one, use the for each and concatenate a string with each each.ActionReason.

Copyright © 2025 Infor. All rights reserved.