Need to see if dependents are also active Employees

We have a request by our benefits department to provide a list of all active Dependents that are also active Employees. I'm able to get a list of all dependents and have the values for SSN but when searching by SSN against the Employee business class it is taking about 10-15 minutes to search just one SSN record and we have about 20,000 records to search against. Is there a quicker solution via IPA that could provide this? i'm familiar with file loading but have never loading two different files and wonder if i had a flat file for dependents with SSN an flat file with Employees with SSN to match against if it would be faster with them both loaded into memory?

Find more posts tagged with

Process Automation (IPA) - General Discussions

Accepted answers

All comments

don-kintzle

We had a similar request where we needed to identify the dependents that were also active employees in a file being sent to a vendor. With the assistance of Infor, we created a User Relation between the DependentIdentitifcationNumber and EmployeeIdentificationNumber business classes to help identify them. Once a relation was created we were then able to create a conditional field to Identify those that were also Employees. I believe this would work for your needs. We could exchange emails if you are interested.

russel-cornelson

You have a SSN and want to find the employee?

You must remember that the SSN is encrypted at the database so if you ask find me record where SSN = 123456789; the system will literally have to temporarily decrypt all records in the system to find the one that matches.

Instead you should use an approach like this....

1) javascript function to pull the 2nd, 4th, 6th, 8th character from the SSN you are searching for.

function extractCharacters(input) {
if (input.length !== 9) {
throw new Error("Input must be exactly 9 characters long.");
}

// Extract specific characters using their indices
const result = input[1] + input[3] + input[5] + input[7];

return result;
}

2) var PartialSSN = extractCharacters(FULLSSN);

3) Now make a landmark transaction against EmployeeIdentificationNumber

module=hr
Object=EmployeeIdentificationNumber
Action=Find
ActionType=MultiRecordQuery
SET=ByPartOfIdentificationNumber

^^ this last one is the most import part.
Then provide HROrganization=#### and PartOfIdentificationNumber=<!PartialSSN>

NOTE: The partial 4 digits are stored unencrypted as a key; so you can very quickly find 1 or a few records that share that key, and only those are decrypted to return the Employee number.

4) if additional employee fields are now required make another landmark transaction that uses the direct employee index fields.

Noe Ramos

i forgot to mention the day you posted this that i read this and ran with it. Support hadn't told me anything about why it was taking so long to query against the Employee class by SSN and it wasn't until you mentioned the encrypt/decrypt that i then remembered exactly just that and it made sense. however i didnt know about this class or at least had never used this new class EmployeeIdentificationNumber so thank you for that.

which brings me into new territory with my IPA writing if you dont mind me asking -

i'm able to take my fullSSN and create a new variable partialSSN to get a list from the EmployeeIdentificationNumber node populating about 100 records.

what would be the best way to take those 100 records from that Node and query against a match from my fullSSN? i thought about putting those 100 records into a msgbuilder variable - but then at that point i wasn't sure how to find the match and have it write that match to my final record?

Copyright © 2025 Infor. All rights reserved.