Custom Employee Lookup using SysTableLookup (Past Workers Example)

// Event handler on Employee Name control in HcmWorkerV2 form

[FormControlEventHandler(formControlStr(HcmWorkerV2, HcmWorker_EmployeeName), FormControlEventType::Lookup)]

public static void HcmWorker_EmployeeName_OnLookup(FormControl sender, FormControlEventArgs e)

{

    SysTableLookup          sysTableLookup;     // Lookup object

    Query                   query = new Query(); // Query object to build custom lookup

    QueryBuildDataSource    qbdsDirPerson, qbdsHcmWorker, qbdsEmployment;

    // Step 1: Base table = DirPerson (employee details)

    qbdsDirPerson = query.addDataSource(tableNum(DirPerson));

    // Step 2: Join HcmWorker with DirPerson

    qbdsHcmWorker = qbdsDirPerson.addDataSource(tableNum(HcmWorker));

    qbdsHcmWorker.relations(true);                 // Use table relations

    qbdsHcmWorker.joinMode(JoinMode::InnerJoin);   // Inner join (only matching workers)

    // Step 3: Join HcmEmployment with HcmWorker

    qbdsEmployment = qbdsHcmWorker.addDataSource(tableNum(HcmEmployment));

    qbdsEmployment.relations(true);                // Use table relations

    qbdsEmployment.joinMode(JoinMode::NoExistsJoin); // We want to apply condition for past workers

    // Step 4: Apply Past Worker filter

    // This method sets the condition: Employment.ValidTo < Today

    MCSSA_ReplacementWorkerLoopUp::addExitedRanges(qbdsEmployment);

    // Step 5: Initialize SysTableLookup

    sysTableLookup = SysTableLookup::newParameters(tableNum(DirPerson), sender);

    sysTableLookup.parmQuery(query); // Attach our custom query

    // Step 6: Add fields to show in the lookup

    sysTableLookup.addLookupfield(fieldNum(DirPerson, Name), true); // Employee Name

    sysTableLookup.addLookupMethod(tableMethodStr(DirPerson, PersonnelNumber)); // Personnel Number

    // Step 7: Show lookup

    sysTableLookup.performFormLookup();

    // Step 8: Cancel the super call (to stop default lookup from executing)

    FormControlCancelableSuperEventArgs cancelableSuperEventArgs = e as FormControlCancelableSuperEventArgs;

    cancelableSuperEventArgs.CancelSuperCall();

}

🛠 How It Works

1. Event Handler → Lookup event on HcmWorker_EmployeeName control.

2. Query Build → Add three tables:

   • DirPerson (base, has Name)

   • HcmWorker (worker details)

   • HcmEmployment (employment details, used to filter past workers)

3. Join Modes →

   • InnerJoin between DirPerson & HcmWorker

   • NoExistsJoin with HcmEmployment for applying a filter

4. Filter Method → Custom method addExitedRanges() ensures only employees with ValidTo < Today are shown.

5. SysTableLookup → Used to create a nice lookup form with multiple fields (Name, PersonnelNumber).

6. CancelSuperCall → Ensures only our custom lookup runs, not the default one.
   
   

✅ Result

When the user opens the Employee Name lookup →
✔ Only Past Workers appear
✔ Fields shown = Name and Personnel Number